How do you list customers with no orders using a LEFT JOIN and IS NULL?

## The shape of the pattern ```sql SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL; ``` Read it as two independent moves. The `LEFT JOIN` guarantees that every customer reaches the intermediate result: those with orders appear once per order, those without appear once with all `orders` columns set to `NULL`. The `WHERE` clause then throws away everything except that second group. What remains is precisely the customers the join could not match — the ones with no orders at all. ## Why the NULL test identifies unmatched rows NULL-extension is the only way a `NULL` can appear in `o.customer_id` here. That column is the join key: for any row the join actually matched, `o.customer_id` equals `c.id` and is therefore a real value. So `o.customer_id IS NULL` is a reliable flag meaning "the outer join invented this half of the row". That reasoning is exactly why the choice of column matters. Suppose you test a genuinely nullable column instead: ```sql -- WRONG: shipped_at is NULL on every unshipped order WHERE o.shipped_at IS NULL ``` Now the filter keeps two different populations: customers with no orders, and customers whose orders simply have not shipped. The query still runs, returns plausible-looking output, and is wrong. Safe columns to test are the join key itself or the right table's primary key, both of which are `NOT NULL` by construction. ## Why the test goes in WHERE `IS NULL` here is a statement about the *joined row*, not about which pairings the join should accept. Putting it in `WHERE` lets the join complete first and then filters its output. That is the whole trick: build the superset, then keep the manufactured rows. ## Duplicates and row counts Beginners often reach for `DISTINCT` out of habit. It is unnecessary. A customer with orders is eliminated by the filter no matter how many orders they have, and a customer without orders is NULL-extended exactly once. So the result carries at most one row per customer already, and adding `DISTINCT` only hides your own understanding of the shape. A quick sanity check that catches most mistakes: if the table has 10 customers and 4 of them have at least one order, the anti-join must return exactly 6 rows. If it returns more, you tested the wrong column or joined on the wrong key. ## Selecting columns from the right side A subtle readability point: never select right-side columns in an anti-join. By construction they are all `NULL` for every returned row, so `SELECT c.name, o.total` produces a column of nothing but `NULL`s and invites the next reader to think something is broken. Project only from the preserved table. ## The same shape solves many requirements Once recognised, the pattern generalises to every "…with none" or "…that never…" requirement: - products never ordered - users who never logged in - departments with no employees - parent records orphaned in a data-migration check It is also directional. To find *orphan* orders — order rows whose customer no longer exists — flip which table is preserved: ```sql SELECT o.id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL; ``` Same idiom, opposite driving table. Being able to switch direction on demand, and to say out loud which side is preserved and which column proves the non-match, is what an interviewer is checking when they ask the classic "customers who never ordered" question. ## What a strong answer sounds like Write the query, then narrate it: "LEFT JOIN keeps every customer; the unmatched ones come back with NULL in the order columns; I filter on the join key because it can never be NULL in a real order row; and no DISTINCT is needed because each unmatched customer is emitted once." That covers the mechanics, the correctness caveat and the row-count reasoning in three sentences.