LEFT JOIN: Keeping All Left-Side Rows

LEFT JOIN returns all rows from the left table, plus matching rows from the right table. When there's no match in the right table, the right-side columns are filled with NULL.

This contrasts with INNER JOIN, which drops any row that doesn't have a match on both sides.

Prior lesson

The sample dataset is defined in the Introduction to SQL lesson.

Syntax

SELECT columns
FROM left_table AS l          -- left table
LEFT JOIN right_table AS r ON l.key = r.key;  -- right table

Venn diagram view

Join type	Keeps rows from left with no match	Keeps rows from right with no match
INNER JOIN	No	No
LEFT JOIN	Yes (NULLs on right side)	No

Examples

Example 1: All customers, including those with no orders

Frank Lee (customer 6) exists in customers but has no orders. A LEFT JOIN keeps Frank in the result and fills the orders columns with NULL.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

Result (all orders per customer, NULLs where no order exists):

customer_id	name	order_id	total_amount
1	Alice Johnson	1	999.00
1	Alice Johnson	2	59.98
1	Alice Johnson	6	349.00
2	Bob Smith	3	349.00
2	Bob Smith	7	999.00
3	Carol White	4	796.00
3	Carol White	9	999.00
4	Dave Brown	5	49.90
4	Dave Brown	10	24.95
5	Eve Davis	8	89.97
6	Frank Lee	NULL	NULL

Example 2: Find customers with NO orders

When the right-side columns are all NULL, the row had no match. Filtering on IS NULL isolates those rows — a classic anti-join pattern.

SELECT
    c.customer_id,
    c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Result:

customer_id	name
6	Frank Lee

Common Mistake

Filtering on the right-side table in WHERE turns LEFT JOIN into INNER JOIN

Once you add a WHERE condition on the right-side table, rows that were NULL (unmatched) fail the condition and get dropped - effectively converting your LEFT JOIN into an INNER JOIN.

-- This looks like a LEFT JOIN but behaves like INNER JOIN:
SELECT c.name, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';  -- drops NULLs!

-- To filter on the right side while preserving NULLs, put the condition in ON:
SELECT c.name, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
                     AND o.status = 'completed';

Practice

Write a query that returns each customer's name, city, order_id, and order_date. Include customers who have never placed an order.

Show answer

SELECT
    c.name,
    c.city,
    o.order_id,
    o.order_date
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

Expected result:

name	city	order_id	order_date
Alice Johnson	New York	1	2024-01-05
Alice Johnson	New York	2	2024-01-10
Alice Johnson	New York	6	2024-03-01
Bob Smith	London	3	2024-01-15
Bob Smith	London	7	2024-03-15
Carol White	Sydney	4	2024-02-01
Carol White	Sydney	9	2024-04-10
Dave Brown	Toronto	5	2024-02-10
Dave Brown	Toronto	10	2024-05-01
Eve Davis	New York	8	2024-04-01
Frank Lee	NULL	NULL	NULL

Frank Lee has no orders, so all order columns are NULL.