Understanding SQL LEFT JOIN: Complete Guide with Examples

LEFT JOIN keeps all rows from the left table, adding matching data from the right table:

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Every user appears in results, even those without orders. Orders columns are NULL for users with no orders.

LEFT JOIN vs INNER JOIN

INNER JOIN - only matching rows:

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- Only users WHO HAVE orders

LEFT JOIN - all left table rows:

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- ALL users, with or without orders

How It Works

Given these tables:

users:
| id | name  |
|----|-------|
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |

orders:
| id | user_id | total |
|----|---------|-------|
| 1  | 1       | 50.00 |
| 2  | 1       | 30.00 |
| 3  | 2       | 20.00 |

LEFT JOIN result:

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

| name  | total |
|-------|-------|
| Alice | 50.00 |
| Alice | 30.00 |
| Bob   | 20.00 |
| Carol | NULL  |  -- No orders

Carol appears with NULL because she has no orders.

Finding Unmatched Rows

Users without orders:

SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;
-- Returns: Carol

The WHERE clause filters for rows where the join found no match.

Counting with LEFT JOIN

Count orders per user:

SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

| name  | order_count |
|-------|-------------|
| Alice | 2           |
| Bob   | 1           |
| Carol | 0           |

COUNT(orders.id) counts non-NULL values, so Carol gets 0.

Multiple LEFT JOINs

SELECT 
    users.name,
    COUNT(DISTINCT orders.id) as order_count,
    COUNT(DISTINCT reviews.id) as review_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN reviews ON users.id = reviews.user_id
GROUP BY users.id, users.name;

Each LEFT JOIN preserves all users.

LEFT JOIN with WHERE

Be careful with WHERE clauses:

-- Wrong - filters out unmatched rows
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';
-- Carol disappears (orders.status is NULL)

-- Correct - filter in JOIN condition
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id 
    AND orders.status = 'completed';
-- Carol remains with NULL orders

COALESCE for Defaults

Replace NULL with defaults:

SELECT 
    users.name,
    COALESCE(SUM(orders.total), 0) as total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

| name  | total_spent |
|-------|-------------|
| Alice | 80.00       |
| Bob   | 20.00       |
| Carol | 0.00        |  -- NULL becomes 0

LEFT JOIN vs RIGHT JOIN

These are equivalent:

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

-- RIGHT JOIN (same result, tables swapped)
SELECT * FROM orders
RIGHT JOIN users ON users.id = orders.user_id;

LEFT JOIN is more common and readable.

Performance Considerations

LEFT JOIN can be slower than INNER JOIN:

Must check all left table rows
Cannot skip rows early
Produces more rows

Indexes on join columns help:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Common Mistakes

Filtering NULL in WHERE instead of JOIN:

-- Breaks LEFT JOIN
WHERE orders.created_at > '2024-01-01'

-- Correct
LEFT JOIN orders ON users.id = orders.user_id 
    AND orders.created_at > '2024-01-01'

Forgetting NULL handling in aggregates:

-- Wrong - counts NULL as 1
COUNT(*)

-- Correct - counts actual values
COUNT(orders.id)

Real-World Example

SELECT 
    customers.id,
    customers.name,
    customers.email,
    COUNT(orders.id) as total_orders,
    COALESCE(SUM(orders.amount), 0) as lifetime_value,
    MAX(orders.created_at) as last_order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name, customers.email
ORDER BY lifetime_value DESC;

Shows all customers, including those who never ordered.