MySQL — Left Join

Basic LEFT JOIN

SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;

LEFT JOIN Examples

-- All users with their orders (including users without orders)
SELECT
    u.name,
    COALESCE(COUNT(o.id), 0) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Users who haven't placed orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Products never ordered
SELECT p.name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

LEFT JOIN vs INNER JOIN

-- INNER JOIN: only matching rows
SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN: all users, matching orders
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Mini Practice

Write MySQL code that:

1. Uses LEFT JOIN
2. Finds unmatched records
3. Counts related records
4. Compares with INNER JOIN

Up Next

Next: Learn about Right Join.