Most NULL bugs come from forgetting that SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Once you accept that, the rest follows.
= NULL is always UNKNOWN
x = NULL never returns TRUE, even when x is NULL. Use IS NULL / IS NOT NULL. The same applies to <>, <, and >.
NULL in WHERE filters out the row
WHERE only keeps rows where the predicate is TRUE. UNKNOWN rows get dropped, which is why NOT IN with NULLs in the right side returns nothing.
-- Surprising: returns zero rows if any value in the subquery is NULL
SELECT * FROM users WHERE id NOT IN (SELECT manager_id FROM users);NULL in aggregates
COUNT(*) counts every row. COUNT(col) skips NULL. SUM/AVG also skip NULL. This is usually what you want — except when zero NULLs would have meant zero.
NULL in joins
JOIN ON a = b drops rows where either side is NULL. Use a IS NOT DISTINCT FROM b (Postgres) or COALESCE-based keys to treat NULLs as equal.
Rule of thumb: assume any column can be NULL until you prove otherwise. Add COALESCE at the boundary, not in five places downstream.