COALESCE in SQL: the analyst cheat sheet

Why COALESCE matters

COALESCE quietly carries every dashboard you have ever shipped. NULL breaks almost everything an analyst touches: SUM() over an empty group returns NULL instead of 0, a LEFT JOIN without a match leaves NULL in the right-hand columns, and any arithmetic involving NULL collapses the whole expression to NULL. Without a fallback strategy, your "revenue per user" tile shows blanks for half the users and product asks why the dashboard is broken on Monday morning.

COALESCE solves problems where the answer is "if this value is missing, use the next best one". Picture a Stripe-style billing table where premium subscribers have a paid_plan_price, free-tier users have a NULL price but a default_plan_price from another table, and a small slice has neither. A single COALESCE(paid_plan_price, default_plan_price, 0) gives you a usable number in every row. Multiply that across activation rate, retention, ARPU, and engagement, and COALESCE becomes the single most-used null-safety primitive in the analyst toolkit.

Interviewers know this. At Meta, Stripe, DoorDash, and Snowflake, roughly one in three SQL screens hinges on COALESCE — whether explicitly ("give me LTV with no NULLs") or implicitly through a left-join takehome. Knowing the syntax is table stakes; knowing the edge cases (empty strings, zero vs NULL, type coercion, NULLIF pairing, short-circuit evaluation) separates a middle data analyst from a senior one. This cheat sheet covers every pattern you need.

What COALESCE actually does

The function returns the first non-NULL value from a left-to-right scan of its arguments. The signature looks like this in every major engine — Postgres, MySQL, SQL Server, Snowflake, BigQuery, Redshift, Databricks, DuckDB.

COALESCE(expression_1, expression_2, ..., expression_n)

You can pass two arguments or twenty. If every argument evaluates to NULL, the result is NULL. That last detail is the one beginners forget — COALESCE does not magically guarantee a non-NULL output. It guarantees the first non-NULL output from the list you provided. If your list is all NULLs, you get NULL back, which is why a sensible final argument (0, 'unknown', '') is usually part of the pattern.

SELECT COALESCE(NULL, NULL, 'hello', 'world');  -- 'hello'
SELECT COALESCE(NULL, 42, 100);                 -- 42
SELECT COALESCE(NULL, NULL, NULL);              -- NULL
SELECT COALESCE(NULL, NULL, NULL, 'fallback');  -- 'fallback'

Eight patterns you will use weekly

These are the eight templates that cover roughly 95% of analyst use cases. Memorize the shape; the arguments change but the structure repeats.

1. Replace NULL with a display default

The classic. A user table has nullable phone_number and the dashboard needs a non-empty string.

SELECT
    user_id,
    COALESCE(phone_number, 'NOT provided') AS phone_display
FROM users;

2. Fallback chain by priority

Try the primary contact, then the secondary, then a phone, then a sentinel value. Order matters — left to right is your priority list.

SELECT
    user_id,
    COALESCE(primary_email, secondary_email, phone_number, 'no_contact') AS contact
FROM users;

3. Aggregate over an empty group

SUM, MAX, AVG all return NULL when the group has zero rows. Wrap them.

SELECT
    user_id,
    COALESCE(SUM(amount_usd), 0) AS total_spent_usd,
    COALESCE(MAX(order_at), '1970-01-01') AS last_order_at
FROM orders
GROUP BY user_id;

4. LEFT JOIN with multi-source defaults

You have promo prices in one table and list prices in another. Pick whichever is present.

SELECT
    u.user_id,
    COALESCE(p.promo_price_usd, l.list_price_usd, 0) AS final_price_usd
FROM users u
LEFT JOIN promo_prices  p ON p.user_id = u.user_id
LEFT JOIN list_prices   l ON l.user_id = u.user_id;

5. COALESCE plus NULLIF for empty strings and zeros

This is the pattern senior interviewers look for. A blank string '' is not NULL — COALESCE(name, 'unknown') will happily return ''. Convert the empty value to NULL with NULLIF first.

SELECT
    user_id,
    COALESCE(NULLIF(display_name, ''), 'unknown') AS display_name_clean
FROM users;

The same trick avoids division by zero. NULLIF(denominator, 0) turns zero into NULL, the division yields NULL, and the outer COALESCE substitutes a safe value.

