COALESCE in SQL: a working guide

Why COALESCE matters

COALESCE is the ANSI SQL function that returns the first non-NULL value from a list of arguments. It scans left to right, stops at the first non-NULL, and returns NULL if every argument is NULL. That single behavior stitches together half the data-cleaning queries on a real analytics job: replacing missing usernames with a fallback, pushing referrer through an attribution chain, padding empty days in a revenue report, or making SUM return 0 on an empty group.

On a data analyst loop at Stripe, Snowflake, DoorDash, or Linear, COALESCE shows up everywhere SQL touches imperfect data. Interviewers rarely ask "explain COALESCE". They ask "fill in missing days in this revenue series" or "why is your SUM blank on this department". Candidates who do not know the function reach for nested CASE and miss the trap that COALESCE on an indexed column silently kills the query plan.

Syntax and type rules

COALESCE(value1, value2, ..., valueN)

The function takes two or more arguments. No hard upper bound exists; in practice engines accept dozens. Evaluation is short-circuiting: once a non-NULL value is found, the rest are skipped, which matters when a later argument is an expensive subquery.

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- returns 'third'

SELECT COALESCE(NULL, NULL, NULL);
-- returns NULL

The return type follows the engine's type-promotion rules. If arguments cannot be coerced to one compatible type, the query fails at parse time. A frequent mistake is mixing a numeric column with a string fallback like COALESCE(amount, 'n/a'). Cast the column or use a numeric sentinel.

Fallback chains

The largest single use case for COALESCE is a chain of fallback values. You have a preferred source, a backup, and maybe a static default — walk through them in order until something is filled in.

-- Traffic source: utm_source, then referrer, then 'direct'
SELECT
    user_id,
    COALESCE(utm_source, referrer, 'direct') AS traffic_source
FROM visits;

-- Display name: nickname, then first_name, then a static label
SELECT
    user_id,
    COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

Without COALESCE you write a chain of IS NOT NULL checks inside a CASE WHEN and the SELECT bloats by ten lines. Short-circuit evaluation also means the engine skips the third argument if the second was already filled in, so a fallback hitting an expensive function does not pay that cost on most rows. A practical attribution pattern chains the click ID, campaign tag, referrer host, and a direct bucket — one COALESCE replaces a four-way LEFT JOIN with priority logic.

COALESCE in SELECT and WHERE

The most common spot for COALESCE is in SELECT, where it stops NULL from leaking into a dashboard cell. BI tools render NULL as blank, which looks like missing data to a stakeholder even when the value is correctly absent.

SELECT
    user_id,
    COALESCE(city, 'Unspecified') AS city,
    COALESCE(company, 'Independent') AS company,
    COALESCE(experience_years, 0) AS experience_years
FROM candidates;

COALESCE does not change stored data; it substitutes on the fly during the read. Replacing NULLs in the table itself with sentinel values is an antipattern: it bakes a UI convention into the warehouse and breaks the next consumer who wants to know whether the value was unknown.

In WHERE, COALESCE is occasionally useful as a guard against NULL propagation, but it has a sharp performance trap. COALESCE on an indexed column inside a WHERE clause kills B-tree index usage — the optimizer sees a function wrapping the column and falls back to a full scan.

-- index on status is NOT used
WHERE COALESCE(status, 'unknown') = 'active'

-- index on status IS used
WHERE (status = 'active' OR status IS NULL)

If filtering by a COALESCE expression is a recurring pattern, build a functional index on the expression in Postgres or rewrite the predicate as an explicit OR. The rule applies to every column-wrapping function, not just COALESCE.

COALESCE with aggregates

SQL aggregates ignore NULLs by definition. SUM, AVG, MIN, MAX, and COUNT(column) skip NULL rows — usually correct, occasionally surprising. COALESCE is the lever to bend it back.

-- SUM over an empty group returns NULL, not 0
SELECT
    department,
    COALESCE(SUM(bonus), 0) AS total_bonus
FROM employees
GROUP BY department;

-- AVG ignores NULL rows; wrap the input to count them as zeros
SELECT
    team_id,
    AVG(COALESCE(score, 0)) AS avg_score_with_zeros,
    AVG(score) AS avg_score_excluding_nulls
FROM reviews
GROUP BY team_id;

