NULL in SQL: IS NULL, COALESCE and the mistakes NULL causes
NULL means 'unknown'. Any comparison with it is unknown too, not true or false, so NULLs quietly drop out of filters unless you handle them on purpose.
IS NULL, never = NULL
manager_id = NULL is never true, not even when manager_id is NULL. Use IS NULL and IS NOT NULL.
SELECT name FROM employees WHERE manager_id IS NULL;
COALESCE for a fallback
COALESCE(a, b, c) returns the first argument that isn't NULL. All arguments must be the same type, so cast numbers to text if the fallback is a word.
SELECT name, COALESCE(manager_id::text, 'no manager') AS manager FROM employees;
How aggregates treat NULL
COUNT(*) counts rows; COUNT(column) skips NULLs. SUM and AVG ignore NULLs too, which is usually right but changes an average if you meant missing to be zero.
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Writing = NULL or != NULL.
- NOT IN with a subquery that can return NULL: it returns no rows at all. Use NOT EXISTS.
- COALESCE(number_column, 'text'), which fails on mismatched types.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.