~/learn/SQL · Filtering & sorting
NULLs and COALESCE
The missing-value rules that quietly break queries.
why this matters
Missing data is everywhere — a customer with no phone, an order with no ship date. If you don't handle NULL, your counts and averages quietly go wrong.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
NULL means unknown, not zero and not empty string. Any comparison with it is neither true nor false, so `= NULL` never matches — use IS NULL and IS NOT NULL.
COALESCE returns the first non-NULL argument, which is how you supply a default. NULLIF does the reverse: it turns a sentinel value into NULL, handy for avoiding division by zero.
Try it: Keep only customers whose spend is 0.
Note
Aggregates skip NULLs: COUNT(col) ignores them while COUNT(*) counts the row. AVG divides by the non-NULL count, so a NULL is not the same as a zero.
Think of NULL as 'unknown'
NULL means "unknown", not zero or empty. NULL = NULL → unknown (not true!) NULL IS NULL → true 5 + NULL → NULL
key takeaways
- Use IS NULL / IS NOT NULL, never = NULL.
- COALESCE(x, fallback) replaces NULL with a value.
- COUNT(col) skips NULLs; COUNT(*) doesn't.
check your understanding
Which condition finds rows where email is missing?
free practice
Build confidence with a question you can solve without Pro.