Placement matters and is a recurring interview trap. COALESCE(SUM(x), 0) wraps the aggregate output and only kicks in when the whole group reduces to NULL (every row NULL, or an empty group after a LEFT JOIN). SUM(COALESCE(x, 0)) wraps each row before summing, so every NULL row becomes a contributing zero. For SUM the two forms agree by accident. For AVG and COUNT they diverge: the first preserves NULL-skipping, the second pulls the denominator up by counting NULL rows as zeros.

Filling gaps in reports

A staple use of COALESCE is padding a sparse time series with zeros. The pattern is a calendar table LEFT JOINed against the facts — days without facts come through as NULL on the aggregated columns, and the chart drops gaps. COALESCE turns each NULL into a 0 and the line stays continuous.

-- Daily revenue with gaps filled
SELECT
    d.dt AS report_date,
    COALESCE(SUM(o.amount), 0) AS revenue,
    COALESCE(COUNT(o.order_id), 0) AS orders
FROM calendar d
LEFT JOIN orders o ON o.order_date = d.dt
WHERE d.dt BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY d.dt
ORDER BY d.dt;

The same pattern works for any sparse grouping dimension: country, channel, plan tier, day-of-week. Generate the full dimension set in a CTE, LEFT JOIN the aggregated fact, and wrap the metric columns. Without that wrapping, a downstream arithmetic step propagates the NULL and silently drops the row.

COALESCE vs CASE, ISNULL, NULLIF

COALESCE is a special case of CASE WHEN. The two forms below are equivalent — COALESCE is just the concise shape when the only condition is "is this value NULL".

-- COALESCE: concise
SELECT COALESCE(nickname, first_name, 'Anonymous') AS name FROM users;

-- Equivalent CASE WHEN: verbose but identical
SELECT
    CASE
        WHEN nickname   IS NOT NULL THEN nickname
        WHEN first_name IS NOT NULL THEN first_name
        ELSE 'Anonymous'
    END AS name
FROM users;

The moment the rule becomes "non-empty string", "non-zero", or "matches a pattern", you need CASE WHEN — COALESCE only looks at NULL. The classic miss is treating empty strings as NULL: COALESCE('', 'fallback') returns '' because '' is not NULL. Fix it by passing through NULLIF first: COALESCE(NULLIF(col, ''), 'fallback').

ISNULL is a SQL Server function with exactly two arguments that behaves like a two-argument COALESCE. Outside SQL Server it either does not exist or behaves differently (in MySQL ISNULL(x) returns 1 or 0, not a value). COALESCE is the portable choice. NULLIF is the inverse operation: COALESCE turns NULL into a value, NULLIF turns a value into NULL. The canonical use is safe division — revenue / NULLIF(sessions, 0) returns NULL instead of crashing when sessions is zero.

Common pitfalls

The most expensive pitfall in production is COALESCE on an indexed column inside a WHERE clause. The optimizer sees a function wrapping the column and refuses to use the index, so a query that should hit a single row scans the entire table. The fix is to rewrite the predicate as column = value OR column IS NULL or build a functional index on the COALESCE expression. The trap is easy to miss in code review because the query still returns the correct answer, just orders of magnitude slower.

Another common trap is misplacing COALESCE relative to the aggregate. COALESCE(SUM(x), 0) and SUM(COALESCE(x, 0)) are not equivalent for AVG and COUNT. If you mean "treat missing rows as zero for the average", wrap each row. If you mean "if the whole group is empty, return zero instead of NULL", wrap the aggregate. Pick on purpose, not at random.

A third pitfall is type incompatibility. COALESCE requires all arguments to be coercible to a single type — mixing a numeric column with a string fallback fails at parse time. Use a numeric sentinel like -1 or handle the NULL at the application layer instead of a forced cast.

A fourth pitfall is forgetting that empty strings are not NULL. COALESCE(name, 'Anonymous') does nothing for a row where name = ''; you need COALESCE(NULLIF(name, ''), 'Anonymous'). The same applies to zero, whitespace-only strings, and any "logically empty" value the table stores explicitly.

A fifth pitfall is using COALESCE as a patch for upstream data quality. If a column has 30% NULLs and every query wraps it, fix the ETL instead of patching every consumer. COALESCE is a defensive read pattern, not a replacement for clean data.

Interview questions

