SQL / NULL AND THREE-VALUED LOGIC
Filling gaps with COALESCE
Use COALESCE to substitute a chosen fallback for NULL, order fallbacks by priority, and place it inside or outside an aggregate deliberately.
What you will learn
- Write COALESCE(a, b, 'default') to return the first non-NULL argument, left to right
- Order arguments by trust: the leftmost non-NULL wins and later ones are skipped
- Put COALESCE outside SUM to fill empty groups; inside it changes COUNT and AVG
- Expect NULL back when every argument is NULL; COALESCE invents nothing
Understanding Filling gaps with COALESCE
COALESCE takes any number of arguments, evaluates them left to right, and returns the first one that is not NULL; if every argument is NULL, the result is NULL. The useful mental model is a priority list rather than a repair tool: the leftmost argument is the source you trust most, and each argument to its right is what you will settle for when the one before it is unknown. Because it produces a value instead of a truth value, it sidesteps the fact that nothing compares equal to NULL — you are choosing a substitute, not testing for one.
You reach for it constantly because NULL propagates: arithmetic or concatenation touching an unknown yields an unknown, so a single missing value can blank out an entire computed column. COALESCE is where you make the modelling decision about what that unknown should stand for, and the right answer is local — a missing shipping fee may sensibly be 0, while a missing exam score must not be. One constraint follows from the fact that a result column has exactly one type: all arguments must share a common type, which is why COALESCE(price, 'unknown') is a type error rather than a mixed column.
Placement matters as much as order. Filling a value in before it enters an aggregate changes what the aggregate is computing — an extra 0 raises COUNT and pulls AVG toward zero — while filling in the aggregate's result only changes what gets printed. The practical rule is to coalesce at the edge of the calculation, on the way out to the report, and to coalesce earlier only when the substituted value is genuinely part of the arithmetic, such as treating a customer with no payments as having paid 0.
WITH contact AS (
SELECT 'Ada' AS name, 'ada@lab.org' AS email, '555-0101' AS phone
UNION ALL SELECT 'Grace', NULL, '555-0102'
UNION ALL SELECT 'Alan', NULL, NULL
)
SELECT name,
email,
COALESCE(email, phone, 'no contact on file') AS reachable_at
FROM contact
ORDER BY name;COALESCE returns the first non-NULL value from a left-to-right priority list, so the argument order and the position of the call are both decisions you are making about meaning.
Worked examples
Filling an empty aggregate
Shows why the 0 has to be applied to the result of SUM rather than to its input in a LEFT JOIN report.
WITH customer AS (
SELECT 1 AS id, 'Ada' AS name
UNION ALL SELECT 2, 'Grace'
UNION ALL SELECT 3, 'Alan'
),
payment AS (
SELECT 1 AS customer_id, 40 AS amount
UNION ALL SELECT 1, 15
UNION ALL SELECT 2, 30
)
SELECT c.name,
SUM(p.amount) AS raw_sum,
COALESCE(SUM(p.amount), 0) AS paid
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;Example explained
Line 1LEFT JOIN keeps Alan even though payment has no matching row, so his group holds one row whose p.amount is NULL.
Line 2SUM ignores NULL inputs, and with no values left to add it returns NULL rather than 0, which is why raw_sum is NULL for Alan.
Line 3COALESCE wraps the finished aggregate, so it rewrites only that one NULL group total into 0 and leaves 55 and 30 untouched.
Line 4SUM(COALESCE(p.amount, 0)) would also print 0 here, but it turns the unmatched row into a real 0 that COUNT and AVG would then include.
COALESCE as a precedence chain
Demonstrates that the argument order is the override policy, and that an all-NULL argument list yields NULL.
WITH setting AS (
SELECT 'timeout' AS setting_key, NULL AS user_value, 30 AS org_value, 60 AS fallback
UNION ALL SELECT 'retries', 5, 3, 1
UNION ALL SELECT 'ttl', NULL, NULL, NULL
)
SELECT setting_key,
COALESCE(user_value, org_value, fallback) AS effective,
COALESCE(fallback, org_value, user_value) AS if_order_flipped
FROM setting
ORDER BY setting_key;Example explained
Line 1COALESCE(user_value, org_value, fallback) reads left to right, so a NULL user_value hands control to org_value and 'timeout' resolves to 30.
Line 2For 'retries' the first argument is already non-NULL, so 5 wins and the remaining two arguments are never used.
Line 3The flipped column shows the order is the entire policy: with fallback first, the specific values 5 and 30 are silently discarded.
Line 4'ttl' has no non-NULL argument, so the result is NULL — COALESCE reports that everything was unknown instead of inventing a number.
Important notes
Arguments to the right of the first non-NULL one are normally not evaluated, so an expensive fallback may cost nothing; do not use that as an error guard, since an aggregate written inside COALESCE is still computed for the whole group regardless of its position.
COALESCE is standard SQL and variadic, while IFNULL (MySQL, SQLite), ISNULL (SQL Server) and NVL (Oracle) take exactly two arguments; ISNULL also forces the result to the first argument's type, which can truncate a longer replacement string.
Common mistakes
Writing SUM(COALESCE(amount, 0)) to guard against an empty result: when the WHERE clause matches no rows there is nothing to substitute, so the query still returns NULL — only COALESCE(SUM(amount), 0) is guaranteed to give 0.
Coalescing to 0 on a column where 0 is a legal measurement, which merges 'not recorded' with 'recorded as zero'; later WHERE value = 0 filters and averages then report the two cases as one.
Mixing types in the argument list, as in COALESCE(qty, 'none'): a strongly typed engine cannot make one column both numeric and text and raises an error, so you must CAST(qty AS text) first if you really want a word.
Try it yourself
Change, predict, then run
In a browser editor, build a four-row CTE of users with nickname, full_name and login columns, leaving different values NULL in each row including one row where all three are NULL, then return a single display_name that prefers nickname, then full_name, then login. Swap the order so login comes first and note exactly which rows change and why.
Open the SQL workspaceCheck your understanding
Customer 7 has no rows at all in payment. What do SELECT COALESCE(SUM(amount), 0) FROM payment WHERE customer_id = 7 and SELECT SUM(COALESCE(amount, 0)) FROM payment WHERE customer_id = 7 return?
- Both return 0, because COALESCE handles the missing value wherever it is written
- The first returns NULL and the second returns 0, because the inner substitution runs first
- The first returns 0 and the second returns NULL, because there are no rows for SUM to add
- Both return NULL, since SUM over zero rows is NULL and COALESCE cannot change that
Show answer
The filter removes every row, so the aggregate runs over an empty set: SUM returns NULL, and a COALESCE written inside the aggregate never executes because there is no row to execute it on. Only the outer COALESCE sees that NULL and turns it into 0. The first option is tempting because SUM(COALESCE(amount, 0)) does yield 0 in a LEFT JOIN report, where the unmatched side supplies one all-NULL row to substitute — but here there is no row at all.