4 Ways to Replace NULL with a Different Value in MySQL

Use COALESCE(), IFNULL(), IF(), or CASE to return a fallback when a MySQL expression is NULL. These expressions change the query result; they do not update the value stored in the table. The examples use orders.shipping_fee and return 0 when that column is NULL.

1. Use COALESCE() for one or more fallbacks

COALESCE() returns the first argument that is not NULL. Use it when you have several possible values to try:

SELECT
    order_id,
    COALESCE(shipping_fee, default_shipping_fee, 0) AS shipping_fee
FROM orders;

MySQL checks shipping_fee first, then default_shipping_fee, and returns 0 only if both are NULL. COALESCE() can take more than two arguments and is part of the SQL standard.

For a single fallback, the shorter form is:

SELECT COALESCE(shipping_fee, 0) AS shipping_fee
FROM orders;

See SQLiz’s MySQL COALESCE() reference.

2. Use IFNULL() for a two-value fallback

IFNULL(expr, fallback) returns expr when it is not NULL; otherwise, it returns fallback:

SELECT
    order_id,
    IFNULL(shipping_fee, 0) AS shipping_fee
FROM orders;

IFNULL() handles exactly two arguments. Choose COALESCE() if you need to test a sequence of fallback columns. See the MySQL flow-control function reference.

3. Use IF() with an explicit IS NULL condition

IF(condition, value_if_true, value_if_false) selects one of two values. To replace only NULL, test it explicitly:

SELECT
    order_id,
    IF(shipping_fee IS NULL, 0, shipping_fee) AS shipping_fee
FROM orders;

Do not write IF(shipping_fee, 0, shipping_fee) for a NULL check: 0 is false in a MySQL condition, so that expression would also replace a real zero. MySQL’s IF() and IFNULL() functions have different purposes: IF() evaluates a condition, while IFNULL() checks its first argument for NULL.

4. Use CASE for portable or multi-condition logic

CASE is useful when the fallback depends on more than whether a value is NULL:

SELECT
    order_id,
    CASE
        WHEN shipping_fee IS NULL THEN 0
        WHEN shipping_fee < 0 THEN 0
        ELSE shipping_fee
    END AS displayed_shipping_fee
FROM orders;

This example replaces NULL and negative fees with 0. For only a NULL fallback, CASE WHEN shipping_fee IS NULL THEN 0 ELSE shipping_fee END is equivalent to IF(shipping_fee IS NULL, 0, shipping_fee).

For more CASE patterns, see SQLiz’s MySQL CASE guide.

Important: NULL is not an empty string or zero

These methods replace SQL NULL. They do not treat an empty string ('') or numeric zero (0) as missing. For example, IFNULL('', 'Unknown') returns the empty string. If empty strings should also use a fallback, test for both conditions:

CASE
    WHEN customer_name IS NULL OR customer_name = '' THEN 'Unknown'
    ELSE customer_name
END

To change stored data rather than only the query result, use an UPDATE with a WHERE column_name IS NULL condition. Review the target rows before making a permanent update.

Do not confuse IFNULL(expr, fallback) with ISNULL(expr): MySQL’s ISNULL() returns 1 or 0 to test whether an expression is NULL; it does not return a replacement value. Also keep fallback arguments type-compatible. MySQL derives the result type from the expressions passed to COALESCE(), IFNULL(), IF(), and CASE.