COALESCE in SQL: A Practical Guide with Examples

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

COALESCE is a SQL function that returns the first non-NULL value from its argument list. It is part of the ANSI SQL standard and is supported natively in PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and ClickHouse. If you have ever written a query that broke because a column was NULL, COALESCE is usually the fix.

In this post, we’ll cover:

• What COALESCE does - The short answer with a running example
• Everyday uses - Default values, concatenation, and aggregations
• COALESCE vs IFNULL, NVL, and ISNULL - Which one works in which dialect
• Pitfalls - Short-circuit evaluation, type coercion, and aggregate behavior
• Try it yourself - Run every example in your browser

What COALESCE does

COALESCE(a, b, c, ...) returns the first argument that is not NULL. If every argument is NULL, it returns NULL. It accepts two or more arguments and evaluates them left to right.

SELECT COALESCE(NULL, NULL, 'hello', 'world');
-- returns 'hello'

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

The most common use is replacing a NULL column with a default value in a SELECT:

SELECT
  id,
  COALESCE(nickname, full_name, 'Anonymous') AS display_name
FROM users;

If nickname is not NULL, that is what you get. Otherwise full_name. If both are NULL, the row falls back to 'Anonymous'. You get one clean expression instead of a nested CASE.

Everyday uses of COALESCE

Default values in projections

Any time a column is nullable and you want a human-friendly string in a report or dashboard, wrap it in COALESCE:

SELECT
  order_id,
  COALESCE(tracking_number, 'Not yet shipped') AS tracking_number,
  COALESCE(shipped_at, NOW()) AS shipped_at_or_now
FROM orders;

This is especially useful when the output feeds an internal tool or a CSV export, because consumers do not have to handle the NULL case separately.

Safe string concatenation

In PostgreSQL, the || concatenation operator returns NULL when any operand is NULL, which silently destroys otherwise fine output:

SELECT first_name || ' ' || last_name AS name
FROM users;
-- returns NULL for any row where first_name OR last_name is NULL

COALESCE each piece to an empty string and you keep whatever data you have:

SELECT
  COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS name
FROM users;

This same pattern shows up when building addresses, SKUs, or search keys from multiple nullable columns.

NULL-safe aggregates and totals

SUM, AVG, and friends skip NULL values, so SUM(NULL) = NULL. This becomes a problem when you aggregate a filtered subset that has no matching rows:

SELECT
  user_id,
  SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

Users with no completed orders get total_spent = NULL instead of 0. Downstream code that does arithmetic on the result then produces more NULLs. The fix is to COALESCE the aggregate:

SELECT
  user_id,
  COALESCE(SUM(amount), 0) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

Fallback lookups across columns

When a record has multiple possible sources for the same value — for example, a user’s preferred email versus a billing email versus a signup email — COALESCE gives you a priority-ordered fallback:

SELECT
  user_id,
  COALESCE(preferred_email, billing_email, signup_email) AS contact_email
FROM users
WHERE COALESCE(preferred_email, billing_email, signup_email) IS NOT NULL;

COALESCE in WHERE clauses

WHERE col = 'x' is always false when col is NULL, because any comparison with NULL produces UNKNOWN. If you want NULL to behave like a default value in your filter, COALESCE both sides:

-- treat NULL status as 'active' for filtering
SELECT *
FROM users
WHERE COALESCE(status, 'active') = 'active';

Be aware that wrapping a column in a function makes the predicate non-sargable, meaning it usually prevents the planner from using an index on status. For tables where this matters, add a partial or expression index, or change the schema so status is NOT NULL with a default.

COALESCE vs IFNULL, NVL, and ISNULL

Several dialects ship dialect-specific two-argument variants of COALESCE. They all do roughly the same thing, but they are not interchangeable across databases.

Function	Databases	Arguments	Notes
COALESCE	ANSI standard — works everywhere	Two or more	Variadic; preferred default
IFNULL	MySQL, SQLite	Exactly two	Returns first if not NULL, else second
NVL	Oracle	Exactly two	Oracle also has NVL2 and NULLIF
ISNULL	SQL Server	Exactly two	In PostgreSQL, IS NULL is an operator, not this function
NULLIF	ANSI standard	Exactly two	Opposite direction — returns NULL when the two args are equal

If you want your SQL to move cleanly between PostgreSQL and MySQL (for example in a migration test harness), always use COALESCE. It is the only option that works identically everywhere.

Practical PostgreSQL patterns

COALESCE with JSONB keys

PostgreSQL’s -> and ->> return NULL when a key is missing. COALESCE makes downstream logic predictable:

SELECT
  id,
  COALESCE(preferences->>'theme', 'system') AS theme,
  COALESCE((preferences->>'notify_email')::boolean, TRUE) AS notify_email
FROM users;

This pattern is common in tables that store semi-structured user settings in a JSONB column — PostgreSQL’s recommended approach for flexible per-user config.

COALESCE with window functions

LAG and LEAD return NULL at the edges of the window. COALESCE to a sensible default when you plan to do arithmetic on the result (see our SQL window functions guide for the full set of OVER patterns):

SELECT
  order_id,
  placed_at,
  COALESCE(
    placed_at - LAG(placed_at) OVER (PARTITION BY user_id ORDER BY placed_at),
    INTERVAL '0'
  ) AS time_since_last_order
FROM orders;

COALESCE in UPDATE

