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 .…