What does COALESCE return?

COALESCE returns the first non-NULL value from its argument list. It accepts two or more arguments, scans them left to right, and stops at the first non-NULL. If all arguments are NULL, it returns NULL. The function is ANSI SQL and works in PostgreSQL, MySQL, Snowflake, BigQuery, SQL Server, Oracle, and ClickHouse. Evaluation is short-circuiting, so an expensive expression in position three does not run if position two already supplied a value.

What does COALESCE(NULL, 0, NULL, 5) return?

Zero. The first non-NULL value is 0, which is a concrete number, not a NULL. A common mistake is to treat zero as a missing value the way some languages treat falsy values; SQL does not. NULL means unknown or absent; 0 means known and happens to equal zero. COALESCE only cares about NULL.

How is COALESCE different from CASE WHEN?

COALESCE is a shorthand for one specific kind of CASE WHEN — it tests each argument for NULL and returns the first one that is not. CASE WHEN can express arbitrary boolean conditions, including non-NULL checks like "greater than 100", "matches a regex", or "string is not empty". For pure NULL handling COALESCE is more concise and more readable. For anything else you need CASE.

How do you compute an AVG that counts NULL rows as zeros?

Wrap the column inside the aggregate: AVG(COALESCE(score, 0)). Without the COALESCE, AVG ignores NULL rows entirely, so a column with values [10, NULL, NULL, 20, 30] averages to 20. With COALESCE(score, 0), the NULL rows become zeros, the inputs become [10, 0, 0, 20, 30], and the average drops to 12. Which is correct depends on whether NULL means "no score yet" or "scored zero".

Does COALESCE in a WHERE clause affect index usage?

Yes, usually destructively. WHERE COALESCE(col, 'unknown') = 'active' cannot use a B-tree index on col because the optimizer cannot see through the function call to the bare column. The same is true of LOWER, UPPER, TRIM, CAST, and other column-wrapping functions. Fixes include rewriting the predicate as WHERE col = 'active' OR col IS NULL or building a functional index on the expression.

Related reading

• SQL window functions interview questions
• CASE WHEN in SQL: full guide
• NULL in SQL: a working analyst's guide

If you want to drill SQL patterns like this every day, NAILDD is launching with 500+ analytics problems across exactly this shape.

FAQ

Does COALESCE work in every major SQL engine?

Yes. COALESCE is part of the ANSI SQL standard and behaves identically in PostgreSQL, MySQL, ClickHouse, BigQuery, Snowflake, SQL Server, Oracle, Redshift, and DuckDB. ISNULL exists only in SQL Server with a two-argument shape, IFNULL exists in MySQL and SQLite, NVL exists in Oracle, but COALESCE is the one form that runs everywhere unmodified.

How many arguments can COALESCE take?

The standard does not set a hard limit, and in practice engines accept dozens. Chains of more than three or four arguments are uncommon and usually signal a schema problem, where the same logical field has been stored across many physical columns. Consolidating those columns or moving to a JSON field is often the better fix than a long COALESCE chain.

What is the difference between COALESCE and NULLIF?

They are inverse operations. COALESCE turns NULL into a chosen value; NULLIF turns a chosen value into NULL. They combine cleanly: COALESCE(NULLIF(col, ''), 'fallback') treats empty strings as NULL and then substitutes a default, which is the standard way to handle "logically missing but stored as blank" columns. NULLIF is also the safe-division idiom — x / NULLIF(y, 0) returns NULL when y is zero instead of throwing a divide-by-zero error.

Should I store COALESCE values in the table, or apply them at read time?

Apply them at read time. Storing a sentinel value like 'unknown', 0, or -1 bakes a presentation convention into the schema and confuses every downstream consumer about whether the value is really known. Keep NULL in the storage layer to mean "absent or unknown" and apply COALESCE at the SELECT or BI layer. The exception is when the sentinel has a real semantic meaning in your domain and is documented as such.

Is COALESCE slow on large tables?

The function itself is essentially free — the engine evaluates it row by row and a non-NULL argument short-circuits the rest. The slowness comes from COALESCE on indexed columns inside WHERE clauses, where it disables index usage and forces a full scan. In SELECT and ORDER BY it is cheap. The fix in WHERE is either a functional index or rewriting the predicate so the indexed column appears bare.