How IFNULL() Works in MariaDB

In MariaDB, the IFNULL() function allows us to replace NULL values with another value.

Syntax

IFNULL(expr1,expr2)

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

Also, from MariaDB 10.3, NVL() can be used as an alias for the IFNULL() function. Therefore, we have the option of using the following syntax instead:

NVL(expr1,expr2)

The result is the same regardless of which syntax is used.

Example

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

SELECT IFNULL( null, 'No Value' );

Result:

No Value

In this case, I used IFNULL() to replace the NULL value with No Value.

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

SELECT IFNULL( 'Spicy', 'No Value' );

Result:

Spicy

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

Database Example

Suppose we run the following query:

SELECT * FROM Employees;

Result:

empId	name	dept
-----   -----   -----
1	Jess	Sales
2	Rohit	NULL
3	Zohan	Sales
4	Homer	NULL

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

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

SELECT
    empId,
    name,
    IFNULL( dept, 'Not yet assigned' ) AS dept
FROM Employees;

Result:

empId	name	dept
-----   -----   ----------------
1	Jess	Sales
2	Rohit	Not yet assigned
3	Zohan	Sales
4	Homer	Not yet assigned

Expressions

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

SELECT IFNULL( 3 * 7, 0 );

We get this:

21

So, we don’t get the 3 * 7 part. We get the result of that expression (which in this case is 21).

The same is true when the result of the expression is NULL. For example, if we run the following code:

SELECT IFNULL( 3 / 0, 0 );

We get this:

0.0000

However, this is a dangerous example. Zero is a value. NULL is not.

Returning zero when there’s a NULL value could be misleading or even completely wrong. Imagine if we were dealing with prices. We could end up with a price of zero, which could be incorrect and cost the business a lot of money.

Therefore in such cases it’s usually better to use a more meaningful value that conveys to the reader that there is no value.

Example:

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

Result:

No value

The NVL() Function

Starting with MariaDB 10.3, NVL() is an alias for IFNULL(). Therefore, we can replace IFNULL() with NVL() in any of the above examples.

Example:

SELECT NVL( 3 / 0, 'No value' );

Result:

No value