MariaDB IFNULL(): Syntax, NULL Fallbacks and Examples

The MariaDB IFNULL() function returns its first argument when that argument is not SQL NULL. Otherwise, it returns the second argument. Use it to display a fallback value for missing data or to supply a defined result when an expression evaluates to NULL.

Syntax

IFNULL(expression, fallback)

IFNULL() takes exactly two arguments. It returns the first when it is non-NULL, including 0, FALSE, or an empty string, and uses the second only when the first is SQL NULL.

MariaDB also supports NVL(expr1, expr2) as an alias for IFNULL(). The result’s numeric or string type can depend on the expressions and the context in which it is used.

Examples

Replace SQL NULL with a fallback

SELECT
    IFNULL(NULL, 'No email') AS missing_email,
    IFNULL('[email protected]', 'No email') AS present_email;

missing_email  present_email
-------------  ----------------
No email       [email protected]

The second argument is a fallback, not a value to append or combine with the first.

Empty strings and zero are not NULL

SELECT
    IFNULL('', 'fallback') AS empty_string,
    IFNULL(0, 99) AS zero_value,
    IFNULL(NULLIF('', ''), 'fallback') AS empty_as_missing;

empty_string  zero_value  empty_as_missing
------------  ----------  ----------------
              0           fallback

IFNULL('', 'fallback') returns an empty string, not 'fallback'. If you need to treat an empty string as missing, explicitly convert it with NULLIF() first. Do not confuse this with the SQL IS NULL predicate.

Provide a display value for a missing column

SELECT
    product_name,
    IFNULL(description, 'Description unavailable') AS display_description
FROM products;

Here products(product_name, description) is an existing example table. IFNULL() does not update the original row; it only changes the expression returned by the query.

Calculate averages without hiding missing values

SELECT
    COUNT(price) AS known_prices,
    AVG(price) AS average_known,
    AVG(IFNULL(price, 0)) AS average_when_missing_means_zero
FROM (
    SELECT 10 AS price
    UNION ALL SELECT 20
    UNION ALL SELECT NULL
) AS sample_prices;

There are two known prices. AVG(price) is 15 because NULL is ignored, while AVG(IFNULL(price, 0)) is 10 because the missing value is treated as a third price of zero. Adding IFNULL() inside an aggregate is a business-rule choice, not a general best practice.

If your goal is to return zero only when no non-NULL rows contribute, place the fallback around the aggregate:

SELECT IFNULL(SUM(price), 0) AS total_price
FROM products
WHERE 1 = 0;

total_price
-----------
0

IFNULL versus COALESCE

IFNULL(a, b) and COALESCE(a, b) give the same fallback result for two arguments. COALESCE() can accept more than two values; read the MariaDB COALESCE() examples to compare them.

Frequently asked questions

Does IFNULL handle empty strings?

No. An empty string is a non-NULL value in MariaDB. Use NULLIF(column, '') first only if your application treats blank text as missing.

Is IFNULL the same as ISNULL?

No. IFNULL(a, b) chooses a fallback expression. MariaDB ISNULL(a) is a predicate-style function that returns 1 when a is SQL NULL and 0 otherwise.

Is IFNULL equivalent to NVL in MariaDB?

Yes. MariaDB documents NVL() as an alias for IFNULL(). For portability across database engines, review which function names your target system supports.

See the official MariaDB IFNULL documentation.