SELECT
    cohort_month,
    COALESCE(retained_users * 1.0 / NULLIF(cohort_size, 0), 0) AS retention_rate
FROM cohorts;

6. Force NULLs to the end of an ORDER BY

Some engines sort NULL first, others sort it last, and the SQL standard leaves it undefined. If you want a portable behavior, wrap the sort key.

SELECT *
FROM users
ORDER BY COALESCE(last_login_at, '1900-01-01') DESC;

Where supported, ORDER BY last_login_at DESC NULLS LAST is cleaner. Postgres, Snowflake, BigQuery, and Oracle support it natively; MySQL and SQL Server do not, so COALESCE remains the cross-engine workaround.

7. Pick the most recent date across columns

A user has last_login_at, last_purchase_at, and last_session_at — any of them might be NULL. Use GREATEST over a COALESCE'd set so a NULL on one column does not nullify the whole expression in engines where GREATEST is NULL-propagating.

SELECT
    user_id,
    GREATEST(
        COALESCE(last_login_at,    '1900-01-01'),
        COALESCE(last_purchase_at, '1900-01-01'),
        COALESCE(last_session_at,  '1900-01-01')
    ) AS last_activity_at
FROM user_activity;

8. COALESCE inside GROUP BY

Group "unknown country" rows into a single bucket instead of dropping them.

SELECT
    COALESCE(country_code, 'unknown') AS country,
    COUNT(*) AS user_count
FROM users
GROUP BY COALESCE(country_code, 'unknown');

COALESCE vs ISNULL vs IFNULL vs NVL

Different engines ship different two-argument shortcuts. They all do roughly the same thing — return the second value if the first is NULL — but with subtle behavior differences around type coercion and argument count. The portable answer is always COALESCE.

A real bug from this difference: in SQL Server, ISNULL(short_column, 'this is a longer fallback string') silently truncates the fallback to fit the type of the first argument. COALESCE follows the rules of CASE and uses the highest-precedence type, which is what you almost always want.

Type compatibility and casting

COALESCE requires all arguments to be of compatible types. If they are not, the engine either coerces them (sometimes surprisingly) or throws an error. Mixing a TEXT column with an integer literal is the most common offender.

-- Errors in strict engines: phone_number is TEXT, 0 is INTEGER
COALESCE(phone_number, 0);

-- Fix: keep the result type explicit
COALESCE(phone_number, '0');

-- Or cast the column
COALESCE(CAST(phone_number AS INTEGER), 0);

Date columns trip people up too. A timestamp with timezone and a plain date will sometimes coerce, sometimes not — Postgres is strict, MySQL is forgiving, Snowflake sits in the middle. When in doubt, cast both sides to the same type explicitly and skip the implicit-conversion lottery.

Common pitfalls

Empty strings are not NULL. This is the single most common COALESCE bug in production analytics code. A user signs up without a display name, your app stores '' instead of NULL, and COALESCE(display_name, 'anonymous') returns the empty string. The fix is COALESCE(NULLIF(display_name, ''), 'anonymous') — turn the empty string into a true NULL, then fall back. The same trap applies to zeros that should be treated as missing, whitespace-only strings, and placeholder sentinels like 'N/A' or -1 that leaked in from upstream ETL.

Comparing to NULL with = quietly drops rows. WHERE column = NULL returns no rows because NULL is not equal to anything, including itself — even NULL = NULL evaluates to NULL, not TRUE. COALESCE does not help because the comparison happens before it can intervene. Use IS NULL and IS NOT NULL for null tests. The closest COALESCE workaround is WHERE COALESCE(column, 0) = 0, which catches both literal zeros and NULLs, but you usually want them separated anyway.

Forgetting the final fallback. COALESCE(a, b, c) returns NULL when all three are NULL, so the column you swore would never be null still ends up with NULLs in production. Always close the chain with a guaranteed non-NULL literal — a number, a string, a sentinel date — unless you genuinely want NULL when everything is missing. Reviewers should flag any COALESCE chain whose final argument is itself a nullable column.

Confusing COALESCE with NVL2 or IIF. Some engines provide NVL2(a, b, c) (Oracle) or IIF(cond, a, b) (SQL Server) for conditional logic. These are not interchangeable. If your logic is "if X is set, do Y, else do Z", that is a CASE expression. Reach for COALESCE only when the answer is "the first non-NULL from this prioritized list".

