NULL in a join key

Question

NULL in a join key

Accepted Answer

SQL uses three-valued logic: NULL compared to anything - including another NULL - evaluates to UNKNOWN, not TRUE. So ON a.id = b.id will not match when both a.id and b.id are NULL. This is why LEFT JOIN + WHERE right.id IS NULL finds unmatched rows rather than accidentally matching two NULLs. If you need to treat NULLs as equal, use ON (a.id = b.id OR (a.id IS NULL AND b.id IS NULL)) or the IS NOT DISTINCT FROM operator in PostgreSQL.