Why does WHERE col = NULL return no rows in PostgreSQL, and what should you use instead?
NULL means unknown, so comparing anything to it with = gives NULL, not true, and WHERE keeps only true rows. Use IS NULL, or IS NOT DISTINCT FROM when you want NULLs treated as equal.
Explain it simply
NULL is not a value, it is a missing value. If you ask "is the unknown number equal to the other unknown number?", the honest answer is "unknown", not "yes". SQL uses three outcomes: true, false and NULL. A WHERE clause keeps only rows where the condition is true, so NULL rows disappear.
Even NULL = NULL gives NULL, and NOT (NULL) is still NULL.
Example
SELECT NULL = NULL; -- NULL
SELECT NULL IS NULL; -- true
SELECT 1 IS DISTINCT FROM NULL; -- true
SELECT NULL IS NOT DISTINCT FROM NULL; -- true
-- students with no phone number
SELECT name FROM students WHERE phone IS NULL;
-- NOT IN trap: one NULL in the list makes every row fail
SELECT 5 NOT IN (1, 2, NULL); -- NULL, not trueCOALESCE(phone, 'unknown') replaces NULL with a default when displaying.
What to say in the interview
"Comparisons with NULL return NULL, and WHERE only keeps true, so = NULL matches nothing. I use IS NULL, or IS NOT DISTINCT FROM when comparing two columns that may both be NULL. I also watch NOT IN with subqueries, since a single NULL makes it return nothing."
Common mistakes
- Using NOT IN on a subquery column that can contain NULL; prefer NOT EXISTS.