Why does adding WHERE o.status = 'PAID' to a LEFT JOIN drop customers with no orders?

## The symptom Somebody writes a report that must list every customer, paid orders included where they exist. They start from a `LEFT JOIN`, which does the right thing, then add a status filter in `WHERE` — and the customers with no orders vanish. The `LEFT JOIN` is still in the text, so the query looks correct on a skim. This is probably the single most-asked SQL trap in interviews, and it is asked because it is one of the most common real bugs in reporting code. ```sql -- collapses: only customers with at least one PAID order survive SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'PAID'; ``` ## Why it happens, step by step A joined table is defined in stages, and the `LEFT JOIN` has one stage that the `INNER JOIN` does not: 1. Pair each customer with each order and keep the pairs for which the `ON` condition is TRUE. A customer with no orders — or whose orders all belong to someone else — produces no surviving pair. 2. **NULL-extension.** Every left row that produced no surviving pair is added back to the result once, with every column of `orders` set to NULL. This is the step that makes the join "outer". 3. `WHERE` is applied to the rows from step 2. In step 3 the NULL-extended row is just a row like any other, and it is tested like any other. `o.status` is NULL there, and comparing NULL to anything with `=` yields UNKNOWN, SQL's third truth value. `WHERE` retains a row only when its predicate is TRUE; FALSE and UNKNOWN are both discarded. So every NULL-extended row is removed, and what is left is precisely the set of matched pairs that also pass the filter — the inner join result. The `LEFT JOIN` keyword did its job and was then undone one clause later. Note that it is not only `=`. `o.total > 100`, `o.status <> 'CANCELLED'`, `o.status IN ('PAID','SHIPPED')` and `o.ordered_at >= DATE '2024-01-01'` all return UNKNOWN against NULL and all collapse the join in exactly the same way. The one shape that survives is an explicit null test such as `o.id IS NULL`, which is TRUE for NULL-extended rows — that is why the "rows with no match" idiom works at all, and why it is the exception to remember. ## The fix: move the predicate into ON ```sql -- preserves every customer SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'; ``` Inside `ON`, the status test is part of deciding what counts as a match. A cancelled order simply is not a match, so a customer whose only order is cancelled produces no surviving pair in step 1 and is therefore NULL-extended in step 2 — one row, name filled in, order columns NULL. The customer stays in the report, correctly showing that they have no paid order. This is the general recipe: **a filter on the optional (NULL-extendable) side of an outer join belongs in `ON` if you want unmatched rows kept, and in `WHERE` only if you deliberately want them gone.** A filter on the preserved side is a different matter — it belongs in `WHERE`, where it removes the rows you actually meant to remove. ## Reading the difference in the output The two versions differ in row count and in shape. Suppose ten customers, six of whom have at least one paid order, two of whom have only cancelled orders, and two of whom have no orders at all. The `WHERE` version returns rows for six customers. The `ON` version returns rows for all ten: the six with their paid orders, and four single rows with NULL order columns. If your report must show "0" or "—" for inactive customers, only the second version can produce it. ## Deliberate collapse is legitimate Sometimes the collapsing form is exactly what you want, and then the honest thing is to write `INNER JOIN`. Leaving `LEFT JOIN` in the text while filtering the optional side in `WHERE` is misleading to the next reader: it advertises an intent the query does not have. In review, treat `LEFT JOIN` plus a `WHERE` predicate on that alias as either a bug or a keyword that should be changed. ## What interviewers listen for The answer they want has three parts: the NULL-extension step, the fact that the comparison against NULL yields UNKNOWN and `WHERE` keeps only TRUE, and the fix by moving the predicate into `ON`. Candidates who say only "WHERE turns it into an inner join" have memorised the outcome; candidates who mention `IS NULL` as the surviving shape have actually thought about it.