SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested
SQL COALESCE returns the first value in its list that is not NULL. Learn how to use it for defaults, fallback columns, safe maths and LEFT JOIN gaps, how it compares with IFNULL, ISNULL and NULLIF, and the traps it does not fix, with queries we ran.
- SQL COALESCE takes any number of arguments and returns the first one that is not NULL, or NULL if all are.
- An empty string is not NULL: combine
NULLIF(TRIM(col), '')with COALESCE to skip blanks. - Pair
NULLIF(x, 0)with COALESCE to avoid division by zero and still show a number. - COALESCE is standard SQL.
IFNULLandISNULLare vendor-specific two-argument versions.
NULL means “unknown”, and unknown values spread. Add a NULL to a number and you get NULL. Compare a NULL with anything and the row quietly drops out of your results. SQL COALESCE is the everyday tool for replacing those unknowns with something useful: a default, a fallback column or a zero.
Every query below was run in SQLite 3.50.4 from Python 3.13, and the outputs are copied from that run. COALESCE and NULLIF are standard and behave the same way in PostgreSQL, MySQL, SQL Server and Oracle. Where engines differ, such as division by zero and ISNULL, we say so and note that we did not run those engines. For background on how NULL works, read our SQL NULL values guide.
What is COALESCE?
The syntax is simple:
COALESCE(value1, value2, ..., valueN)
The database checks each argument from left to right and returns the first one that is not NULL. If every argument is NULL, the result is NULL. Think of it as a shorthand for a CASE expression:
CASE WHEN value1 IS NOT NULL THEN value1
WHEN value2 IS NOT NULL THEN value2
ELSE value3 END
That equivalence is written into the SQL standard, and it is why COALESCE works everywhere. Our SQL CASE WHEN guide covers the longer form.
Picking the first available value
Customer contact data is often patchy. We stored five contacts, each with a different mix of mobile number, work phone and email, using masked sample numbers:
SELECT name,
COALESCE(mobile, work_phone, email, 'no contact') AS best
FROM contacts;
| name | mobile | work_phone | best | |
|---|---|---|---|---|
| Asha | 98xxxx0101 | NULL | asha@example.com | 98xxxx0101 |
| Ravi | NULL | 020-xxxx-55 | NULL | 020-xxxx-55 |
| Li | NULL | NULL | li@example.com | li@example.com |
| Sam | NULL | NULL | NULL | no contact |
| Noor | ” (empty) | NULL | noor@example.com | ” (empty) |
Four rows behaved as expected. Noor’s row did not: the result was an empty string, even though an email was available.
Empty strings are not NULL
COALESCE only skips NULL. An empty string, a space or the text 'N/A' are all real values to the database. The fix is to turn blanks into NULL first with NULLIF:
SELECT name,
COALESCE(NULLIF(TRIM(mobile), ''), work_phone, email, 'no contact') AS best
FROM contacts;
With this version Noor’s best contact became noor@example.com, and the other four rows were unchanged. TRIM removes spaces, NULLIF converts the empty result to NULL, and COALESCE moves on. This three-function chain is worth memorising; it cleans most form data. Our SQL string functions guide covers TRIM and friends.
NULL in calculations
Any arithmetic with NULL returns NULL. We applied an optional discount to revenue:
SELECT region, revenue, discount,
revenue * (1 - discount) AS raw,
revenue * (1 - COALESCE(discount, 0)) AS fixed
FROM sales;
| region | revenue | discount | raw | fixed |
|---|---|---|---|---|
| North | 5000 | NULL | NULL | 5000 |
| South | 0 | 0.1 | 0.0 | 0.0 |
| East | NULL | 0.05 | NULL | NULL |
| West | 1200 | NULL | NULL | 1200 |
The raw column lost two regions’ revenue just because no discount was recorded. Wrapping the discount in COALESCE fixed North and West. East stays NULL because the revenue itself is unknown, and that is correct: do not invent a zero for a value that might be large.
NULLIF and division by zero
NULLIF(a, b) returns NULL when a equals b, and a otherwise. Its classic job is protecting a division:
SELECT region, revenue, orders,
revenue * 1.0 / NULLIF(orders, 0) AS aov,
COALESCE(revenue * 1.0 / NULLIF(orders, 0), 0) AS aov_or_0
FROM sales;
North returned an average order value of 100.0. South and West had zero orders, so NULLIF turned the divisor into NULL and the result into NULL instead of an error; COALESCE then displayed 0. Engines differ here: SQLite returned NULL even for a bare 5000/0 in our test, while PostgreSQL and SQL Server raise a division-by-zero error (we did not run those). Using NULLIF makes the behaviour the same everywhere.
Tip: decide whether “no orders” should show 0 or a blank. A zero average order value suggests orders worth nothing, so many reports keep NULL and display a dash.
COALESCE with aggregates
Aggregate functions skip NULLs, which changes averages more than people expect:
SELECT SUM(revenue), COUNT(revenue), COUNT(*),
AVG(revenue), AVG(COALESCE(revenue, 0))
FROM sales;
(6200, 3, 4, 2066.6666666666665, 1550.0)
AVG divided by the three known values, while the COALESCE version treated East as zero and divided by four. Neither is automatically right; choose based on whether a missing value means “zero” or “unknown”. There is also a quieter trap: SUM over no rows returns NULL, not 0. Our query for a region that does not exist returned NULL until we wrapped it as COALESCE(SUM(revenue), 0).
Filling LEFT JOIN gaps
A LEFT JOIN fills missing matches with NULL. Only North had a refund in our data:
SELECT s.region, r.amount AS refund, COALESCE(r.amount, 0) AS refund0
FROM sales s
LEFT JOIN refunds r ON r.region = s.region;
North showed 300, and the other three regions showed NULL in the raw column but 0 in the COALESCE column. That makes the column safe to subtract from revenue. Join behaviour is explained in our SQL joins guide and our new SQL self join guide, which uses COALESCE to label the top manager.
The WHERE trap COALESCE fixes
We counted contacts whose mobile is not 98xxxx0101. Four of five contacts qualify, but the plain query disagreed:
SELECT COUNT(*) FROM contacts WHERE mobile <> '98xxxx0101'; -- 1
SELECT COUNT(*) FROM contacts WHERE COALESCE(mobile, '') <> '98xxxx0101'; -- 4
SELECT COUNT(*) FROM contacts WHERE mobile IS NOT '98xxxx0101'; -- 4 (SQLite)
The plain query returned 1 because a comparison with NULL is unknown, and unknown rows are filtered out. COALESCE fixed it. The standard null-safe alternative is IS DISTINCT FROM in PostgreSQL and recent SQL Server versions, and SQLite accepts IS NOT as we used. Be aware that wrapping a column in COALESCE inside WHERE can stop the database using an index on it.
COALESCE vs IFNULL vs ISNULL vs NVL
| Function | Arguments | Where | Note |
|---|---|---|---|
COALESCE |
Any number | All major databases | Standard SQL, the portable choice |
IFNULL |
Exactly 2 | SQLite, MySQL | Three arguments raised “wrong number of arguments” in SQLite |
ISNULL |
Exactly 2 | SQL Server | Result takes the first argument’s type, so long fallbacks can be cut short |
NVL |
Exactly 2 | Oracle | Oracle’s two-argument version |
NULLIF |
Exactly 2 | All major databases | The reverse job: turns a value into NULL |
Watch out for MySQL: its ISNULL(x) takes one argument and returns 1 or 0, which is a test, not a replacement. When in doubt, write COALESCE.
Types and short-circuiting
Two finer points came out of our tests. First, the arguments should share a type. SQLite is flexible and returned the integer 5 from COALESCE(NULL, 5, 'x'), but stricter engines such as PostgreSQL reject mixed types, so cast explicitly. Second, COALESCE stops at the first non-NULL value. We registered a Python function that logs each call and ran COALESCE('first', slow('second')); the function was never called. Put cheap columns first and expensive subqueries last.
SQL COALESCE checklist
- Decide what NULL means for each column before replacing it: missing, not applicable or truly zero.
- Clean blanks first with
NULLIF(TRIM(col), '')so empty strings do not block the fallback. - Protect every division with
NULLIF(divisor, 0), whatever your current database does. - Wrap totals as
COALESCE(SUM(col), 0)when an empty result should read as zero. - Keep argument types consistent and cast where needed, so the query ports between engines.
- Watch WHERE clauses: use COALESCE or a null-safe comparison, and check the query plan afterwards.
- Test with NULL rows in your fixtures; most COALESCE bugs only appear when real data has gaps.
Final thoughts
SQL COALESCE is small but it touches almost every report you will write. Use it to choose fallback columns, to keep NULL out of arithmetic, to fill LEFT JOIN gaps and to make sums of nothing read as zero. Combine it with NULLIF to clean blanks and protect divisions. And before reaching for it, ask whether a missing value really means zero, because hiding an unknown can be worse than showing it.
Frequently asked questions
What does COALESCE do in SQL?
It returns the first non-NULL value from its list of arguments. If every argument is NULL, it returns NULL.
What is the difference between COALESCE and ISNULL?
COALESCE is standard SQL and accepts any number of arguments. ISNULL is SQL Server specific, takes exactly two, and returns the type of the first argument, which can truncate longer values.
Does COALESCE treat an empty string as NULL?
No. An empty string is a real value. Use COALESCE(NULLIF(TRIM(col), ”), fallback) to treat blanks as missing.
How do I avoid division by zero in SQL?
Divide by NULLIF(divisor, 0) so a zero becomes NULL and the result is NULL instead of an error. Wrap it in COALESCE if you want to display a default such as 0.
Why does SUM return NULL instead of 0?
SUM over zero rows, or over only NULL values, returns NULL. Use COALESCE(SUM(col), 0) when an empty result should read as zero.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.