COALESCE returns the first value in its list that isn’t NULL. It’s how SQL swaps a missing value for a fallback. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.
How to use COALESCE
Give COALESCE any number of values. It checks them from left to right and returns the first one that isn’t NULL, or NULL if they all are:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
In a real query the values are usually columns, with a literal at the end as the last resort:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Rules to know
- It stops at the first non-NULL value, so later arguments aren’t evaluated once one is found. Put the value that’s most often filled in first.
- Every argument needs a compatible type. Mixing a number with text fails in PostgreSQL and SQL Server, so cast first:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
An empty string isn’t NULL. COALESCE passes
''straight through. Wrap the column inNULLIF(column, '')to treat empty strings as missing too:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Alternative syntax
You’ll also see two-argument shortcuts: IFNULL in MySQL and SQLite, and ISNULL in SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Prefer COALESCE. It takes any number of arguments, and SQL Server’s ISNULL gives its result the type of the first argument, which can cut your fallback short:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
A CASE expression can do the same job, at greater length: COALESCE(nickname, name) is shorthand for CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. See the SQL CASE statement guide for when you need the extra power.
When should you use COALESCE?
Reach for COALESCE whenever a NULL would make your result wrong, ugly or empty. Most uses fall into one of these four.
Fall back to another column
When the value you want lives in one of several columns, COALESCE picks the first one that’s filled in: a nickname or a full name, a mobile or a landline, a shipping or a billing address.
Use a default when every value is missing
Put a literal at the end of the list and the result is never NULL. That’s what you want for reports and screens, where “Address Unknown” or 0.00 reads better than a blank.
Fill in gaps with a computed value
The fallback doesn’t have to be a column or a constant. It can be an expression, such as a username or email address built from other columns, which makes COALESCE handy for cleaning up imported data.
Keep NULLs out of calculations
Any arithmetic with a NULL gives NULL, so a missing discount or bonus wipes out the whole total. COALESCE(discount, 0) turns the missing value into a number the calculation can use.
Real examples of COALESCE
Show a price, whatever’s on file
Products might have a sale price, a regular price, or (before launch) neither. Check them in order and default to zero:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Pick the best phone number to call
Contacts have up to three numbers. Show the first one available, and say so when there isn’t one:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Generate a placeholder email address
Employees without an email on file get one built from their name, so every row in the directory has a value:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
In MySQL and SQL Server, join the strings with CONCAT() instead of ||.
Total an order when discounts are optional
Without COALESCE, one line with no discount makes the whole order total come out wrong, because SUM skips the NULL lines entirely:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Further reading about COALESCE
On this blog:
-
MySQL COALESCE: COALESCE alongside MySQL’s
IFNULL - SQL CASE statement guide: conditional logic beyond NULL checks
- SQL not equal guide: why comparisons with NULL never match
- SQL GROUP BY guide: the aggregates COALESCE often feeds into
- SQL SUBSTRING guide: extracting text from columns that may be NULL
Official documentation:
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."