SQL COALESCE Function: Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

COALESCE in SQL returns the first non-NULL value from a list of expressions. You use it to replace NULLs with a default, merge values from several columns, or pick the first available value across a set of candidates. This article covers the syntax, a worked example, common mistakes and the limits of the function.

Quick Answer

COALESCE(expr1, expr2, ...) evaluates its arguments left to right and returns the first one that is not NULL.
It returns NULL only when every argument is NULL.
All arguments must be compatible data types, so the database can decide on a single result type.
It is standard SQL and works in SQLite, PostgreSQL, MySQL, SQL Server, Oracle and others.
ISNULL is not the same thing. In SQL Server, ISNULL(check_expression, replacement_value) takes exactly two arguments. In MySQL, ISNULL(expr) is a test that returns 1 or 0.

Syntax

COALESCE(expression1, expression2, ..., expressionN)

Argument	Required?	Meaning
`expression1`	Yes	The first value to test. Returned if it is not NULL.
`expression2` ... `expressionN`	No	Fallback values, tested in order. The first non-NULL one is returned.

PostgreSQL and MySQL accept a single argument, in which case COALESCE(x) simply returns x, including NULL. SQLite and SQL Server require at least two arguments. With two or more, it behaves like a chain of CASE expressions:

$$ \text{COALESCE}(a, b, c) \equiv \text{CASE WHEN } a \text{ IS NOT NULL THEN } a \text{ WHEN } b \text{ IS NOT NULL THEN } b \text{ ELSE } c \text{ END} $$

That equivalence matters because it explains the type rules. A CASE expression must produce one type, and so must COALESCE.

How It Works

The database evaluates arguments from left to right and stops at the first non-NULL value. Arguments after that point are not returned, though the database may still evaluate them depending on the engine and the expressions involved. Do not rely on COALESCE to skip expensive subqueries or functions.

The result type is determined from all the arguments together. If you mix types that cannot be reconciled, the query fails before it returns any rows. A PostgreSQL discussion of this behavior shows the error COALESCE types smallint and character varying cannot be matched, and notes that CASE and Oracle's NVL raise similar complaints [1]. The fix is an explicit cast, for example COALESCE(CAST(number AS varchar(100)), name) [1].

Because COALESCE is a standard expression, it can appear anywhere an expression is allowed: in the SELECT list, in WHERE, in ORDER BY, in GROUP BY, and inside other functions. In SQL Server's parser it is modeled as a CoalesceExpression, a kind of primary expression, which is why it composes so freely with the rest of a query [2].

Worked Example

The customers table below has five rows. Two customers have no phone number recorded, so phone is NULL for them.

customer_id	first_name	last_name	phone
1	Alice	Johnson	555-0101
2	Bob	Smith	NULL
3	Carol	Williams	555-0103
4	David	Brown	NULL
5	Eve	Davis	555-0105

To show a readable label instead of a blank, wrap phone in COALESCE with a fallback string.

SELECT
  customer_id,
  first_name,
  last_name,
  COALESCE(phone, 'unknown') AS phone_display
FROM customers
ORDER BY customer_id;

The query works in four steps. First, SELECT customer_id, first_name, last_name, phone FROM customers retrieves the base columns, including the NULL phone values. Second, COALESCE(phone, 'unknown') returns the phone value when it is not NULL and the string 'unknown' otherwise. Third, AS phone_display gives the computed column a readable alias. Fourth, ORDER BY customer_id sorts the output for a stable result.

customer_id	first_name	last_name	phone_display
1	Alice	Johnson	555-0101
2	Bob	Smith	unknown
3	Carol	Williams	555-0103
4	David	Brown	unknown
5	Eve	Davis	555-0105

The result was checked with an equivalent SQLite query, executed in sqlite3 3.37.2. Rows 2 and 4 now read unknown instead of NULL. The other three rows are unchanged.

More Examples

Fall back through several columns. A common pattern is to prefer one contact field, then another, then a literal.

SELECT
  customer_id,
  COALESCE(mobile_phone, home_phone, work_phone, 'no contact') AS best_phone
FROM customers;

The first non-NULL column wins. If all three are NULL, the literal is returned.

Use COALESCE in arithmetic. NULL propagates through arithmetic, so price * quantity is NULL when either side is NULL. Wrapping the operand keeps the total meaningful.

SELECT
  order_id,
  price * COALESCE(quantity, 0) AS line_total
FROM order_lines;

Use COALESCE in ORDER BY. You can sort NULLs to a chosen position by substituting a sentinel value.

SELECT customer_id, phone
FROM customers
ORDER BY COALESCE(phone, 'zzz');

