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.
NULL is contagious in arithmetic
Any arithmetic expression that touches a NULL becomes NULL: 5 + NULL is NULL, not 5:
| name | email_length |
|---|---|
| Aisyah | 15 |
| Bala | NULL |
| Chong | NULL |
| Devi | 13 |
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.
LENGTH of a NULL email is NULL
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:
| name | best_contact |
|---|---|
| Aisyah | aisyah@mail.com |
| Bala | 012-3456789 |
| Chong | No contact info |
| Devi | devi@mail.com |
Bala has no email, so COALESCE falls through to his phone. Chong has neither, so it falls all the way through to the literal fallback string.
Email, or phone, or a fallback message
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:
| name |
|---|
| Chong |
Customers with no contact info at all
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:
| name | failed_login_attempts | risk_ratio |
|---|---|---|
| Aisyah | 3 | 33 |
| Bala | 0 | NULL |
| Chong | 5 | 20 |
| Devi | 0 | 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, so the result is a clean NULL instead of a crash.
NULLIF guarding a division by zero
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.