SQL COALESCE: Replace NULL Values

Think of how you reach a customer: try their mobile first, then the home number, then the office, and if none of them is on file, note "no number". COALESCE does exactly that, from left to right, for every row. The four sample contacts below are written into the query, so you can run it and edit them:

WITH contacts(name, mobile, home, office) AS (
  VALUES ('Neha',   '555-0101', NULL,       '555-0199'),
         ('Vikram', NULL,       '555-0142', NULL),
         ('Aditi',  NULL,       NULL,       '555-0177'),
         ('Rohan',  NULL,       NULL,       NULL)
)
SELECT name, mobile, home, office,
       COALESCE(mobile, home, office, 'No number on file') AS best_contact
FROM contacts;

▶ Run this query in the SQL Playground

Neha has a mobile number, so it is used and the other columns are never looked at. Vikram has only a home number and Aditi only an office number. Rohan has none, so he gets the final default. Without the last argument his result would be NULL.

NULL means "no value recorded". Zero and an empty string are real values. The difference shows as soon as NULL meets an operator: arithmetic with NULL gives NULL, and so does a comparison, even NULL = NULL:

SELECT 5 + NULL          AS plus_null,
       NULL = NULL       AS null_equals_null,
       5 + 0             AS plus_zero,
       'abc' || ''       AS plus_empty_string,
       COALESCE(NULL, 0) AS coalesced;

▶ Run this query in the SQL Playground

In the Playground, 97 orders have no shipped_date because they have not been shipped yet, and 10 employees have no manager_id. Those are the NULLs the examples below work with:

SELECT COUNT(*) AS all_orders,
       COUNT(*) - COUNT(shipped_date) AS not_shipped
FROM orders;

▶ Run this query in the SQL Playground

SELECT COUNT(*) - COUNT(manager_id) AS no_manager
FROM employees;

▶ Run this query in the SQL Playground

To find NULLs you use IS NULL. To replace them with something else, you use COALESCE.

COALESCE(value_1, value_2, ..., value_n)

SELECT COALESCE(NULL, NULL, 'SQLab Hub', 'never reached') AS first_value,
       COALESCE(NULL, NULL) AS all_null;

▶ Run this query in the SQL Playground

The first column skips two NULLs and returns SQLab Hub; the last argument is never reached. The second column has nothing but NULLs, so the result is NULL. COALESCE is part of standard SQL and works the same in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.

The most common use is a readable label for a missing value. Orders that have not shipped get "Not shipped yet" instead of a blank cell:

SELECT id, status,
       COALESCE(shipped_date, 'Not shipped yet') AS shipped
FROM orders
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

Here the fallback is text while shipped_date is a date. SQLite stores dates as text, so this works. PostgreSQL and SQL Server require one type for all arguments, so there you convert the date first: COALESCE(CAST(shipped_date AS VARCHAR(10)), 'Not shipped yet').

A fallback from another table

With a self join, every employee can be shown next to their manager. Employees without one get a label instead of NULL:

SELECT e.first_name || ' ' || e.last_name AS employee,
       e.title,
       COALESCE(m.first_name || ' ' || m.last_name, 'No manager') AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id
LIMIT 6;

▶ Run this query in the SQL Playground

For the top-level employees, m.first_name || ' ' || m.last_name is NULL because the joined manager row does not exist, so COALESCE moves on to the label.

Any arithmetic with NULL returns NULL. If a bonus was never recorded, salary + bonus wipes out the whole total. COALESCE replaces the missing bonus with 0 before the addition:

WITH pay(name, salary, bonus) AS (
  VALUES ('Aditi', 112000, 15000),
         ('Rohan',  58000, NULL),
         ('Priya',  97000, 8000),
         ('Karan',  46000, NULL),
         ('Sneha',  63000, 0)
)
SELECT name, salary, bonus,
       salary + bonus              AS total_without_coalesce,
       salary + COALESCE(bonus, 0) AS total_pay
FROM pay;

▶ Run this query in the SQL Playground

Rohan and Karan have no bonus recorded, so their first total is NULL. Sneha's bonus is a real 0, and her total works either way. COALESCE makes the missing bonus behave like hers.

Aggregate functions already skip NULLs, so COALESCE inside them changes the answer, not only the display. Compare the bonus figures for the same five people:

