Replace null with 0 in MySQL

MySQL

SQL

data management

database

null values

Replace null with 0 in MySQL

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

In MySQL, NULL represents a missing or unknown value. When NULL appears in arithmetic expressions, the result is always NULL (5 + NULL = NULL). To substitute NULL with 0 (or any default), use IFNULL(), COALESCE(), or a CASE expression. IFNULL(column, 0) is the simplest MySQL-specific option, while COALESCE(column, 0) is the SQL-standard portable version. These functions work in SELECT, WHERE, ORDER BY, and aggregate contexts.

Using IFNULL()

sql

1-- Basic syntax: IFNULL(expression, replacement)
2SELECT
3    product_name,
4    IFNULL(price, 0) AS price,
5    IFNULL(discount, 0) AS discount,
6    IFNULL(price, 0) - IFNULL(discount, 0) AS final_price
7FROM products;
8
9-- Example data and results:
10-- | product_name | price | discount | final_price |
11-- |--------------|-------|----------|-------------|
12-- | Widget       | 10.00 | 2.00     | 8.00        |
13-- | Gadget       | NULL  | 1.00     | -1.00       |  -- price was NULL -> 0
14-- | Doohickey    | 5.00  | NULL     | 5.00        |  -- discount was NULL -> 0

IFNULL() takes exactly two arguments. If the first argument is NULL, it returns the second. Otherwise, it returns the first unchanged. This is MySQL-specific.

Using COALESCE()

sql

1-- COALESCE returns the first non-NULL value
2SELECT
3    employee_name,
4    COALESCE(bonus, commission, 0) AS compensation
5FROM employees;
6
7-- Checks bonus first, then commission, then falls back to 0
8-- If bonus = NULL and commission = 500, returns 500
9-- If both are NULL, returns 0
10
11-- Single column replacement (same as IFNULL)
12SELECT
13    product_name,
14    COALESCE(stock_count, 0) AS stock_count
15FROM products;

COALESCE() is the SQL-standard function. It accepts any number of arguments and returns the first non-NULL value. For a single column with a default, it behaves identically to IFNULL().

Using CASE Expression

sql

1-- CASE for more complex logic
2SELECT
3    order_id,
4    CASE
5        WHEN shipping_fee IS NULL THEN 0
6        WHEN shipping_fee < 0 THEN 0
7        ELSE shipping_fee
8    END AS shipping_fee
9FROM orders;

CASE is verbose but allows combining NULL checks with other conditions in a single expression.

In Aggregate Functions

sql

1-- SUM, AVG, COUNT already handle NULL differently
2
3-- SUM ignores NULL values (does not treat them as 0)
4SELECT SUM(amount) FROM payments;  -- NULLs are skipped
5
6-- To treat NULL as 0 in SUM:
7SELECT SUM(IFNULL(amount, 0)) FROM payments;
8
9-- COUNT(*) counts all rows; COUNT(column) skips NULLs
10SELECT
11    COUNT(*) AS total_rows,
12    COUNT(email) AS rows_with_email
13FROM users;
14
15-- AVG ignores NULLs (affects the divisor)
16SELECT AVG(score) FROM reviews;       -- NULL rows excluded from average
17SELECT AVG(IFNULL(score, 0)) FROM reviews;  -- NULL rows counted as 0

The choice between letting aggregates skip NULLs versus replacing them with 0 depends on your business logic. Skipping NULLs gives a more accurate average, while replacing with 0 includes "no response" as a zero score.

In WHERE and ORDER BY

sql

1-- NULLs in WHERE — cannot use = NULL, must use IS NULL
2SELECT * FROM products WHERE price IS NULL;
3
4-- Replace NULL for sorting (NULLs sort first by default in MySQL)
5SELECT * FROM products
6ORDER BY IFNULL(price, 0);
7
8-- Or use COALESCE with a large value to sort NULLs last
9SELECT * FROM products
10ORDER BY COALESCE(price, 99999999);

Updating NULL to 0 Permanently

sql

1-- Replace NULL values in the actual table
2UPDATE products
3SET price = 0
4WHERE price IS NULL;
5
6-- Replace NULLs in multiple columns
7UPDATE products
8SET
9    price = IFNULL(price, 0),
10    discount = IFNULL(discount, 0),
11    stock_count = IFNULL(stock_count, 0);
12
13-- Prevent future NULLs with a DEFAULT and NOT NULL constraint
14ALTER TABLE products
15MODIFY COLUMN price DECIMAL(10,2) NOT NULL DEFAULT 0;

If NULL should never appear in a column, add a NOT NULL constraint with a DEFAULT value to prevent it at the schema level.

In JOIN Results

sql

1-- LEFT JOIN produces NULLs for non-matching rows
2SELECT
3    c.customer_name,
4    IFNULL(SUM(o.amount), 0) AS total_spent
5FROM customers c
6LEFT JOIN orders o ON c.id = o.customer_id
7GROUP BY c.customer_name;
8
9-- Without IFNULL, customers with no orders show NULL instead of 0

LEFT JOINs are a common source of unexpected NULLs. Wrapping the aggregated column in IFNULL ensures customers with no orders show 0 instead of NULL.

Common Pitfalls

Using = NULL instead of IS NULL: WHERE price = NULL returns no rows because NULL = NULL evaluates to NULL (unknown), not TRUE. Always use IS NULL or IS NOT NULL.
NULL in arithmetic: 5 + NULL = NULL, 10 * NULL = NULL. Any arithmetic with NULL produces NULL. Wrap each nullable operand in IFNULL() before calculating.
IFNULL vs COALESCE portability: IFNULL() is MySQL-specific. If you migrate to PostgreSQL, Oracle, or SQL Server, use COALESCE() for cross-database compatibility.
AVG with replaced NULLs changes the result: AVG(score) skips NULLs (divides by non-NULL count). AVG(IFNULL(score, 0)) includes them as zeros (divides by total count), which lowers the average.
Forgetting NULLs in GROUP BY: GROUP BY treats all NULL values as equal and groups them together. If you need NULLs in separate groups, replace them with a sentinel value before grouping.

Summary

IFNULL(column, 0) is the simplest MySQL way to replace NULL with 0
COALESCE(column, 0) is the SQL-standard equivalent and supports multiple fallback values
Aggregate functions (SUM, AVG, COUNT(col)) skip NULLs by default — wrap in IFNULL if you want to treat them as 0
Use UPDATE ... SET col = IFNULL(col, 0) to permanently replace NULLs in the table
Add NOT NULL DEFAULT 0 constraints to prevent future NULLs at the schema level