From MariaDB 10.3, NVL() can be used as an alias for the IFNULL() function. Therefore, both functions enable us to replace NULL values with another value.

Syntax

NVL(expr1,expr2)

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

Given NVL() is an alias for IFNULL(), we can also use the following syntax:

IFNULL(expr1,expr2)

Both of the above syntaxes return the same result.

Example

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

SELECT NVL( null, 'No Value' );

Result:

No Value

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

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

SELECT NVL( '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 NVL() to replace the NULL values with a more meaningful value for the reader:

SELECT
    empId,
    name,
    NVL( 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 NVL( 4 * 10, 0 );

We get this:

40

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

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

SELECT NVL( 4 / 0, 0 );

Result:

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.

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 NVL( 4 / 0, 'No value' );

Result:

No value

The IFNULL() Function

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

Example:

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

Result:

No value