WITH pay(name, salary, bonus) AS (
  VALUES ('Aditi', 112000, 15000),
         ('Rohan',  58000, NULL),
         ('Priya',  97000, 8000),
         ('Karan',  46000, NULL),
         ('Sneha',  63000, 0)
)
SELECT SUM(bonus)              AS sum_bonus,
       SUM(COALESCE(bonus, 0)) AS sum_coalesced,
       ROUND(AVG(bonus), 2)    AS avg_bonus,
       AVG(COALESCE(bonus, 0)) AS avg_coalesced,
       COUNT(bonus)            AS count_bonus,
       COUNT(COALESCE(bonus, 0)) AS count_coalesced
FROM pay;

▶ Run this query in the SQL Playground

Neither average is "correct" on its own. AVG(bonus) answers "what is the typical bonus among people who got one?"; AVG(COALESCE(bonus, 0)) answers "what is the average bonus per employee?". Decide which question you are asking before you add COALESCE.

SUM over no rows

SUM of zero rows is NULL, not 0. That matters for dashboard tiles. There are no orders in 2025 in the Playground, so the plain SUM is NULL and the wrapped one shows 0:

SELECT SUM(total)              AS plain_sum,
       COALESCE(SUM(total), 0) AS revenue_2025
FROM orders
WHERE order_date >= '2025-01-01';

▶ Run this query in the SQL Playground

A LEFT JOIN keeps every row from the left table and fills the columns of the right table with NULL when there is no match. This lists customers with their order count and total spend, including customers who never ordered:

SELECT u.id, u.first_name, u.last_name,
       COUNT(o.id) AS orders,
       SUM(o.total) AS raw_spend,
       COALESCE(ROUND(SUM(o.total), 2), 0) AS total_spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

For a customer with no orders, SUM(o.total) is NULL and COALESCE turns it into 0. COUNT(o.id) is already 0, because COUNT never returns NULL. 36 of the 150 customers have never ordered:

SELECT COUNT(*) AS customers_without_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

▶ Run this query in the SQL Playground

When 0 would be misleading

Products without reviews have no average rating. Showing 0 would look like a terrible rating, so a label is the honest default:

SELECT p.name,
       COUNT(r.id) AS reviews,
       COALESCE(CAST(ROUND(AVG(r.rating), 1) AS TEXT), 'No reviews yet') AS avg_rating
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
ORDER BY reviews, p.id
LIMIT 6;

▶ Run this query in the SQL Playground

A NULL inside a CASE condition makes that condition unknown, so the row falls through to ELSE without anyone noticing. Wrapping the column in COALESCE makes the intent explicit:

WITH pay(name, salary, bonus) AS (
  VALUES ('Aditi', 112000, 15000),
         ('Rohan',  58000, NULL),
         ('Priya',  97000, 8000),
         ('Karan',  46000, NULL),
         ('Sneha',  63000, 0)
)
SELECT name, bonus,
       CASE WHEN COALESCE(bonus, 0) >= 10000 THEN 'High bonus'
            WHEN COALESCE(bonus, 0) > 0      THEN 'Bonus'
            ELSE 'No bonus'
       END AS bonus_group
FROM pay;

▶ Run this query in the SQL Playground

COALESCE is shorthand for one particular CASE: COALESCE(a, b) means CASE WHEN a IS NOT NULL THEN a ELSE b END.

A subquery as an argument

Any argument can be a scalar subquery. This shows each customer's latest order date, or a label if there is none:

SELECT u.first_name, u.last_name,
       COALESCE((SELECT MAX(o.order_date)
                 FROM orders o
                 WHERE o.user_id = u.id), 'Never ordered') AS last_order
FROM users u
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

NULLIF is close to the opposite of COALESCE: NULLIF(a, b) returns NULL when a equals b. Together they make a safe division. NULLIF turns a zero denominator into NULL, so the division gives NULL instead of an error, and COALESCE turns that NULL into a default:

SELECT u.id, u.first_name,
       COUNT(o.id) AS orders,
       COALESCE(ROUND(SUM(o.total) / NULLIF(COUNT(o.id), 0), 2), 0) AS avg_order_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

SQLite returns NULL for a division by zero anyway, but PostgreSQL and SQL Server raise an error, so the NULLIF is what makes this query portable.

Most databases have a two-argument shortcut for the same job. They behave like COALESCE(a, b), with small differences:

In the Playground, IFNULL and COALESCE give the same answer:

