Some DBMSs provide an NVL() function, which can be used when working with potentially NULL values. The function allows us to replace any NULL values with another value.

Syntax

The exact syntax will depend on your DBMS, but it will most likely fall under one of the following:

NVL( expr1, expr2 )

With this syntax, if expr1 is null, then expr2 is returned. Otherwise expr1 is returned. In some DBMSs (such as MariaDB) this makes NVL() a synonym for its IFNULL() function.

DBMSs that use this syntax include Oracle, MariaDB, Exasol, Vertica, Snowflake, and IBM Informix.

Other DBMSs use the following syntax:

NVL( expression, expression, ... )

With this syntax, we can provide any number of arguments, and NVL() will return the first non-NULL value. This syntax makes NVL() a synonym for COALESCE() (which returns the first non-NULL value of its arguments).

Products that use this syntax include Amazon Redshift and IBM DB2.

Amazon Redshift implements NVL() as an expression (as opposed to a function) that works identically to its COALESCE() expression.

Example – Syntax 1

Here’s a basic example that demonstrates the first syntax of the NVL() function:

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.

In Oracle Database, we would need to add FROM DUAL from such examples. Here’s an example that uses Oracle:

SELECT NVL(null, 'Fish')
FROM DUAL;

Result:

Fish

Example – Syntax 2

Here’s an example of how NVL() works in DBMSs that use the second syntax:

SELECT NVL(null, 'Papaya', 'Salad');

Result:

Papaya

In this case, Papaya was the first non-null value, and so NVL() returned that value.

We would get the same result if we had more NULL values before the non-NULL value:

SELECT NVL(null, null, null, 'Papaya', 'Salad');

Result:

Papaya

As mentioned, this is exactly how COALESE() works. It simply returns the first non-NULL value.

NVL2()

Most DBMSs that support NVL() also have an NVL2() function that works similar to NVL(). The difference is that NVL2() accepts three arguments. This allows us to specify a different value to return if the value is not NULL.

Example:

SELECT NVL2(1, 2, 3);

Result:

2

In this case, the first argument is not NULL, and so the second argument is returned.

If the first argument is NULL, then the third argument is returned:

SELECT NVL2(null, 2, 3);

Result:

3