What about customers who never ordered?
Marketing wants to run a win-back campaign: a discount code for anyone who signed up but never actually bought anything. Harry already knows how to join customers to orders — so he tries the obvious thing first.
What about customers who never placed an order?
Try your join from last time and see what you get.
Harry writes the same join as before:
SELECT customers.name, orders.id
FROM customers
JOIN orders ON customers.id = orders.customer_id;This gives me every customer who has an order. I need the opposite — the ones missing from this list entirely.
Right, and here's the problem: a regular JOIN only keeps rows where a match actually exists on both sides. A customer with zero orders has nothing on the orders side to match — so JOIN just... drops them. There's no trace of them left to filter for.
What Harry needs is a join that doesn’t throw anyone away. LEFT JOIN keeps every row from the left-hand table — customers, here — no matter what. If it finds a matching order, great, it attaches it. If it doesn’t, it keeps the customer anyway and fills every column that would’ve come from orders with NULL.
Keep everything on the left. Fill the gaps with NULL.
Watch what happens to three customers side by side: Bob (2 orders), Chloe (0 orders), and Diego (1 order).
SELECT customers.name, orders.id AS order_id, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE customers.id IN (2, 3, 4)
ORDER BY customers.id, orders.id;| name | order_id | amount |
|---|---|---|
| Bob Turner | 5 | 199.99 |
| Bob Turner | 6 | 29.99 |
| Chloe Martin | NULL | NULL |
| Diego Fernandez | 7 | 49.99 |
Bob shows up twice, once per order, same as a regular join. Diego shows up once. Chloe still shows up — that’s the whole point of LEFT — but with NULL standing in for every order column, because there was nothing on the right to attach.
Running…
24 rows now, not 22 — Chloe and Hana each get exactly one row, padded out with NULLs, instead of disappearing.
Now Harry can actually ask his question. He doesn’t want every customer — just the ones where the join found nothing. And orders.id is a perfect column to test: it’s never NULL in the real orders table, so if it shows up NULL here, it can only mean one thing — the join didn’t find a match.
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;| name |
|---|
| Chloe Martin |
| Hana Sato |
Try it: What happens if you swap that LEFT JOIN back to a plain JOIN and keep the same WHERE orders.id IS NULL?
Run your query to see results here.
It runs fine — and comes back completely empty. Not an error, just zero rows. Makes sense once you think it through: a regular JOIN already throws away any customer without a matching order, so by the time WHERE runs, every remaining row has a real orders.id. There’s nothing left for “IS NULL” to catch. The LEFT is what keeps the “missing” rows around long enough to find.
Support wants to personally call every customer who’s never ordered — but only the ones NOVA doesn’t already have a phone number for, since those are the ones worth chasing down contact info for. Find the customers with no phone number on file and no orders at all.
Hint 1
This needs two separate conditions to both be true at once: missing phone, and missing orders.
Hint 2
customers.phone IS NULL checks data that's genuinely stored that way. orders.id IS NULL, after a LEFT JOIN, checks for "no match found." They're testing different kinds of NULL, but the syntax is identical.
Hint 3
SELECT customers.name FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE customers.phone IS NULL AND orders.id IS NULL;
Solution
Running…
Only Chloe matches both. Farah and Julia also have no phone number, but they’ve both ordered, so they drop out. It’s worth noticing there are two different flavors of NULL at play in that query: customers.phone is NULL because that’s genuinely what’s stored for Chloe — nobody ever collected it. orders.id is NULL because the LEFT JOIN manufactured it, on the spot, to mean “nothing matched here.” Same keyword, two different origins — and the second kind only exists because you asked for the join in the first place.
LEFT JOIN keeps every row from the left table, matched or not, and fills in unmatched right-side columns with NULL. Pick a column from the right table that’s never NULL in real data — usually its id — and test WHERE that column IS NULL to find the rows with no match at all.