COALESCE is a SQL function that takes a list of values and returns the first one that is not NULL. If every value is NULL, it returns NULL. You use it to show a default instead of a blank, to avoid NULL breaking a calculation, and to pick the best available value from several columns.
Jump to: syntax · sample data · 6 examples · COALESCE vs ISNULL, IFNULL and NVL · common mistakes · FAQ
COALESCE syntax
COALESCE(value1, value2, value3, ...)SQL checks the arguments from left to right and returns the first one that is not NULL. It stops as soon as it finds one, so later arguments are not evaluated. You can pass two arguments or twenty. Arguments can be columns, literals, expressions or even subqueries.
SELECT COALESCE(NULL, NULL, 'third'); -- third
SELECT COALESCE('first', 'second'); -- first
SELECT COALESCE(NULL, NULL); -- NULLCOALESCE is part of the SQL standard, so the syntax above works in PostgreSQL, MySQL, SQL Server, SQLite, Oracle, Snowflake and BigQuery. If you are new to NULL itself, read how to use NULL values in SQL first. The short version: NULL means "unknown or missing", it is not zero and it is not an empty string.
Set up the sample data
Every query below ran on SQLite 3.45 and the output is real. Paste this into any SQL client, such as Beekeeper Studio, to follow along.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, phone TEXT, mobile TEXT, email TEXT, discount REAL);
INSERT INTO customers VALUES
(1, 'Ana', '555-0101', NULL, '[email protected]', 0.10),
(2, 'Ben', NULL, '555-0202', '[email protected]', NULL),
(3, 'Chloe', NULL, NULL, NULL, 0.05),
(4, 'Dev', NULL, NULL, '[email protected]', NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 50), (3, 2, 80);
CREATE TABLE products (id INTEGER, name TEXT, price REAL, sale_price REAL);
INSERT INTO products VALUES (1, 'Mug', 10, 8), (2, 'Pen', 2, NULL), (3, 'Book', 20, NULL);6 COALESCE examples
1. Replace NULL with a default value
The most common use. Show "no phone" when a customer has no phone number.
SELECT name, COALESCE(phone, 'no phone') AS phone
FROM customers;name | phone
Ana | 555-0101
Ben | no phone
Chloe | no phone
Dev | no phoneThis changes only the result set. The table itself is untouched. To change stored data you would use UPDATE.
2. Pick the first available value from several columns
Ben has no phone but does have a mobile number. A list of arguments gives you a fallback chain: phone, then mobile, then a literal.
SELECT name, COALESCE(phone, mobile, 'no phone') AS contact
FROM customers;name | contact
Ana | 555-0101
Ben | 555-0202
Chloe | no phone
Dev | no phoneOrder matters. Put the value you prefer first and the last-resort default last.
3. Stop NULL from breaking a calculation
Any arithmetic with NULL returns NULL. A customer with no discount should pay full price, not "unknown".
-- Without COALESCE
SELECT name, discount, 100 * (1 - discount) AS price FROM customers;
name | discount | price
Ana | 0.1 | 90.0
Ben | NULL | NULL
Chloe | 0.05 | 95.0
Dev | NULL | NULL
-- With COALESCE
SELECT name, 100 * (1 - COALESCE(discount, 0)) AS price FROM customers;
name | price
Ana | 90.0
Ben | 100
Chloe | 95.0
Dev | 1004. Show 0 instead of NULL after a LEFT JOIN
A LEFT JOIN keeps customers who have no orders, and SUM of no rows is NULL, not 0. Wrap the aggregate in COALESCE to get a clean zero.
SELECT c.name,
SUM(o.amount) AS total,
COALESCE(SUM(o.amount), 0) AS total_safe
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;name | total | total_safe
Ana | 150 | 150
Ben | 80 | 80
Chloe | NULL | 0
Dev | NULL | 0This is the same trick used to include zero in a count. Note that COUNT already returns 0 for no rows, so COALESCE is only needed around SUM, AVG, MAX and similar.
5. Use a sale price when there is one, otherwise the regular price
SELECT name, COALESCE(sale_price, price) AS price_now
FROM products;name | price_now
Mug | 8.0
Pen | 2.0
Book | 20.06. Use COALESCE in WHERE and ORDER BY
COALESCE works anywhere an expression does. Find customers with no contact number at all:
SELECT name
FROM customers
WHERE COALESCE(phone, mobile) IS NULL;name
Chloe
DevOr sort so that people with a number come first, then alphabetically:
SELECT name, phone, mobile
FROM customers
ORDER BY COALESCE(phone, mobile) IS NULL, name;name | phone | mobile
Ana | 555-0101 | NULL
Ben | NULL | 555-0202
Chloe | NULL | NULL
Dev | NULL | NULLThe IS NULL test returns 0 or 1 in SQLite and MySQL. In PostgreSQL it returns a boolean, and false sorts before true, so the same query works there too. SQL Server does not allow a bare boolean expression, so use a CASE instead (see how to use CASE in SQL).
COALESCE is shorthand for CASE
These two queries are equivalent, and the first is much shorter:
SELECT name, COALESCE(phone, mobile, 'no phone') AS contact FROM customers;
SELECT name,
CASE WHEN phone IS NOT NULL THEN phone
WHEN mobile IS NOT NULL THEN mobile
ELSE 'no phone'
END AS contact
FROM customers;Both return the same four rows. Use CASE when your conditions are anything other than "is it NULL?".
COALESCE vs ISNULL, IFNULL and NVL
Most databases also have a two-argument function that does a smaller version of the same job. COALESCE is the one to learn because it is standard and takes any number of arguments.
| Function | Where it works | Arguments |
|---|---|---|
COALESCE(a, b, c, ...) | All major databases (SQL standard) | 2 or more |
ISNULL(a, b) | SQL Server only | Exactly 2 |
IFNULL(a, b) | MySQL, SQLite, MariaDB | Exactly 2 |
NVL(a, b) | Oracle | Exactly 2 |
One quirk worth knowing in SQL Server: ISNULL takes its result data type from the first argument, so ISNULL(short_col, 'a long default') can silently truncate the default. COALESCE uses the type with the highest precedence among its arguments, which avoids that surprise. (This behaviour is documented in Microsoft's T-SQL reference.)
In my SQLite test, IFNULL(phone, 'n/a') and COALESCE(phone, 'n/a') returned identical results. PostgreSQL has no ISNULL() or IFNULL() function, so COALESCE is the way to do it there.
Common COALESCE mistakes
- Mixing incompatible data types.
COALESCE(discount, 'none')mixes a number and text. PostgreSQL and SQL Server raise an error, while SQLite quietly allows it. Cast one side so the types match, for exampleCOALESCE(CAST(discount AS TEXT), 'none'). - Treating empty strings as NULL.
COALESCEonly replacesNULL. An empty string''counts as a real value and is returned as is. To treat blanks as missing, convert them first withNULLIF:COALESCE(NULLIF(phone, ''), 'no phone').NULLIF(a, b)returnsNULLwhen the two values are equal. - Letting COALESCE hide a real data problem. Defaulting a missing price to 0 changes your averages. In my test data,
AVG(discount)was 0.075 (it ignores NULLs) whileAVG(COALESCE(discount, 0))was 0.0375. Neither is wrong, but they answer different questions. Decide which one you mean. - Expecting it to fill a column permanently. It only changes query output.
- Wrapping an indexed column in COALESCE inside WHERE.
WHERE COALESCE(phone, '') = '555-0101'can stop the database using an index onphone. Where possible, filter the raw column instead.
FAQ
What does COALESCE do in SQL?
It returns the first non-NULL value from the list of arguments you give it, checking left to right. If all arguments are NULL, it returns NULL.
Is COALESCE the same as ISNULL?
No. Both handle NULL, but ISNULL exists only in SQL Server and takes exactly two arguments, while COALESCE is standard SQL and accepts any number.
Does COALESCE work in MySQL, PostgreSQL and SQL Server?
Yes. The syntax is identical in all of them, plus SQLite, Oracle, Snowflake and BigQuery.
Can COALESCE return NULL?
Yes, if every argument is NULL. Add a non-NULL literal as the last argument, such as 0 or 'unknown', to guarantee a value.
Does COALESCE replace empty strings?
No, only NULL. Combine it with NULLIF(col, '') to treat empty strings as missing.
Can I use COALESCE in a WHERE clause or with aggregates?
Yes. It is an ordinary expression, so it works in SELECT, WHERE, ORDER BY, GROUP BY and around aggregates like SUM.
Practice COALESCE
Reading is not enough. Try the free interactive COALESCE lesson, which runs queries against a sample database in your browser, then work through the SQL practice problems. Keep the SQL cheat sheet handy for syntax, and see SQL window functions for the next step up.