COALESCE(new_value, column) is the standard way to implement a partial update where only changed fields are passed in:

UPDATE users
SET
  display_name = COALESCE($1, display_name),
  avatar_url   = COALESCE($2, avatar_url),
  updated_at   = NOW()
WHERE id = $3;

Pass NULL for a field and it stays untouched. Pass a value and it overwrites. This is how most REST and GraphQL backends implement partial updates without writing a different SQL statement per field.

Pitfalls to watch for

Type coercion surprises

Every argument to COALESCE has to be convertible to a common type. PostgreSQL resolves the types left to right, which produces helpful errors but also some surprises:

-- works: both sides resolve to text
SELECT COALESCE(nickname, 'Anonymous') FROM users;

-- fails: can't mix text and integer
SELECT COALESCE(nickname, 0) FROM users;
-- ERROR: COALESCE types text and integer cannot be matched

Cast explicitly when mixing types:

SELECT COALESCE(nickname, '0'::text) FROM users;

The same rule applies to NUMERIC vs INTEGER, TIMESTAMP vs DATE, and any other pair where the implicit cast is ambiguous. Being explicit is almost always clearer for future readers too.

Short-circuit evaluation is a correctness feature, not a performance one

PostgreSQL documents that COALESCE short-circuits: later arguments are not evaluated once a non-NULL value is found. In practice this prevents errors:

-- This would divide by zero if COALESCE evaluated all args.
-- Because of short-circuit, the division is never reached.
SELECT COALESCE(cached_rate, total_amount / NULLIF(quantity, 0))
FROM invoices;

The takeaway is not “put expensive things on the right to save work” — the planner can still evaluate in other orders for subqueries. The takeaway is “COALESCE is safe to use with expressions that would fail on bad input, as long as an earlier argument guarantees a valid value.”

COALESCE inside aggregates vs around aggregates

These two look similar but do completely different things:

SELECT SUM(COALESCE(amount, 0)) FROM orders;
-- treats every NULL row as 0 and sums the result

SELECT COALESCE(SUM(amount), 0) FROM orders;
-- sums non-NULL rows (SUM ignores NULLs) then
-- replaces the whole result with 0 if the table is empty

For most reporting queries, the second form is the one you want. For the rare case where NULL actually means “zero” in your business logic, the first is correct. Pick deliberately.

Don’t use COALESCE to paper over real nullability problems

If a column is “never actually NULL but the schema allows it” and you are sprinkling COALESCE throughout the codebase, the better fix is to add NOT NULL and a default. COALESCE is a query-time patch. Schema-level constraints remove the problem entirely.

Try these examples yourself

You can paste any of the queries above into our free SQL playground to see COALESCE behavior against PostgreSQL, MySQL, SQLite, and ClickHouse — no account or database connection needed. For pre-flight checks on complex queries, the SQL syntax checker catches problems before you run them in production.

For the full workflow — write SQL with AI, run it against your real PostgreSQL database, and turn the results into an internal tool — connect a database to QueryPlane and skip straight to building.

Frequently asked questions

What does COALESCE do in SQL? COALESCE returns the first non-NULL argument from a list of expressions. If every argument is NULL, it returns NULL. It is part of the ANSI SQL standard and works in PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and ClickHouse without dialect-specific syntax.

What is the difference between COALESCE and IFNULL? IFNULL(a, b) accepts exactly two arguments. COALESCE(a, b, c, …) accepts any number of arguments. COALESCE is also ANSI SQL standard, while IFNULL is MySQL/SQLite-specific (NVL is Oracle’s equivalent, ISNULL is SQL Server’s). Prefer COALESCE for portability.

Does COALESCE evaluate all of its arguments? No — COALESCE short-circuits. Once it finds a non-NULL argument, it stops evaluating the remaining ones. This makes it safe to put expensive expressions or expressions that could error (like division) after a guaranteed non-NULL fallback.

Can COALESCE return different data types? All arguments to COALESCE must be coercible to a common type. PostgreSQL applies type-resolution rules — if you pass an INTEGER and a TEXT, you’ll get a type error rather than a silent cast. Cast explicitly when mixing types: COALESCE(int_col, '0'::int) or COALESCE(int_col::text, 'unknown').

Why is my COALESCE returning NULL instead of the default? The most common cause is that the “default” argument is also NULL — for example, COALESCE(col, other_col) where both columns are NULL for that row. Use a literal as the final argument to guarantee a non-NULL result: COALESCE(col, other_col, 'unknown').

Should I use COALESCE or set the column to NOT NULL with a default? If the column is conceptually never NULL and you find yourself wrapping it in COALESCE across many queries, fix the schema instead. ALTER TABLE … ALTER COLUMN … SET NOT NULL plus a DEFAULT removes the need for COALESCE and lets the optimizer reason about the column more aggressively.

Wrapping up

COALESCE is one of the most frequently used functions in real-world SQL, and it is doing one small thing: return the first non-NULL value in an argument list. The tricky parts are almost always about types, aggregate placement, and recognizing when a COALESCE is really a schema smell.

Reach for COALESCE for default values, safe string concatenation, fallback lookups, and partial-update statements. Prefer it over dialect-specific IFNULL, NVL, and ISNULL so that your queries remain portable. And when you find yourself wrapping the same column in COALESCE across dozens of queries, consider whether the column should be NOT NULL with a default instead.