COALESCE in SQL
COALESCE takes a list of values and returns the first one that is not NULL. Everything else about it follows from that single sentence.
It exists because NULL is not a value — it is the absence of one — and almost nothing in SQL treats it the way you would like. COALESCE is how you say “use this, and if it is missing, use that instead” without writing a CASE expression every time.
The shape
COALESCE accepts two or more arguments and walks them left to right, stopping at the first one that is not NULL. If every argument is NULL, the result is NULL — it has nothing else to give you.
The arguments do not have to be columns. A literal is fine, and a literal is the usual last argument, because it is what guarantees the expression always produces something.
COALESCE(first_choice, second_choice, 'fallback')
The everyday use: a placeholder for missing text
Four of the eight objects in the table below have no recorded maker. Selecting the column plainly gives you four blanks, which is honest but reads badly in a report and gives a reader nothing to act on.
Wrapping the column tells them what the blank means. Run this, then delete the COALESCE and run it again — the difference is the whole point of the function.
SELECT title, COALESCE(maker, 'Unknown maker') AS maker FROM objects;
Try it
The better use: a fallback that is another column
The placeholder case is what most tutorials show, and it is the least interesting thing COALESCE does. The real use is picking between columns.
Every object here has a valuation. Only some have been separately insured. “What is each object covered for?” means: the insured figure where there is one, and the valuation otherwise — which is one expression, not a join and not a CASE.
Note what comes back for the Atlas of the Northern Seas: it is covered for 30,000 even though it is valued at 31,025. COALESCE is not choosing the larger number, or the better number. It is choosing the first one that exists. That distinction catches people out precisely because the placeholder examples never surface it.
SELECT title, COALESCE(insured_for, valuation) AS cover FROM objects ORDER BY cover DESC;
Why you cannot just use = NULL
The reflex is to write WHERE maker = NULL. It parses, it runs, and it returns zero rows every time — not an error, which is what makes it dangerous.
NULL means unknown, and comparing anything to an unknown yields another unknown rather than true or false. A WHERE clause keeps rows that are true, so a row whose test came back unknown is dropped. That is why NULL has its own operators, IS NULL and IS NOT NULL, and why COALESCE exists to sidestep the question entirely.
-- returns nothing, silently SELECT title FROM objects WHERE maker = NULL; -- returns the four unattributed objects SELECT title FROM objects WHERE maker IS NULL;
NULL and aggregates, which is where it bites
COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. On this table they are 8 and 4, and the gap is silent — no warning, no error, just a number that is half what you expected.
The same applies to AVG and SUM: they skip NULLs rather than treating them as zero. Sometimes that is what you want. When it is not, COALESCE inside the aggregate is how you say so, and writing it out makes the decision visible to whoever reads the query next.
SELECT COUNT(*) AS rows_total,
COUNT(maker) AS rows_with_maker,
AVG(insured_for) AS avg_skipping_nulls,
AVG(COALESCE(insured_for, valuation)) AS avg_with_fallback
FROM objects;COALESCE, IFNULL, NVL and ISNULL
Most databases ship a two-argument shorthand: IFNULL in MySQL and SQLite, NVL in Oracle, ISNULL in SQL Server. They all do the same job for exactly two arguments.
COALESCE is the one in the SQL standard, it takes any number of arguments, and it works everywhere. There is no reason to reach for the vendor spelling unless you are matching an existing codebase.
COALESCE or CASE?
COALESCE is a shorthand for one specific CASE expression — the one that tests each value for nullness in turn. Anything else, and you want CASE.
If your fallback depends on a condition other than “is it missing” — a different label per condition, a threshold, a comparison between two columns — COALESCE cannot express it and forcing it to will produce something harder to read than the CASE you were avoiding.
-- these two are the same expression COALESCE(insured_for, valuation) CASE WHEN insured_for IS NOT NULL THEN insured_for ELSE valuation END
Practice
Same table, same box above. Work each one out there before opening the answer.
1. Return every title with its note, showing “no note recorded” where the note is missing.
Show one answer
SELECT title, COALESCE(note, 'no note recorded') AS note FROM objects;
2. Return the three objects with the highest cover, where cover is the insured figure if there is one and the valuation otherwise. Show both underlying columns so you can see which one was used.
Show one answer
SELECT title, COALESCE(insured_for, valuation) AS cover, insured_for, valuation FROM objects ORDER BY cover DESC LIMIT 3;3. Return only the objects that have neither a maker nor a note. Two ways to write it — one with IS NULL twice, one with COALESCE. Try both.
Show one answer
SELECT title FROM objects WHERE maker IS NULL AND note IS NULL; -- or, leaning on COALESCE returning NULL when everything is NULL SELECT title FROM objects WHERE COALESCE(maker, note) IS NULL;
Common questions
- What does COALESCE do in SQL?
- It returns the first of its arguments that is not NULL, checking them left to right. If they are all NULL it returns NULL.
- How many arguments can COALESCE take?
- Two or more, with no practical upper limit. That is its main advantage over IFNULL, NVL and ISNULL, which take exactly two.
- Is COALESCE the same as IFNULL?
- For two arguments, yes. IFNULL is MySQL and SQLite, NVL is Oracle, ISNULL is SQL Server. COALESCE is the standard spelling, takes any number of arguments, and works on all of them.
- Why does WHERE column = NULL return no rows?
- NULL means unknown, so comparing anything to it produces unknown rather than true. WHERE keeps only rows that test true, so every row is dropped. Use IS NULL instead.
- Does COALESCE slow a query down?
- The expression itself is cheap. What can cost you is wrapping an indexed column in it inside a WHERE clause, which usually stops the index being used. In the SELECT list it is a non-issue.
Keep going
Reading about SQL and writing it are different skills. Accession is a museum records puzzle that makes you write it — six levels so far, nothing to install and no account.