What happens when a join key contains NULLs? Do NULL values ever match in a JOIN?

How to think about it

This is a deceptively subtle point, and it bites data engineers in ETL pipelines more than anywhere else. NULL in a join key isn’t rare — optional foreign keys, half-loaded dimension tables, and NULL-as-unknown patterns all produce it. The sentence that wins the interview: “NULL never equals anything, including another NULL, so NULL-keyed rows are silently dropped from an INNER JOIN.”

A worked example — watch the row vanish

Table a has a NULL-keyed row (NULL, 'y'); table b has (NULL, 'beta'). They look like they should pair up. They don’t:

SELECT a.val, b.info
FROM a
JOIN b ON a.id = b.id;

val	info
x	alpha

Only (x, alpha) survives. NULL = NULL evaluates to UNKNOWN, and in a join condition UNKNOWN is treated as false — so the two NULL-keyed rows never meet. A LEFT JOIN keeps the left row, but its right side is NULL for the ordinary unmatched-row reason, not because the key itself was NULL:

SELECT a.val, b.info
FROM a
LEFT JOIN b ON a.id = b.id;

val	info
x	alpha
y	NULL

Forcing NULL-to-NULL matching

When you genuinely want the NULLs to pair, use NULL-safe equality. IS NOT DISTINCT FROM (PostgreSQL, BigQuery, DuckDB — and SQLite) treats NULL as equal to NULL:

SELECT a.val, b.info
FROM a
JOIN b ON a.id IS NOT DISTINCT FROM b.id;

val	info
x	alpha
y	beta

Now (y, beta) appears — the NULLs matched. The portable fallback, for engines without that operator, is to COALESCE both keys to a sentinel that can’t occur as a real value:

SELECT a.val, b.info
FROM a
JOIN b ON COALESCE(a.id, -1) = COALESCE(b.id, -1);

Pick -1 or '__null__' deliberately, and document why — both forms change semantics and can hurt index usage.

Name all three options

What separates a good answer from a great one is laying out the full menu rather than just the default:

1. Accept the data loss — if NULL-keyed rows are genuinely invalid, the INNER JOIN is doing the right thing.
2. IS NOT DISTINCT FROM — NULL-safe equality, where the engine supports it.
3. COALESCE to a sentinel — portable, but the sentinel must be a value that can never be a real key.