COALESCE vs ISNULL in SQL

Why this question keeps coming up

Picture the scene. You are interviewing for a data analyst role at Stripe, the screen-share opens, and the engineer drops a table with a sparse nickname column. The prompt sounds harmless: "Return a display name. Fall back to first name, then to the string Guest." You type ISNULL(nickname, first_name, 'Guest') out of habit and the syntax checker barks. The interviewer asks why you reached for ISNULL and what it would do differently in PostgreSQL. That is the moment every SQL analyst either earns or loses a follow-up loop.

COALESCE and ISNULL are the two NULL-replacement functions that look interchangeable on a sample of two arguments and then diverge sharply the moment you scale them up, port them across engines, or pass them strings of different lengths. The confusion is amplified by MySQL having IFNULL, ClickHouse having ifNull, Oracle having NVL, and Snowflake quietly accepting all of them. New hires who learn SQL in one dialect carry that habit into every interview, and senior engineers love watching the answer reveal which dialect you cut your teeth on.

What COALESCE actually does

COALESCE is a variadic function defined in the ANSI/ISO SQL standard. It accepts two or more expressions, evaluates them left to right, and returns the first one that is not NULL. If every argument is NULL, the function returns NULL. Because it is part of the standard, every mainstream engine implements it: PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Redshift, ClickHouse, DuckDB, SQLite, Databricks SQL — the whole stable.

-- Plain fallback
SELECT
    user_id,
    COALESCE(nickname, first_name, 'Guest') AS display_name
FROM users;

-- Attribution chain
SELECT
    visit_id,
    COALESCE(utm_source, referrer_host, 'direct') AS traffic_source
FROM web_visits;

A subtle property worth knowing: the SQL spec defines COALESCE(a, b) as syntactic sugar for CASE WHEN a IS NOT NULL THEN a ELSE b END. That is why some engines short-circuit the evaluation, which matters when one of the arguments calls an expensive subquery or a UDF. PostgreSQL and SQL Server both stop evaluating the moment they find a non-NULL value.

The return type is decided by data type precedence. With COALESCE, the result takes the highest-precedence type among the arguments, so if you mix VARCHAR(5) and VARCHAR(50), the result is VARCHAR(50). This single rule is the cleanest argument for using COALESCE even when you have only two arguments and could technically reach for ISNULL.

What ISNULL actually does

ISNULL is a SQL Server built-in. It accepts exactly two arguments and returns the second if the first is NULL, otherwise the first. It is not in the SQL standard, and PostgreSQL, MySQL, ClickHouse, and Snowflake do not have it. MySQL has a one-argument ISNULL(expr) that returns a boolean — a different function entirely — and that quirk has burned more than one analyst migrating reports from MySQL to SQL Server.

-- SQL Server
SELECT
    user_id,
    ISNULL(nickname, 'Guest') AS display_name
FROM users;

-- Numeric replacement
SELECT
    product_id,
    ISNULL(discount_pct, 0) AS discount_pct
FROM catalog;

The return type rule is where ISNULL and COALESCE part ways. ISNULL coerces the replacement value to the data type of the first argument. If the first argument is VARCHAR(5) and you pass a longer literal, the literal is silently truncated to fit. That behaviour is documented and consistent, but it is the single most common reason production reports show Guest... chopped to Guest, or a 100-character fallback message rendered as a five-character stub. The safest habit, if you do not have a hard reason to optimise for SQL Server, is to never type ISNULL at all.

The key differences side by side

The number of rows where this table maps to a real production decision is small. The number of interview rounds where saying "COALESCE is ANSI, ISNULL is SQL Server, and the type coercion rule differs" lands you a thumbs-up is large. Memorise the four rows under "Return type", "Argument count", "SQL standard", and "Works in PostgreSQL".

Worked examples by dialect

When the engine matters, the syntax matters. Here is the same intent — "give me a display name, falling back through three fields" — written for the engines you are most likely to meet in 2026 interviews.