SELECT id,
       IFNULL(shipped_date, 'Not shipped')   AS with_ifnull,
       COALESCE(shipped_date, 'Not shipped') AS with_coalesce
FROM orders
ORDER BY id
LIMIT 4;

▶ Run this query in the SQL Playground

Prefer COALESCE unless a team standard says otherwise: it takes any number of arguments and runs unchanged if the query moves to another database.

COALESCE in SELECT costs almost nothing. In WHERE it can hurt: like any function wrapped around a column, it usually stops the database from using an index on that column.

Check with EXPLAIN before rewriting, and only bother on large tables.

Six errors that give wrong results, most of them without an error message.

SELECT COALESCE('Not shipped', shipped_date)
FROM orders;

SELECT COALESCE(shipped_date, 'Not shipped')
FROM orders;

SELECT *
FROM pay
WHERE bonus = 0;

SELECT *
FROM pay
WHERE bonus = 0 OR bonus IS NULL;

SELECT name, salary + bonus AS total_pay
FROM pay;

SELECT name, salary + COALESCE(bonus, 0) AS total_pay
FROM pay;

SELECT u.id, SUM(o.total) AS spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

SELECT u.id, COALESCE(SUM(o.total), 0) AS spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

SELECT id, COALESCE(shipped_date, CURRENT_DATE) AS shipped
FROM orders;

SELECT id, COALESCE(shipped_date, 'Not shipped') AS shipped
FROM orders;

SELECT COALESCE(shipped_date, 'Not shipped')
FROM orders;  -- shipped_date is a DATE column

SELECT COALESCE(CAST(shipped_date AS VARCHAR(10)), 'Not shipped')
FROM orders;

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the 8 most recent orders with their shipped date, showing "Awaiting shipment" when there is none. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, status,
          COALESCE(shipped_date, 'Awaiting shipment') AS shipped
   FROM orders
   ORDER BY order_date DESC, id DESC
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   COALESCE returns shipped_date when it exists and the label otherwise.

2. 2Easy Show every employee's manager_id, with 0 for employees who have no manager. List the first 10 by id. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT id, first_name, COALESCE(manager_id, 0) AS manager_id
   FROM employees
   ORDER BY id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   A numeric default keeps the column numeric. 0 is safe here because no employee has id 0.

3. 3Medium For each product category, show the units sold, with 0 for any product that was never ordered counted in. Sort by units sold. Table: products, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          COALESCE(SUM(oi.quantity), 0) AS units_sold
   FROM products p
   LEFT JOIN order_items oi ON oi.product_id = p.id
   GROUP BY p.category
   ORDER BY units_sold DESC;

   ▶ Run this query in the SQL Playground

   The LEFT JOIN keeps unsold products; COALESCE matters for any group whose SUM has nothing to add.

4. 4Medium Show the 8 customers with the lowest total successful payments, including customers with no successful payment (shown as 0). Table: users, payments

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name, u.last_name,
          COALESCE(ROUND(SUM(p.amount), 2), 0) AS paid
   FROM users u
   LEFT JOIN payments p ON p.user_id = u.id AND p.status = 'success'
   GROUP BY u.id, u.first_name, u.last_name
   ORDER BY paid, u.id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   The status filter sits in ON, not WHERE, so customers without a successful payment stay in the result.

5. 5Medium Show each employee with their manager's first name, or "Top level" if they have none. First 8 by id. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name AS employee,
          COALESCE(m.first_name, 'Top level') AS manager
   FROM employees e
   LEFT JOIN employees m ON m.id = e.manager_id
   ORDER BY e.id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   For employees without a manager the joined row is missing, so m.first_name is NULL.

6. 6Hard For each category, show the number of products and the share (in percent) of products that have at least one review. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          COUNT(*) AS products,
          ROUND(100.0 * SUM(CASE WHEN COALESCE(r.reviews, 0) > 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_reviewed
   FROM products p
   LEFT JOIN (SELECT product_id, COUNT(*) AS reviews
              FROM reviews
              GROUP BY product_id) r ON r.product_id = p.id
   GROUP BY p.category
   ORDER BY pct_reviewed DESC, p.category;

   ▶ Run this query in the SQL Playground

   The subquery counts reviews per product; for products without reviews the LEFT JOIN gives NULL, which COALESCE turns into 0 before the comparison.