COALESCE()

Syntax

How it works

COALESCE() takes any number of arguments and returns the first one that is not NULL, scanning left to right. If every argument is NULL, the result is NULL. Because it is defined in the ANSI SQL standard, the same code runs unchanged on MySQL, PostgreSQL, SQL Server and SQLite, which is why it is the preferred way to handle NULL values.

The classic use is to provide a default: COALESCE(discount, 0) reads "use discount, but if it is NULL use 0 instead". Unlike the two argument helpers built into each engine, COALESCE accepts as many fallbacks as you like, so COALESCE(mobile, home, work, 'no phone') walks through several columns and returns the first one that has a value.

Every engine ships a shorter two argument shortcut, but they are not portable. MySQL has IFNULL(a, b), SQL Server has ISNULL(a, b), and Oracle has NVL(a, b). All three do the same job as a two argument COALESCE, yet each only works on its own database and each has its own quirks. COALESCE is really shorthand for a CASE expression, so anything COALESCE does you can also write with CASE when you need extra conditions.

Examples

Default value for a NULL column

-- Show 0 instead of NULL for customers with no discount
SELECT name,
       COALESCE(discount, 0) AS discount
FROM customers;

name  | discount
------+---------
 Sara |       15
 Omar |        0
 Lina |        5

First non-NULL of several columns

-- Pick the best contact number available
SELECT name,
       COALESCE(mobile, home, work, 'no phone') AS contact
FROM customers;

name  | contact
------+-------------
 Sara | 0501234567
 Omar | 042223333
 Lina | no phone

COALESCE in ORDER BY

-- Sort by nickname, falling back to full name
SELECT id, nickname, full_name
FROM users
ORDER BY COALESCE(nickname, full_name);

id | nickname | full_name
---+----------+----------
 3 | Abbas    | Muhammad
 1 | NULL     | Bilal
 2 | Zed      | Younis

Zero instead of NULL for an empty aggregate

-- SUM over no rows returns NULL; force it to 0
SELECT COALESCE(SUM(amount), 0) AS total_paid
FROM payments
WHERE customer_id = 999;

total_paid
----------
         0

Combine COALESCE with NULLIF

-- Treat empty string as missing, then default it
SELECT COALESCE(NULLIF(city, ''), 'Unknown') AS city
FROM addresses;

city
-------
 Dubai
 Unknown
 Cairo

Common mistakes

-- Mixing a number and text can raise a
-- type conversion error on strict engines
SELECT COALESCE(price, 'N/A') AS price
FROM products;

-- Keep the fallback the same type, or convert first
SELECT COALESCE(CAST(price AS CHAR), 'N/A') AS price
FROM products;

-- Assuming ISNULL behaves exactly like COALESCE
-- in SQL Server. It does not.
SELECT ISNULL(short_code, 'unavailable') AS code
FROM items;  -- may truncate to short_code length

-- COALESCE picks the wider type, so nothing is cut
SELECT COALESCE(short_code, 'unavailable') AS code
FROM items;

Performance

COALESCE() short circuits: it evaluates arguments left to right and stops as soon as one is not NULL. If your first column is almost always populated, the later fallback expressions are rarely computed, so ordering the cheapest and most likely arguments first is a small but real win.

The catch is subqueries and function calls. If an argument is a scalar subquery, the optimiser may still run it even when an earlier argument is non-NULL, because COALESCE expands to a CASE expression and each branch condition has to be checked. Avoid putting an expensive subquery as a fallback unless you have confirmed it is not evaluated in your query plan.

Wrapping an indexed column in COALESCE(col, ...) inside a WHERE clause usually prevents the engine from using an index on that column, because the column is now inside a function. If you need to match NULLs and a value, an OR col IS NULL predicate often keeps the index usable. See the functions reference for related NULL handling helpers.

Interview questions

SELECT COALESCE(discount, 0) AS discount
FROM customers;

SELECT COALESCE(SUM(amount), 0) AS total
FROM payments
WHERE customer_id = 999;

SELECT COALESCE(NULLIF(city, ''), 'Unknown') AS city
FROM addresses;