MySQL IFNULL() Function: Return Fallback Values for NULL

In MySQL, the IFNULL() function tests whether an expression is NULL and returns an alternative fallback value if it is. If the expression is not NULL, IFNULL() returns the expression unchanged.

IFNULL() is commonly used to clean up report outputs, prevent arithmetic errors when calculating totals across columns with missing data, and provide default strings for user profiles.

`IFNULL()` Syntax

Here is the syntax of the MySQL IFNULL() function:

IFNULL(expr1, expr2)

Parameters

expr1: Required. The expression to test for NULL.
expr2: Required. The fallback value returned if expr1 evaluates to NULL.

Return value

The IFNULL() function returns expr1 if expr1 is not NULL. If expr1 is NULL, it returns expr2. If both expr1 and expr2 are NULL, the function returns NULL.

The data type of the returned value is determined by the types of both expressions. If one expression is a string and the other is numeric, MySQL converts the result to a string.

`IFNULL()` Examples

Basic NULL replacement

If the first argument is NULL, IFNULL() returns the second argument:

SELECT IFNULL(NULL, 'Default Title') AS result;

+---------------+
| result        |
+---------------+
| Default Title |
+---------------+

If the first argument contains a non-NULL value, IFNULL() returns that value and ignores the fallback:

SELECT IFNULL('Active Customer', 'Unknown') AS result;

+-----------------+
| result          |
+-----------------+
| Active Customer |
+-----------------+

Numeric fallbacks in calculations

When calculating values across database rows, missing data can cause mathematical expressions to return NULL. Use IFNULL() to supply a neutral zero:

SELECT 100 + IFNULL(NULL, 0) AS total_amount;

+--------------+
| total_amount |
+--------------+
|          100 |
+--------------+

Without IFNULL(), 100 + NULL evaluates to NULL.

Handling columns in table queries

Consider a customers table where some contacts have not provided a phone number:

SELECT customer_name,
       IFNULL(phone, 'No Phone Provided') AS contact_phone
FROM customers;

+---------------+-------------------+
| customer_name | contact_phone     |
+---------------+-------------------+
| Sarah Connor  | 555-0199          |
| John Doe      | No Phone Provided |
+---------------+-------------------+

`IFNULL()` versus `COALESCE()` and `ISNULL()`

MySQL provides several functions for inspecting and handling null values, but their purposes differ:

IFNULL(expr1, expr2): A MySQL-specific convenience function that takes exactly two arguments.
COALESCE(expr1, expr2, ...): The ANSI SQL standard function that accepts two or more arguments and evaluates them in order until the first non-NULL value is found.
ISNULL(expr): A one-argument boolean test in MySQL that returns 1 if the argument is NULL and 0 if it is not. Unlike SQL Server’s ISNULL(), MySQL’s ISNULL() does not replace values.

To compare NULL replacement strategies, type precedence, and truncation risks across MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle, see the SQL NULL Handling by Database guide.

Conclusion

The IFNULL() function provides a straightforward way in MySQL to replace NULL values with meaningful defaults. For portable SQL that runs across multiple database management systems, or when evaluating a fallback chain of three or more values, prefer COALESCE().

IFNULL() Syntax