NULL in SQL: The Rules That Break Queries

NULL means unknown

Any comparison with NULL is neither true nor false. It is unknown, and WHERE keeps only rows where the condition is true.

SELECT * FROM customers WHERE city = NULL;    -- always zero rows
SELECT * FROM customers WHERE city IS NULL;   -- correct

The NOT IN trap

-- returns NO rows if any blocked_id is NULL
SELECT * FROM users WHERE id NOT IN (SELECT blocked_id FROM blocks);
 
-- safe
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocks b WHERE b.blocked_id = u.id);

id NOT IN (1, NULL) becomes id <> 1 AND id <> NULL. The second part is unknown, so the whole condition is never true.

Aggregates skip NULLs

AVG(discount) over (10, NULL, 20) is 15, not 10. Decide whether a missing value means zero, and use COALESCE(discount, 0) if it does.

NULLs in joins and grouping

• a.key = b.key never matches NULL keys, so rows with NULL keys vanish from inner joins.
• GROUP BY puts all NULLs into one group.
• UNION and DISTINCT treat NULLs as equal to each other.