What Are The NVL and NVL2 Functions And When Should I Use Them?

The NVL and NVL2 functions in Oracle SQL have very similar names and similar functionality. In this article, I'll explain the difference between them and when to use each of them.

What Is the Oracle NVL Function?

The NVL function is used to return a different value if a NULL value is found in a specific expression.

The syntax of this function is:

NVL ( check_value, value_if_null )

It only has two parameters, the value to check, and the value to replace it with.

First, the check_value is entered. This is the value you want to check for a NULL value.

Then, the value_if_null is entered. This is the value to replace an occurrence of NULL in the check_value with.

So, the logic happens like this:

• Is check_value = NULL?
  • True: Return value_if_null
  • False: Return check_value

The check_value is used both as a return value and as the value to check for NULLs.

For example:

SELECT first_name,
last_name,
NVL(address , 'No address')
FROM employee;

This will show the first name, last name, and address for each employee. If an employee does not have an address, the value of "No address" will appear.

What Is the Oracle NVL2 Function?

The Oracle NVL2 function is a little different. It is also used to check if a value is NULL and return a different value if it is.

However, you also specify a value to return if the value to be checked is not NULL.

NVL2 is also an Oracle-specific function, so if you're planning on moving database systems, it's something to consider.

The syntax of NVL2 is:

NVL2 ( check_value, value_if_not_null, value_if_null )

There are three parameters. The first is the check_value, is also checked for NULL, just like the NVL function.

The second parameter, value_if_not_null is returned if the check_value is not NULL.

The final parameter, value_if_null, is returned if the check_value is NULL.

In this function, a value other than the check_value is returned in either condition.

So, the logic for this function is:

• Is check_value = NULL?
  • True: Return value_if_null
  • False: Return value_if_not_null

For example:

SELECT first_name,
last_name,
NVL2(address, 'Has address', 'No address provided') AS address_check
FROM employee;

This query will show the employee's first name and last name. It also shows a value of either "Has address" or "No address provided", depending on if the address field is NULL.

When Should I Use NVL or NVL2?

There are many cases where you might want to use NVL or NVL2, or even a different function.

If you just need a simple comparison to see if a value is NULL and handle it, use NVL.

If you need an IF THEN ELSE syntax and just need to check for a NULL value, use NVL2.

If you're likely to move to a non-Oracle system, then use NVL (possible with a CASE statement).

So, that's how to use the NVL and NVL2 functions in Oracle. They are similar, but have some advantages, depending on your situation.