What happens to NULLs in a join condition?

-- orders: id, customer_id
-- (1, 5), (2, NULL), (3, NULL)

SELECT * FROM orders a
JOIN orders b ON a.customer_id = b.customer_id
WHERE a.id <> b.id;

You might expect rows 2 and 3 to match each other since they both have customer_id = NULL — but they don't, because NULL = NULL is UNKNOWN, and ON (like WHERE) only keeps rows where the condition evaluates to TRUE.

Why this matters in practice

This is the correct, standard SQL behavior, and it's usually what you want — two "unknown" values shouldn't be treated as definitely equal. But it surprises people who expect join semantics to match GROUP BY/DISTINCT semantics, where multiple NULLs in a grouping column are, by convention, grouped together as if equal:

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
-- all NULL customer_id rows land in ONE group together

Similarly, most engines' UNIQUE constraints treat multiple NULLs as not violating uniqueness (i.e., you can insert many rows with NULL in a unique column) — again, a different rule than the join comparison.

If you actually need NULL = NULL to match

Use IS NOT DISTINCT FROM (standard SQL, supported by PostgreSQL and others) instead of =:

SELECT * FROM a
JOIN b ON a.key IS NOT DISTINCT FROM b.key;

IS [NOT] DISTINCT FROM is a NULL-safe equality comparison — NULL IS NOT DISTINCT FROM NULL evaluates to TRUE. MySQL has an equivalent NULL-safe operator, <=>. This is important for tables where a foreign key column can legitimately be NULL and you actually want those rows to match each other during a join (rare, but it does come up in data-reconciliation queries).