Rows with a phone number sort first, and the NULL rows land at the end.

Use COALESCE with a date. When a record has both a created date and an updated date, the most recent activity is often what you want.

SELECT
  record_id,
  COALESCE(updated_at, created_at) AS last_touch
FROM records;

Use COALESCE to build a display name. Combine parts that may be missing.

SELECT
  COALESCE(nickname, first_name, 'Customer') AS display_name
FROM customers;

If you are still getting comfortable with the SELECT clause itself, the SQL SELECT statement guide covers clause order and evaluation. Column aliases like phone_display are explained in the SQL alias article.

Errors and How to Fix Them

Type mismatch. The most common failure is mixing incompatible types, such as a number and a string. The engine cannot pick one result type. Cast the arguments so they agree.

SELECT COALESCE(CAST(account_number AS TEXT), account_name) FROM accounts;

Wrong argument count in ISNULL. If you are coming from SQL Server, ISNULL takes two arguments. Passing three raises an error. Use COALESCE when you need a longer chain.

Unexpected empty strings. COALESCE treats an empty string as a real value, not as missing data. If your source stores '' for unknown, COALESCE(phone, 'unknown') returns ''. Handle that case explicitly.

SELECT NULLIF(TRIM(phone), '') AS phone_clean FROM customers;

Truncation in the result type. When arguments have different lengths, the result type is derived from all of them. A short literal combined with a long column is usually fine, but a long literal combined with a short column can surprise you. Cast explicitly when the width matters.

Common Mistakes

Assuming COALESCE only checks one column. It checks every argument in order. If the first argument is rarely NULL, later arguments almost never appear, which can hide data quality problems.
Using COALESCE where ISNULL is meant. In MySQL, ISNULL(expr) returns 1 or 0 and is a test, not a replacement. Use COALESCE or IFNULL to substitute a value.
Forgetting that empty strings are not NULL. COALESCE will happily return ''. Combine it with NULLIF or TRIM when blank strings mean missing.
Mixing types without a cast. PostgreSQL raises an error, SQL Server may fail with a conversion error, and SQLite and MySQL return mixed types silently. Cast the arguments to a shared type before the query runs.
Relying on short-circuit evaluation for performance. Do not assume later arguments are never evaluated. Keep expensive expressions out of the argument list when you can.
Using COALESCE to fix a schema problem. If a column is NULL because the data model is wrong, a fallback value hides the issue instead of solving it.

Limitations

COALESCE cannot tell you why a value is NULL. It cannot distinguish a value that was never recorded from one that was deliberately cleared, and it cannot distinguish either from a value that failed to load. That distinction usually lives in the source system, not in the query. If you need it, capture it upstream with a status column or an audit table.

The function also cannot change the type of the column it reads. It returns a value of a compatible type, and the underlying column stays NULL. Any downstream process that reads the raw column still sees NULL. If you are aggregating, remember that COALESCE inside an aggregate changes the count of non-NULL values, which can shift averages and totals. Check whether you want the substitution applied before or after the aggregation. For row-level ordering and ranking tasks, functions such as SQL RANK and SQL LEAD interact with NULLs in their own ways, so test the combined behavior on your data.

Frequently Asked Questions

What is the difference between COALESCE and ISNULL in SQL?

In SQL Server, ISNULL(check_expression, replacement_value) takes exactly two arguments and returns the replacement when the first is NULL. COALESCE accepts two or more arguments and returns the first non-NULL one. In MySQL, ISNULL(expr) is different again: it is a boolean test that returns 1 when the expression is NULL and 0 otherwise. The related search "isnull function in sql" usually refers to one of these two behaviors, so check your engine's documentation.

Does COALESCE work in every database?

It is part of standard SQL and is implemented in SQLite, PostgreSQL, MySQL, SQL Server, Oracle and others. The core behavior is the same everywhere. Type resolution rules and error messages differ between engines, so a query that runs on one system may need an explicit cast on another.

What does COALESCE return when all arguments are NULL?

It returns NULL. There is no implicit default. If you need a guaranteed non-NULL result, make the last argument a literal such as 'unknown' or 0.

Can I use COALESCE in a WHERE clause?

Yes. It is an expression, so it can appear in WHERE, ORDER BY, GROUP BY and inside other functions. Be aware that wrapping a column in COALESCE can prevent the optimizer from using an index on that column, so test performance on large tables.

How many arguments can COALESCE take?

The standard does not fix a small limit, and most engines accept a long list. Practical limits vary by database and by the length of the generated expression. If you find yourself passing dozens of arguments, a lookup table or a CASE expression is usually clearer. For multi-step transformations, a common table expression can keep the query readable by splitting the logic into stages.