MySQL IFNULL() Explained

MySQL has an IFNULL() function that allows us to easily replace NULL values with another value.

Syntax

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

Example

Here’s a basic example that uses the NULL constant:

SELECT IFNULL( null, 'n/a' );

Result:

n/a

In this case, I used IFNULL() to replace the NULL value with the string n/a.

Here’s what happens when the first argument is not NULL:

SELECT IFNULL( 'Cow', 'n/a' );

Result:

Cow

In this case, the first argument is returned, because it’s not NULL.

Database Example

Suppose we run the following query:

SELECT
    PetName,
    DOB
FROM Pets;

Result:

+---------+------------+
| PetName | DOB        |
+---------+------------+
| Fluffy  | 2020-11-20 |
| Fetch   | 2019-08-16 |
| Scratch | 2018-10-01 |
| Wag     | 2020-03-15 |
| Tweet   | 2020-11-28 |
| Fluffy  | 2020-09-17 |
| Bark    | NULL       |
| Meow    | NULL       |
| Woof    | 2020-10-03 |
| Ears    | 2022-01-11 |
+---------+------------+

We can see that two rows have NULL values in the DOB column.

In the following query, we use IFNULL() to replace the NULL values with a more meaningful value for the reader:

SELECT
    PetName,
    IFNULL( DOB, 'None supplied' )
FROM Pets;

Result:

+---------+--------------------------------+
| PetName | IFNULL( DOB, 'None supplied' ) |
+---------+--------------------------------+
| Fluffy  | 2020-11-20                     |
| Fetch   | 2019-08-16                     |
| Scratch | 2018-10-01                     |
| Wag     | 2020-03-15                     |
| Tweet   | 2020-11-28                     |
| Fluffy  | 2020-09-17                     |
| Bark    | None supplied                  |
| Meow    | None supplied                  |
| Woof    | 2020-10-03                     |
| Ears    | 2022-01-11                     |
+---------+--------------------------------+

Expressions

The current value of the first argument is evaluated. Therefore, if we provide an expression like the following:

SELECT IFNULL( 2 * 5, 0 );

We get this:

10

So, we don’t get the 2 * 5 part. We get the result of that expression (which in this case is 10).

The same is true when the result of the expression is NULL:

SELECT IFNULL( 2 / 0, 0 );

We get this:

0.0000

It pays to be careful though. Zero is a value. NULL is not.

In the above example I returned zero when there was a NULL value, but this could be misleading or even completely wrong. Imagine if we were dividing prices, and the result was a price of zero. OK, if we’re dividing a price by zero there’s probably something else wrong, but I’m sure you get my point.

In any case, it’s usually better to use a more meaningful value that conveys to the reader that there is no value.

Example:

SELECT IFNULL( 2 / 0, 'No value' );

Result:

No value