Chapter 17 · intermediate · 7 min
NULL, deeper: COALESCE & NULLIF
You've already met IS NULL for filtering. But NULL shows up everywhere else too, in arithmetic, in aggregates, in display. This chapter covers the two functions that do most of the real work: COALESCE (fall back to a default) and NULLIF (turn a specific value into NULL on purpose).
We'll clean up a customer contact list.
The dataset
A contacts table where some customers are missing an email, a phone, or both.
Schema
| id | INT |
| name | TEXT |
| TEXT | |
| phone | TEXT |
| failed_login_attempts | INT |
Example data
NULL is contagious in arithmetic
Any arithmetic expression that touches a NULL becomes NULL: 5 + NULL is NULL, not 5. This trips people up in aggregates too: SUM and AVG quietly skip NULL rows rather than treating them as 0, which can change your answer if you're not expecting it.
Before you run it — Bala and Chong both have a NULL email. What does email_length show for their rows?
COALESCE: fall back to a default
COALESCE(a, b, c, ...) returns the first argument that isn't NULL. It's the standard way to display a friendly default instead of a blank, or to pick between two possible sources of a value.
Before you run it — Bala has no email but has a phone; Chong has neither. What does best_contact show for each of them?
Collapsing two NULL checks into one
Since COALESCE(email, phone) is only NULL when both are NULL, wrapping it in IS NULL replaces email IS NULL AND phone IS NULL with one shorter expression.
Before you run it — of the 4 contacts, who has neither an email nor a phone?
NULLIF: turn a value into NULL on purpose
NULLIF(a, b) returns NULL if a equals b, otherwise it returns a. The classic use is guarding against divide-by-zero: dividing by NULLIF(count, 0) turns a would-be crash into a clean NULL. Bala and Devi both have 0 failed logins, so dividing by their raw count would error with "division by zero". NULLIF sidesteps it by swapping the 0 for a NULL right before the division happens.
Before you run it — Bala and Devi both have 0 failed logins. Does the query error, or does risk_ratio come back as something else for them?
The two functions, side by side
| Function | Purpose | Returns |
|---|---|---|
COALESCE(a, b, ...) | pick the first real value | the first non-NULL argument |
NULLIF(a, b) | suppress a specific value | NULL if a = b, otherwise a |
They're near-opposites: COALESCE replaces a NULL with something real; NULLIF replaces something real with a NULL, on purpose, usually to make a later calculation safe.
If you've used Excel or Google Sheets
COALESCE is the same job as IFERROR(A1, IFERROR(B1, "No contact info")), or more directly, Excel/Sheets' own COALESCE() function, which exists with an identical name and behaviour. NULLIF's divide-by-zero guard is the SQL version of wrapping a formula in IFERROR(A1/B1, ""). Both exist purely to stop a division from blowing up the whole calculation.
Ready to practice? RHB Bank's branch manager fallback question below is a direct COALESCE exercise.
Between COALESCE for defaults and NULLIF for deliberately creating NULLs, you can handle almost any messy-data situation without extra application code.