-- PostgreSQL, Snowflake, BigQuery, Redshift, DuckDB
SELECT COALESCE(nickname, first_name, email, 'Guest') AS display_name
FROM users;

-- SQL Server without COALESCE (don't do this in real code, but interviewers ask)
SELECT ISNULL(nickname, ISNULL(first_name, ISNULL(email, 'Guest'))) AS display_name
FROM users;

-- SQL Server with COALESCE (recommended)
SELECT COALESCE(nickname, first_name, email, 'Guest') AS display_name
FROM users;

-- MySQL
SELECT COALESCE(nickname, first_name, email, 'Guest') AS display_name
FROM users;
-- IFNULL also works for two-arg cases
SELECT IFNULL(nickname, 'Guest') AS display_name FROM users;

-- ClickHouse
SELECT COALESCE(nickname, first_name, email, 'Guest') AS display_name
FROM users;
-- ClickHouse also offers ifNull(a, b) for the two-arg case

The right-hand habit to build is: type COALESCE, with capital letters, regardless of engine. It works in every dialect listed above. The only valid reason to type ISNULL is that your team's SQL style guide for a SQL Server warehouse mandates it, and even then you should pause and consider whether silent truncation is worth the two saved keystrokes.

Common pitfalls

The truncation trap is the one that costs analysts data quality bugs in production. When ISNULL(@name, 'Guest player') runs and @name is declared as VARCHAR(5), SQL Server returns Guest because the replacement is coerced to the first argument's type. Reports look fine in spot-checks because real names happen to fit, then a fallback row arrives, the substring gets cut, and a downstream join fails on a string that was supposed to be unique. The fix is either to widen the declared column type or to use COALESCE, which picks the wider type automatically.

Aggregation around NULL is the second pitfall, and it is the one most analysts cannot articulate cleanly. SUM(bonus) over a column of all NULL values returns NULL, not zero. Wrapping the aggregate in COALESCE(SUM(bonus), 0) is correct and what you want for downstream arithmetic. Wrapping the column itself in SUM(COALESCE(bonus, 0)) produces the same number for sums but masks information when you actually want to count non-NULL rows separately. Decide which one you want before you type, and explain the choice if asked.

Indexed columns wrapped in COALESCE lose their index in most engines. WHERE COALESCE(status, 'unknown') = 'active' cannot use a B-tree index on status because the expression is no longer a bare column reference. The fix in OLTP code is either a partial index covering the predicate or a rewrite using WHERE status = 'active' OR (status IS NULL AND 'unknown' = 'active'), which the optimiser can split. Analytics workloads on columnar engines like Snowflake, BigQuery, and ClickHouse usually do not care, because they scan and prune by partition rather than by index, but knowing the difference shows up in senior-level interviews.

COALESCE with mixed numeric types can promote integers to decimals or floats unexpectedly. COALESCE(integer_column, 0.5) returns a NUMERIC or DECIMAL value in PostgreSQL because the literal is a decimal. If your downstream code expects an integer, cast explicitly with COALESCE(integer_column, 0)::INTEGER or pick a same-typed literal. This bug is rare but vicious because the column types in your output schema drift between deploys.

The last one is NULLIF confusion. NULLIF(a, b) returns NULL when a = b, otherwise a. It is the inverse of COALESCE: where COALESCE removes nulls, NULLIF introduces them. The canonical use is NULLIF(denominator, 0) to protect a division from blowing up, which then pairs neatly with another COALESCE to return zero instead of NULL in the final output. People who confuse the two on an interview generally have not actually shipped a division-by-zero fix in production.

Performance and index behaviour

COALESCE itself is cheap — a constant-time function that evaluates left to right and returns. The interesting cost is what it blocks. The index point above is the big one for OLTP. The second cost is short-circuit semantics with side effects: if you write COALESCE(maybe_null_column, expensive_function(x)), the engine still parses and plans the second argument even when it may not evaluate it, which in some engines changes parameter sniffing and query plan caching.

In columnar warehouses, COALESCE runs vectorised and adds essentially nothing to the per-row cost. Where it does hurt is partition pruning. If you partition on event_date and write WHERE COALESCE(event_date, '1970-01-01') = '2026-05-18', the engine cannot prune partitions because the column is wrapped in a function. Keep the partition column bare and handle the NULL branch separately with a UNION ALL or with (event_date = '2026-05-18' OR event_date IS NULL) when that is the semantics you want.

For ML feature pipelines, COALESCE is the right way to fill missing values in SQL before exporting features to Python. The pattern of COALESCE(numeric_feature, median_value) is deterministic and avoids the train-test leakage that comes from imputing in Pandas across the full dataset. Compute the median in a CTE that only sees the training rows, then join it back.

Related reading

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

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

FAQ

Is there an ISNULL equivalent in PostgreSQL?

Not directly. The function ISNULL does not exist in PostgreSQL, and the operator IS NULL returns a boolean rather than performing a replacement. The correct replacement function in PostgreSQL is COALESCE, which does everything ISNULL does and more. If you are porting SQL Server code, a global find-and-replace from ISNULL(a, b) to COALESCE(a, b) is safe as long as you double-check the truncation behaviour: COALESCE will not truncate strings that ISNULL would.

Are COALESCE and CASE WHEN the same thing?

Functionally, COALESCE(a, b) is defined in the SQL standard as shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END. In practice, COALESCE is more readable for chains of three or more arguments and most query optimisers handle it identically. The one place they diverge is type coercion: CASE follows the same precedence rules as COALESCE, but you have more room to make a mistake with explicit casts. Use COALESCE for NULL replacement and reserve CASE WHEN for conditional logic that is not a simple null fallback.

Does COALESCE hurt query performance?

For ordinary column references, no. COALESCE is a constant-time function evaluated per row, and modern columnar engines run it vectorised. The performance issues come from wrapping indexed or partitioned columns in COALESCE, which prevents index lookups and partition pruning. A query like WHERE COALESCE(status, 'unknown') = 'active' will table-scan even if status is indexed. Rewrite as WHERE status = 'active' if the NULL case does not match, or split into a UNION ALL when it does.

What should I use in ClickHouse for NULL replacement?

ClickHouse supports COALESCE directly and also offers ifNull(a, b) as the two-argument equivalent. The case-sensitive names are coalesce and ifNull; ClickHouse is generally case-insensitive for function names so capitalisation does not matter functionally. For analytical workloads where readability matters, prefer COALESCE because anyone coming from another engine will understand it immediately. Reserve ifNull for code that is already idiomatic ClickHouse and where the rest of the codebase uses camelCase function names.

What is NULLIF and how does it relate to COALESCE?

NULLIF(a, b) is the mirror image of COALESCE: it returns NULL when a equals b, and returns a otherwise. The classic pattern is NULLIF(denominator, 0) to make a division by zero return NULL instead of raising an error, which you can then wrap in another COALESCE to return a sensible default. The combined idiom COALESCE(numerator / NULLIF(denominator, 0), 0) is the safest way to compute ratios in SQL without crashing on empty cohorts, and it is one of the most-tested patterns in analyst interviews because it shows you understand both functions and the semantics of NULL arithmetic.

How do I handle the truncation trap on SQL Server without switching to COALESCE?

If your codebase mandates ISNULL, the workaround is to cast the first argument to a type wide enough to hold any replacement value. Writing ISNULL(CAST(name AS VARCHAR(255)), 'Long default name') removes the truncation because the first argument is now wide enough. The downside is verbosity and the chance of forgetting the cast on a new query. The honest recommendation is to push back on the style guide and standardise on COALESCE, because the cost of one silent truncation in production is much higher than the cost of changing six characters in a linter rule.