Originally published at recca0120.github.io JOIN two tables on a nullable column and the rows disappear. Insert two NULLs into a UNIQUE column and neither insert fails. Both problems share the same root: SQL defines NULL as unknown, and unknown is never equal to anything — including another unknown. What NULL Actually Means In SQL, NULL means "unknown value" — not zero, not empty string, not false. SELECT NULL = NULL ; -- Result: NULL, not TRUE SELECT NULL IS NULL ; -- Result: TRUE NULL = NULL returns NULL , not TRUE . This single rule is why JOINs and UNIQUE constraints behave unexpectedly with NULL. Trap 1: JOIN on NULL Columns Returns No Rows The Scenario Two tables where orders.customer_id can be NULL. You want to JOIN them to find the customer for each order: -- customers table | id | name | | ------|-------| | NULL | Alice | | 1 | Bob | -- orders table | id | customer_id | amount | | ----|-------------|--------| | 1 | NULL | 100 | | 2 | 1 | 200 | SELECT o . id , c . name , o .…