Mixing COALESCE with aggregates inside HAVING. HAVING COALESCE(SUM(amount), 0) > 100 works, but HAVING SUM(COALESCE(amount, 0)) > 100 is usually what you actually want — the outer COALESCE only fixes the empty-group case, while the inner one fixes individual missing values inside the group. Get this wrong and your aggregation silently drops rows that should have rolled up.

Performance and short-circuit evaluation

COALESCE is short-circuit evaluated in every major engine. If the first argument is not NULL, the second and subsequent arguments are not computed. This matters when later arguments are expensive — correlated subqueries, joins, window functions, UDF calls.

-- The subquery only runs for rows where preferred_name IS NULL.
SELECT
    user_id,
    COALESCE(
        preferred_name,
        (SELECT display_name FROM user_profiles WHERE user_profiles.user_id = u.user_id)
    ) AS name
FROM users u;

The corollary: if you have a cheap fallback and an expensive primary value, put the cheap one first only if its NULL-ness genuinely tells you something. Otherwise you defeat short-circuit evaluation. Most query planners will reorder predicates, but they will not reorder COALESCE arguments — left-to-right is contractual.

Indexes are not used through COALESCE in a WHERE clause. WHERE COALESCE(country_code, 'unknown') = 'unknown' will full-scan even if country_code is indexed, because the planner cannot push the COALESCE into the index lookup. Rewrite as WHERE country_code IS NULL OR country_code = 'unknown' when index hits matter. The same applies to JOIN conditions — keep COALESCE on the SELECT list, not on the join key.

Related reading

• NULL in SQL: the cheat sheet
• CASE WHEN in SQL: cheat sheet
• SQL window functions interview questions

If you want to drill SQL patterns like this every day, NAILDD is launching with 500+ SQL problems built around exactly this kind of edge case.

FAQ

What is the difference between COALESCE and ISNULL?

COALESCE is the ANSI SQL standard and works in every major engine — Postgres, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Redshift, Databricks, DuckDB. It accepts two or more arguments and returns the first non-NULL. ISNULL is a vendor-specific shortcut limited to two arguments and behaves differently between MySQL and SQL Server, especially around result-type coercion. The recommendation is to default to COALESCE in all new code and to refactor ISNULL to COALESCE whenever you touch legacy queries.

What does COALESCE return when every argument is NULL?

NULL. There is no special-case behavior. If you want a non-NULL guarantee, the final argument has to be a non-NULL literal — a number, a string, a known-safe date. A common review comment is "this COALESCE chain ends with a nullable column, so it can still produce NULL", and that is almost always a bug rather than the intent.

Can I use COALESCE inside GROUP BY?

Yes. You group by the same expression you select. SELECT COALESCE(country, 'unknown'), COUNT(*) FROM users GROUP BY COALESCE(country, 'unknown') is idiomatic and produces a clean bucket for missing countries. Many engines also accept GROUP BY 1 to reference the first SELECT column by position, which is shorter but more fragile when columns get reordered.

Why does COALESCE not handle empty strings?

Because an empty string is not NULL — it is a zero-length string, which is a fully valid non-NULL value. COALESCE checks for NULL specifically, so an empty string passes through unchanged. The standard workaround is to wrap the column in NULLIF(col, ''), which converts the empty string to NULL, and then let the outer COALESCE substitute the fallback. The full idiom is COALESCE(NULLIF(col, ''), 'fallback').

Should I use COALESCE or CASE WHEN?

Use COALESCE when the logic is "return the first non-NULL from this list" — it is shorter, more readable, and signals intent to anyone reading the query. Use CASE WHEN when the logic involves any condition other than null-ness — comparisons, range checks, business rules. The two are technically interchangeable for the null-coalescing case (CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END), but the COALESCE form is far cleaner.

Does COALESCE evaluate all arguments?

No. Every major engine implements short-circuit evaluation: arguments after the first non-NULL value are not computed. This is critical when later arguments contain expensive subqueries, function calls, or joins. It also means side-effecting functions placed in later positions may not fire — something to watch for if you ever embed a logging UDF or a sequence-advancing call in a COALESCE chain, which you should not anyway.