The COALESCE Function in SQL: Syntax and Examples

COALESCE is a function that takes a list of arguments and returns the first one that is not NULL. Its most common use is substituting a default value for NULL in query results.

MySQL 8.1
COALESCE(value_1, value_2, ..., value_N)

It works identically in every popular DBMS: MySQL, PostgreSQL, SQLite, SQL Server, Oracle. It is part of the SQL standard, which is why COALESCE is the right choice over the non-standard IFNULL, ISNULL and NVL — more on those below.

How COALESCE Works

The function checks its arguments from left to right and stops at the first one that is not NULL:

MySQL 8.1
SELECT COALESCE(NULL, NULL, 'SQL Academy', 'fallback option') AS result;

The first two arguments are NULL, so they are skipped. The third argument is not NULL — it becomes the result, and the string 'fallback option' is never even evaluated.

If all arguments are NULL, the result is NULL as well.

A Live Example: Replacing NULL in a Report

Let's look at the orders table from a delivery service database. The promo_code column stores the order's promo code, and when an order was placed without one, it contains NULL:

MySQL 8.1
SELECT order_id, total_amount, promo_code
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

In a report for a manager, empty cells look like an export error. Let's substitute readable text for NULL:

MySQL 8.1
SELECT order_id, total_amount, COALESCE(promo_code, 'no promo code') AS promo
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

Where a promo code exists, COALESCE returned it unchanged. Where there was NULL, it substituted the text from the second argument. The block above is live: change the replacement text or wrap the delivery_time_min column in COALESCE and see what changes.

A Chain of Fallbacks

The power of COALESCE is that it accepts any number of arguments. A classic scenario is picking the first available contact for a client: the work phone, or the mobile phone if there is none, or a placeholder if both are missing:

MySQL 8.1
SELECT name, COALESCE(work_phone, mobile_phone, 'no contact info') AS contact
FROM clients;

Each row goes through the left-to-right check independently of the others: Anna got her work phone, Boris his mobile, and Vera the placeholder.

COALESCE with Aggregate Functions

The aggregate functions SUM, AVG, MIN, MAX return NULL when a group contains no values at all. Let's calculate the average delivery time per order status:

MySQL 8.1
SELECT status, ROUND(AVG(delivery_time_min)) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Canceled orders have no delivery time — they were never delivered, so AVG returned NULL. If this result feeds into further calculations or a chart, NULL will break them. Let's wrap the aggregate in COALESCE:

MySQL 8.1
SELECT status, COALESCE(ROUND(AVG(delivery_time_min)), 0) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Be mindful of what the substitution means: zero here stands for "no data", not "delivered instantly". In reports where that difference matters, it is better to keep NULL or split such groups out.

NULLIF: The Opposite Function

NULLIF does the reverse: it turns a value into NULL. It takes exactly two arguments and returns NULL if they are equal, otherwise the first argument:

MySQL 8.1
NULLIF(value_1, value_2)

MySQL 8.1
SELECT NULLIF('delivered', 'canceled') AS a, NULLIF('canceled', 'canceled') AS b;

Protecting Against Division by Zero

The most common use of NULLIF is in the denominator of a fraction. In PostgreSQL, dividing by zero crashes the query:

MySQL 8.1
SELECT 100 / 0 AS result;

MySQL in the same situation silently returns NULL with a warning. NULLIF makes the behavior consistent and predictable in both DBMS:

MySQL 8.1
SELECT 100 / NULLIF(0, 0) AS result;

The denominator became NULL, dividing by NULL produced NULL — the query did not fail. You can wrap the whole thing in COALESCE to return, say, zero.

Cleaning Empty Strings with NULLIF and COALESCE

Real-world data contains not only NULL but also empty strings and strings of spaces. COALESCE won't replace those — an empty string is not NULL. Our orders table has exactly this problem:

MySQL 8.1
SELECT order_id, promo_code, COALESCE(promo_code, 'no promo code') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Order 1 got fixed, but orders 2 and 3 with spaces and an empty string remain "holes" in the report. The solution is a combo of three functions: TRIM removes the spaces, NULLIF turns the empty string into NULL, and COALESCE substitutes the placeholder:

MySQL 8.1
SELECT order_id, COALESCE(NULLIF(TRIM(promo_code), ''), 'no promo code') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

This combo is the workhorse of data cleaning — worth memorizing.

What does the expression COALESCE(NULL, '', 'SQL Academy') return?

COALESCE vs IFNULL vs ISNULL vs NVL

COALESCE has non-standard relatives tied to specific DBMS:

The main trap here is ISNULL. In SQL Server it is a two-argument NULL replacement, while in MySQL a function with the same name takes one argument and simply checks it for NULL, returning 1 or 0:

MySQL 8.1
SELECT ISNULL(NULL) AS is_null, ISNULL('text') AS is_not_null, IFNULL(NULL, 'replaced') AS replaced;

Code with ISNULL(a, b) ported from SQL Server to MySQL will fail with an error about the wrong number of arguments.

PostgreSQL has neither IFNULL nor ISNULL — a query using them ends with an error:

MySQL 8.1
SELECT IFNULL(promo_code, 'no promo code') FROM orders;

The only way to replace NULL here is COALESCE — one more reason to write it from the start.

The takeaway is simple: if your code might ever run on a different DBMS, write COALESCE and it will work everywhere.

Which NULL-replacement function works in MySQL, PostgreSQL and SQL Server alike?

Where COALESCE Behaves Unexpectedly

Incompatible argument types. PostgreSQL requires all arguments to be convertible to one type. Trying to substitute a string for a numeric NULL ends with an error:

MySQL 8.1
SELECT COALESCE(delivery_time_min, 'no data') FROM orders;

MySQL in the same situation silently casts the number to a string — the query runs, but the column changes its type, which may surprise the application reading it.

COALESCE is not a substitute for IS NULL in conditions. The comparison WHERE promo_code = NULL returns no rows, and COALESCE can't help: filtering by NULL is what the IS NULL operator is for. See the lesson IS NULL, BETWEEN, IN operators.

An empty string is not NULL. COALESCE('', 'replacement') returns the empty string, not the replacement. If "empty" means "no value" in your data, use the NULLIF and COALESCE combo.

What's Next

The best way to make COALESCE stick is to use it hands-on:

• solve SQL exercises in the trainer — NULL-handling functions come up there regularly;
• learn how NULL behaves in comparisons and filters in the lesson IS NULL, BETWEEN, IN;
• quick function references live in the handbook: COALESCE, IFNULL, NULLIF.