SQL COALESCE and NULL: Replace NULL With 0
· 7 min read
COALESCE returns the first value in its list that is not NULL. Give it a column and a fallback, and you get the column when it has a value and the fallback when it is empty. That is all SQL COALESCE does, and it is the standard way to replace NULL with 0, a label, or another column.
SELECT customer_name,
COALESCE(phone, email, 'no contact') AS best_contact
FROM customers
ORDER BY customer_id;Read it left to right: use the phone number; if there is none, use the email; if that is empty too, write "no contact". Below are the results on real rows, how to find NULLs with IS NULL, why one NULL can wipe out a total, and how COALESCE compares with ISNULL.
A worked example
| customer_id | customer_name | phone | |
|---|---|---|---|
| 1 | Acme Dental | 555-0101 | ops@acme.test |
| 2 | Birch Cafe | NULL | hello@birch.test |
| 3 | Cobalt Labs | NULL | NULL |
| 4 | Dune Studio | 555-0144 | NULL |
Run the query from the top on it:
SELECT customer_name,
COALESCE(phone, email, 'no contact') AS best_contact
FROM customers
ORDER BY customer_id;| customer_name | best_contact |
|---|---|
| Acme Dental | 555-0101 |
| Birch Cafe | hello@birch.test |
| Cobalt Labs | no contact |
| Dune Studio | 555-0144 |
Acme Dental has both, so the phone wins because it comes first. Birch Cafe has no phone, so COALESCE moves on to the email. Cobalt Labs has neither, so it falls all the way through to the text.
All values in the list must be the same kind of data. COALESCE(phone, 0) fails in DuckDB and Postgres because phone is text and 0 is a number. Use COALESCE(phone, '0') or cast one side.
SQL IS NULL: finding empty values
NULL means "unknown" or "missing". It is not zero and it is not an empty string. Because it is unknown, you cannot compare it with =. You test for it with IS NULL:
SELECT customer_name
FROM customers
WHERE phone IS NULL
ORDER BY customer_id;| customer_name |
|---|
| Birch Cafe |
| Cobalt Labs |
The opposite is IS NOT NULL, which returns Acme Dental and Dune Studio.
Writing WHERE phone = NULL is a classic beginner bug. It does not raise an error. It returns zero rows, every time, because NULL = NULL is not true: it is NULL as well, and WHERE only keeps rows where the condition is true.
SQL NULL in calculations
This is where NULL causes real damage. Any arithmetic with a NULL gives NULL.
| line_id | customer_id | product_name | quantity | unit_price | discount_pct | promo_code |
|---|---|---|---|---|---|---|
| 1 | 1 | Paper A4 | 10 | 5.00 | 0.1 | SPRING10 |
| 2 | 2 | Dish soap | 4 | 3.50 | NULL | NULL |
| 3 | 1 | Coffee beans | 2 | 18.00 | 0.0 | WELCOME5 |
| 4 | 4 | Mop head | 3 | 6.00 | NULL | NULL |
Lines 2 and 4 have no discount recorded. Calculate the line totals and watch what happens:
SELECT line_id, product_name,
quantity * unit_price * (1 - discount_pct) AS line_total
FROM order_items
ORDER BY line_id;| line_id | product_name | line_total |
|---|---|---|
| 1 | Paper A4 | 45 |
| 2 | Dish soap | NULL |
| 3 | Coffee beans | 36 |
| 4 | Mop head | NULL |
Wrap that in SUM and you get 81. SUM skips NULLs without a warning, so two real sales vanish from the revenue figure.
Replace NULL with 0
If a missing discount means "no discount", say so with COALESCE:
SELECT SUM(quantity * unit_price * (1 - COALESCE(discount_pct, 0))) AS revenue
FROM order_items;| revenue |
|---|
| 113 |
Now the dish soap (14) and the mop heads (18) are counted. Put the COALESCE on the column that can be empty, inside the calculation, not around the final result.
Zero totals after a LEFT JOIN
The other everyday use is after a left join, where customers with no rows get a NULL sum:
SELECT c.customer_name,
COALESCE(SUM(i.quantity * i.unit_price), 0) AS gross_sales
FROM customers AS c
LEFT JOIN order_items AS i ON i.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;| customer_name | gross_sales |
|---|---|
| Acme Dental | 86.00 |
| Birch Cafe | 14.00 |
| Cobalt Labs | 0.00 |
| Dune Studio | 18.00 |
Without COALESCE, Cobalt Labs shows NULL. Here the outer COALESCE is right, because the NULL comes from "no rows at all", not from a bad value. The join itself, including how to find customers with no matching rows, is covered in SQL LEFT JOIN explained.
Labels for reports
COALESCE also makes output readable: COALESCE(promo_code, '(none)') shows "(none)" instead of a blank cell, which matters when someone pastes your result into a slide.
COALESCE vs ISNULL
Every database has a two-argument shortcut, and they are not the same:
| Database | Shortcut | Notes |
|---|---|---|
| SQL Server | ISNULL(x, 0) | Exactly two arguments. The result takes the type of the first one, so a long fallback can get cut off. |
| MySQL | IFNULL(x, 0) | Two arguments. MySQL's own ISNULL(x) is different: it returns 1 or 0, like a test. |
| Oracle | NVL(x, 0) | Two arguments. |
| All of them | COALESCE(x, y, 0) | Standard SQL, any number of arguments. |
Use COALESCE unless you have a reason not to. It runs everywhere, takes more than two values, and nobody reading your query has to remember which dialect's ISNULL you meant.
Common mistakes
Filtering with <> and losing the NULLs (the AI mistake to watch for)
Ask an AI assistant for "order lines that did not use the SPRING10 promo" and you will often get this:
SELECT line_id, product_name, promo_code
FROM order_items
WHERE promo_code <> 'SPRING10'
ORDER BY line_id;| line_id | product_name | promo_code |
|---|---|---|
| 3 | Coffee beans | WELCOME5 |
Only one row. Lines 2 and 4 used no promo at all, so they clearly did not use SPRING10, but NULL <> 'SPRING10' is NULL, not true, and WHERE drops them. The assistant will usually describe the result confidently ("only one line was sold without SPRING10"), and the number looks plausible.
The fix is to say what should happen to NULLs:
SELECT line_id, product_name, promo_code
FROM order_items
WHERE promo_code <> 'SPRING10' OR promo_code IS NULL
ORDER BY line_id;| line_id | product_name | promo_code |
|---|---|---|
| 2 | Dish soap | NULL |
| 3 | Coffee beans | WELCOME5 |
| 4 | Mop head | NULL |
DuckDB and Postgres also accept WHERE promo_code IS DISTINCT FROM 'SPRING10', which treats NULL as a normal value and gives the same three rows.
How to spot it: whenever you see <>, != or NOT IN on a column, ask "can this column be empty?" If yes, count the rows with IS NULL and check that they ended up where they should.
Replacing NULL with 0 when it changes the meaning
COALESCE(x, 0) is not always safe. On the table above, AVG(discount_pct) is 0.05: the average of the discounts that were recorded. AVG(COALESCE(discount_pct, 0)) is 0.025, because it counts the two missing ones as zero. Which one is right depends on what the NULL means. If it means "no discount", use 0. If it means "we don't know", leave it out and say so.
Using = NULL
Covered above, but worth repeating because it fails silently. = NULL and <> NULL never match anything. Always use IS NULL or IS NOT NULL.
FAQ
How do I replace NULL with 0 in SQL?
Use COALESCE(column, 0). It returns the column's value when there is one and 0 when it is NULL. In SQL Server ISNULL(column, 0) does the same, and in MySQL IFNULL(column, 0).
What is the difference between COALESCE and ISNULL?
COALESCE is standard SQL and takes any number of arguments. ISNULL is SQL Server's two-argument version and uses the first argument's data type for the result. In MySQL, ISNULL is a test that returns 1 or 0, not a replacement.
Does COUNT include NULL values?
COUNT(*) counts every row. COUNT(column) skips rows where that column is NULL. On the customers table, COUNT(*) is 4 and COUNT(phone) is 2. There is more on this in SQL aggregate functions.
Why does my SUM return NULL?
Because there were no non-NULL values to add: either no rows matched, or every value was NULL. Wrap it as COALESCE(SUM(x), 0) if you want 0 instead.
Is an empty string the same as NULL?
No, in most databases '' is a real value and IS NULL will not find it. Oracle is the exception: it treats empty strings as NULL. To catch both, use WHERE COALESCE(email, '') = ''.
NULLs also explain several WHERE clause surprises, so read that next if your filters return fewer rows than you expected.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Customers with no completed orders” →