After a LEFT JOIN, how do you tell a source NULL from an outer-join NULL?
answer
- the result set does not label its NULLs
- one NULL came from a table, one from the join
- pick a column that can never be NULL
- its primary key is the usual probe
- or project a literal 1 into the right side
basics
~20 sTest a right-side column that cannot be NULL in the base table, usually its primary key. If that comes back NULL the row never matched; if it has a value the match happened, so any other NULL you see is real data from the matched row.
solid answer
~50 sA `LEFT JOIN` produces NULLs from two completely different causes and the result set does not label them. Either the right row was missing and the engine NULL-extended every right column, or the right row matched fine and the column you are reading was NULL in the source. The reliable discriminator is a right-side column declared `NOT NULL` — its primary key is the usual choice. `WHERE o.order_id IS NULL` means *no matching order existed*; if `o.order_id` has a value, the row matched and a NULL in `o.shipped_at` is genuine data. When the right side is a derived table you control, an even clearer trick is to project a literal marker such as `1 AS matched` inside it: after the join, `matched IS NULL` means unmatched, with no dependence on which columns are nullable. What you must not do is test the joined nullable column itself, or wrap it in `COALESCE` — both erase the distinction you are trying to make.
code
sql · 8 lines-- customers.customer_id and orders.order_id are NOT NULL;
-- orders.shipped_at is nullable.
SELECT c.name,
o.shipped_at,
CASE WHEN o.order_id IS NULL THEN 'no order'
ELSE 'order exists' END AS match_state
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;go deeper
Know that an outer join invents NULLs for unmatched rows, so a NULL in the result may never have existed in any table. Naming the primary-key probe is enough here.
Explain both origins and give the discriminator: test a right-side NOT NULL column, or project a literal marker into a derived table. Say why testing the nullable column itself conflates the two.
Show why it matters downstream — the wrong probe changes reported populations with no error anywhere — and describe how you would keep the check robust if a NOT NULL constraint is later relaxed.
Treat it as a semantic contract for shared datasets: decide whether pipelines should carry an explicit match flag rather than letting every consumer re-derive the distinction from whichever column they guessed was NOT NULL.
## Two NULLs that look identical Run a `LEFT JOIN` and you get one result set containing NULLs of two different origins: 1. **NULL-extension.** The right side had no row satisfying the join predicate, so the engine emitted the left row with every right-hand column set to NULL. This NULL means "there is no such row". 2. **Source NULL.** The right side matched perfectly, and the column you are reading simply had no value in that matched row. This NULL means "the row exists but that attribute is unrecorded". SQL does not tag them. `NULL IS NULL` is `TRUE` in both cases, so the result set alone cannot tell you which happened — you have to build the discriminator into the query. ## The reliable technique: probe a NOT NULL column Pick a right-side column that can never be NULL in the base table. A primary key is the natural candidate, because the table's own constraints guarantee it has a value in every stored row. Then the only way it can be NULL in the join output is NULL-extension: ```sql SELECT c.name, o.shipped_at, CASE WHEN o.order_id IS NULL THEN 'no order' ELSE 'order exists' END AS match_state FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id; ``` Now `shipped_at` is interpretable. If `match_state` is `'order exists'` and `shipped_at` is NULL, the order is real and simply has not shipped. If `match_state` is `'no order'`, the customer has no order at all and `shipped_at` was never data in the first place. This is also the correct reading of the familiar anti-join idiom `WHERE o.order_id IS NULL`. It looks like it filters on data; it really filters on *the absence of a match*, and it is sound only because the probed column is `NOT NULL` in the base table. Point that idiom at a nullable column and it starts returning matched rows too — a genuine, easy-to-miss bug. ## The self-documenting variant: a literal marker When the right side is a subquery you control, project a constant into it: ```sql SELECT c.name, x.shipped_at, x.matched FROM customers c LEFT JOIN (SELECT o.customer_id, o.shipped_at, 1 AS matched FROM orders o) x ON x.customer_id = c.customer_id; -- x.matched IS NULL => no matching order row ``` A literal `1` cannot be NULL in any produced row, so after the join `matched IS NULL` means "unmatched" unconditionally. It survives someone later relaxing the `NOT NULL` constraint on whichever column you happened to probe, and it announces the intent to the next reader instead of relying on them knowing the schema. ## What breaks the distinction - **Probing the joined nullable column.** `WHERE o.shipped_at IS NULL` conflates "no order" with "unshipped order". This is the most common form of the bug and it usually inflates a count. - **Wrapping in COALESCE too early.** `COALESCE(o.shipped_at, DATE '1900-01-01')` collapses both origins into the same substitute value. Discriminate first, substitute afterwards. - **SELECT * and eyeballing.** A NULL renders identically either way in every client; there is nothing visual to spot. ## Why interviewers ask it Because the distinction changes numbers people report. "Customers with no shipped order" and "customers with no order" are different populations, and a query that confuses them is wrong in a way that still returns a plausible-looking figure. Being asked to explain the discriminator is really being asked whether you understand that an outer join *manufactures* NULLs that were never in any table. ## The related trap on the other side Remember where the NULL-extension came from in the first place: an outer join can fail to match for two reasons — the right table genuinely has no such key, or the *left* row's join key was NULL, which never matches anything under equality. Both surface identically as a NULL-extended row. If you need to separate those too, test the left key itself with `WHERE c.region_id IS NULL`; that is data on the preserved side and therefore always trustworthy. ## The habit to keep Whenever a query reads a right-side column after an outer join, decide explicitly what a NULL there should mean, and probe a `NOT NULL` column or a projected marker to enforce that meaning. Do not let the ambiguity reach a `CASE` expression, an aggregate or a report.
- Why is the anti-join idiom WHERE right.id IS NULL only correct on some columns?It tests for the absence of a match, not for data, and that reading holds only when the probed column is NOT NULL in the base table. Point it at a nullable column and matched rows whose value happens to be NULL come back too, silently inflating the "unmatched" set.
- How would you separate no-matching-row from a NULL join key on the preserved side?Test the left key itself: it is data on the preserved side and is never manufactured by the join. `WHERE c.region_id IS NULL` isolates rows that could never have matched anything, while a non-NULL left key plus a NULL-extended right side means the right table genuinely had no such row.
- Where does COALESCE belong when you also need a display default?After the discrimination, never before it. Work out the match state with a NOT NULL probe or a projected marker, branch on it in a CASE expression, and only then substitute a display value. Applying COALESCE to the joined column first destroys the very difference you needed.
saying these in an interview costs you the question
- Tests the nullable joined column itself for IS NULL
- Says the result set marks which NULLs the join produced
- Uses COALESCE before deciding whether the row matched
- Claims a LEFT JOIN never produces NULLs of its own
- Assumes any NULL after an outer join means no match