NULL handling — three-valued logic and gotchas
NULL means 'unknown' in SQL — not 'empty' and not 'zero'. Three-valued logic (TRUE, FALSE, UNKNOWN) quietly breaks naive queries.
represents **'we don't know'** — not 'empty string' and not 'zero'. 'The employee's manager is NULL' could mean 'no manager exists' or 'we haven't recorded one yet'. The DB doesn't distinguish.
** (3VL)**: any comparison with NULL returns NULL (not TRUE, not FALSE). `WHERE x = NULL` never matches anything — use `WHERE x IS NULL`. `WHERE x <> 'foo'` also misses NULLs — you must add `OR x IS NULL` if you want them.
**Arithmetic with NULL**: anything + NULL = NULL. `salary + bonus` is NULL if bonus is NULL. Use `salary + (bonus, 0)` to treat NULL as zero.
Aggregates skip NULL (except COUNT(*)). SUM(commission) sums the non-NULL values; 10 rows with 2 NULLs gives sum of 8. AVG(commission) divides by 8, not 10.
Sorting: NULLs sort last in ASC, first in DESC (Postgres default, configurable with NULLS FIRST/LAST). Consistent across dialects… mostly.
**The COALESCE trick**: `COALESCE(a, b, c, 'default')` returns the first non-NULL. Use it to provide fallbacks: `COALESCE(phone, mobile_phone, 'unknown')`. `NULLIF(a, b)` returns NULL if a = b — useful to avoid divide-by-zero: `x / NULLIF(y, 0)`.
Grounded on https://www.postgresql.org/docs/current/functions-comparison.html
Next up
Query optimization — anti-patterns to avoid
The top 10 mistakes that turn fast queries slow: SELECT *, function on indexed column, N+1, implicit casts, deep pagination.