NULL Handling and COALESCE
NULL means "unknown", not zero and not empty. Pick an expression and watch the same rows get handled differently by each one.
The Expression
Reading It
COALESCE(a, b) — first non-NULL argument.
NULLIF(a, b) — NULL if a = b, else a.
x = NULL — always UNKNOWN, never TRUE. Use IS NULL.
employees.bonus, and this expression applied
—Query
Result
NULL and COALESCE: A Practical Guide
The value that means "I don't know" and behaves exactly like it.
Quick Context
NULL is not zero, not an empty string, and not false. It means the value is unknown, and every comparison that touches it inherits that uncertainty: NULL = NULL is not TRUE, it is UNKNOWN. This is three-valued logic, and COALESCE, NULLIF and IS NULL are the tools for working with it on purpose instead of by accident.
The three tools
- COALESCE(a, b, ...) returns the first non-NULL argument. COALESCE(bonus, 0) turns a missing bonus into a real zero for arithmetic.
- NULLIF(a, b) is the reverse: returns NULL if a equals b, otherwise a. Useful for turning a sentinel value like 0 or "" back into a proper NULL before averaging.
- IS NULL / IS NOT NULL are the only correct way to test for NULL. = NULL and != NULL both silently evaluate to UNKNOWN and match nothing.
Interactive Exploration Guide
- Look at the raw column. Three employees have no bonus recorded — NULL, not zero.
- Try bonus = 0. Every NULL row evaluates to UNKNOWN, not TRUE, so none of them match a filter on this condition — even though "no bonus" feels like it should mean zero.
- Try COALESCE(bonus, 0). The NULLs become real zeros, usable in arithmetic.
- Compare AVG(bonus) with and without COALESCE. AVG ignores NULLs entirely — it divides by the count of non-NULL rows, not by all rows — so wrapping in COALESCE changes the answer because it changes what counts as a value.
- Compare COUNT(bonus) with COUNT(*). COUNT(column) skips NULLs; COUNT(*) counts every row regardless. Same table, different answer.
Key Takeaway
NULL means unknown, so comparisons involving it are UNKNOWN rather than TRUE or FALSE, and aggregates skip it by default. COALESCE substitutes a default, NULLIF creates a NULL on purpose, and IS NULL is the only comparison that actually works — everything else is a trap that looks like it should work and quietly does not.