In a LEFT JOIN, does WHERE (o.status = 'PAID' OR o.id IS NULL) equal putting the status test in ON?

## Where the guard comes from Someone hits the classic collapse — `LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'PAID'` losing customers — and reaches for the smallest edit that brings rows back: ```sql WHERE o.status = 'PAID' OR o.id IS NULL ``` It is a reasonable-looking patch, because `o.id IS NULL` really is the one predicate shape that is TRUE for NULL-extended rows. The output row count goes up, some of the missing customers return, and the change ships. It is still wrong, and the wrongness is data-dependent, which is what makes it worth an interview question. ## Reasoning about the two forms row by row Group the customers by what their orders look like. **Customer with no orders at all.** The join produces no pair. NULL-extension creates one row with `o.id` NULL. Under the `ON` version it survives, since nothing filters it. Under the guard it survives too, via the `IS NULL` branch. **Same.** **Customer with at least one paid order.** Both forms return that customer's paid orders — the `ON` version by pairing only with paid orders, the guard by keeping the paid rows and discarding the rest. **Same.** **Customer whose orders exist but none is paid.** Here they diverge. Under the `ON` version, no cancelled order qualifies as a match, so the customer has zero surviving pairs and is NULL-extended: one row, blank order columns, customer present. Under the guard, the join *did* produce pairs — real cancelled orders with a non-NULL `o.id` — so no NULL-extended row was ever created. Every one of those rows fails `o.status = 'PAID'` and fails `o.id IS NULL`. All are discarded and **the customer vanishes**. So the guard is equivalent to the `ON` placement exactly when no left row has matches that all fail the filter. In a `customers`/`orders` schema that condition is essentially never guaranteed, which is why the patch behaves correctly in a small test dataset and wrongly in production. ```sql -- customer 7 has exactly two orders, both 'CANCELLED' -- A: keeps customer 7 (one NULL-extended row) SELECT c.id, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'; -- B: loses customer 7 entirely SELECT c.id, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'PAID' OR o.id IS NULL; ``` ## Why the difference is structural, not cosmetic NULL-extension is decided *before* `WHERE` runs, on the basis of the `ON` condition alone. Once the join has produced real matched rows for a left row, that left row is not a candidate for NULL-extension any more — no later clause can put it back. `WHERE` can only select among rows that exist; it cannot manufacture the NULL-extended row that a stricter `ON` clause would have created. That is the entire asymmetry, and it is why "filter in `ON`" and "filter in `WHERE` with a null-guard" are different operations rather than two spellings of one. A useful mental summary: `ON` changes *what counts as a match*, so it changes which rows get NULL-extended. `WHERE` changes *which existing rows are returned*, and by then the set of NULL-extended rows is already fixed. ## Choose the guarded column carefully — but that is not the real problem One secondary trap: the guard must test a column that cannot be NULL in real data, typically the right table's primary key. Guarding on a nullable column such as `o.note` keeps genuinely-matched rows that merely happen to have a NULL note, mixing source NULLs with join-produced NULLs. Fixing that choice, however, does not fix the structural problem above — the customer with only cancelled orders is still lost. ## When the guard is nonetheless the right tool There is a legitimate use for `OR <key> IS NULL` in `WHERE`: when the intent really is "rows that matched and pass the filter, **plus** rows that matched nothing at all", and you specifically do *not* want to rescue left rows whose matches all failed. That is a narrow requirement, and if you write it, say so in a comment, because every reviewer will otherwise read it as the mis-patched collapse. ## What interviewers listen for The expected answer is "no", followed by the exact dividing case: a left row with matches that all fail the predicate. Candidates who can construct that row on the spot — customer 7, two cancelled orders — have genuinely understood outer-join evaluation order rather than memorised a fix. Bonus points for noting that `WHERE` cannot create NULL-extended rows the join never produced.