How do you write a join predicate that treats NULL on both sides as a match?

## The problem being solved `ON a.k = b.k` rejects any pair where either key is NULL, because the comparison yields `UNKNOWN` and a join admits only `TRUE`. Sometimes that is exactly right. But sometimes NULL is a meaningful category in your data — an optional `region` that is genuinely absent on both rows of a matching pair, two extracts of the same source where an attribute was never captured — and you want "absent here, absent there" to count as a match. You have to ask for that explicitly. ## The standard predicate: IS NOT DISTINCT FROM `a IS NOT DISTINCT FROM b` is a null-safe comparison. Unlike `=`, it is total: it always returns `TRUE` or `FALSE`, never `UNKNOWN`. | a | b | a = b | a IS NOT DISTINCT FROM b | |---|---|---|---| | 1 | 1 | TRUE | TRUE | | 1 | 2 | FALSE | FALSE | | 1 | NULL | UNKNOWN | FALSE | | NULL | NULL | UNKNOWN | TRUE | Read it as English: two values are *distinct* if you can tell them apart, and two NULLs are indistinguishable. Put it straight into the `ON` clause: ```sql SELECT s.id, t.id FROM staging s JOIN target t ON s.region IS NOT DISTINCT FROM t.region; ``` The negated form `IS DISTINCT FROM` is the null-safe `<>`, which is the predicate you want when writing change-detection comparisons. ## When your engine does not have it Support is not universal. MySQL provides the null-safe equality operator `<=>`, which has the same truth table: ```sql -- MySQL JOIN target t ON s.region <=> t.region ``` Where neither spelling exists, the portable expansion is an explicit disjunction: ```sql JOIN target t ON (s.region = t.region OR (s.region IS NULL AND t.region IS NULL)) ``` The outer parentheses are load-bearing. If this is one conjunct among several and you omit them, `AND` binds tighter than `OR` and the predicate quietly means something else. The form is verbose, but it runs everywhere and it makes the intent legible. ## Composite keys Each nullable column needs its own null-safe comparison; a single plain `=` anywhere in the conjunction reintroduces the trap, because `TRUE AND UNKNOWN` is `UNKNOWN`. ```sql JOIN target t ON s.region IS NOT DISTINCT FROM t.region AND s.sku IS NOT DISTINCT FROM t.sku ``` A reasonable middle ground on a wide key is to use the null-safe form only for the columns that are actually nullable and leave `=` on the `NOT NULL` ones — it documents which columns you expect to be optional. ## The consequence people forget A null-safe join treats NULL as a single shared value, which means *every* NULL row on one side matches *every* NULL row on the other. If staging has 500 rows with a NULL region and target has 300, that block alone produces 150,000 output rows. Under plain equality it produced zero. This is why an apparently harmless switch from `=` to `IS NOT DISTINCT FROM` sometimes turns a fast query into one that never finishes, and it surprises people far more than the semantics do. Check the cardinality of the NULL group on both sides before making the change. There is a second, quieter cost: null-safe predicates and hand-written `OR` expansions are harder for an engine to satisfy from an ordinary index on the key, so the join method the planner picks may change. Treat that as a portability caveat to verify on your engine, not as a universal rule. ## The COALESCE alternative and why it is second-best The other common workaround maps NULL to a sentinel on both sides — `ON COALESCE(s.region, '~none~') = COALESCE(t.region, '~none~')`. It works in any dialect and the intent is visible. Its weakness is that it is correct only while the sentinel can never occur in real data, and it wraps the column in a function on both sides. Prefer the null-safe predicate wherever your engine offers one. ## The question behind the question An interviewer asking this usually wants the third option too: if NULL is behaving like a value in your joins, consider making it one. A `NOT NULL` column with an explicit "unknown" member row in the referenced table removes the special case from every query anyone writes against that table afterwards, instead of asking each author to remember a predicate.