Lesson 2 of 5 · 15–25 minutes

LEFT JOIN and finding what's missing

🏠The everyday picture

A class list with each student's homework, where students who haven't submitted still appear, with a blank in the homework column. The blanks are often exactly what the teacher wants to see.

📘What it really is

LEFT JOIN keeps every row from the left table, filling the right side with NULL where there's no match:

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

Customers with no orders appear once, with order_id NULL.

Finding things that are missing, a very common question:

-- Customers who have never ordered
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Others: orders with no payment yet; enquiries never assigned; products never sold.
RIGHT JOIN is the mirror image (rarely used; swap the table order instead). FULL JOIN keeps unmatched rows from both sides (PostgreSQL; newer SQLite).
Trap: a condition on the right table in WHERE turns a LEFT JOIN back into an inner join, because NULL rows fail it. Put such conditions in the ON clause instead: LEFT JOIN orders o ON o.customer_id = c.id AND o.placed_at >= '2026-10-01'.

🖱️Try it

Write queries for: customers who have never ordered; orders with no payment recorded; products never sold; and every customer with their number of orders in October, including those with zero (use the ON trick and COUNT(o.id)).

✅Quick check

How do you find customers with no orders?

Show the answer

LEFT JOIN orders and keep rows where the order's id IS NULL.

Why put a date condition in ON rather than WHERE in a LEFT JOIN?

Show the answer

In WHERE, it removes the rows with no match, turning the LEFT JOIN into an inner join.