Oracle NVL, NVL2 and COALESCE functions

I have seen quite a few questions on internet forums and knowledge exchange sites about Oracle NVL, NVL2, and COALESCE functions for handling nulls, so I decided to write an article about them today.

Those functions are actually similar, but knowing what differentiates them from each other can help you use the correct one for a given situation. Also, using them correctly at work can make you look like a seasoned SQL developer 😉 .

Oracle NVL

This is an Oracle-specific function and is probably the most commonly used by Oracle developers. Its purpose is to let you replace NULLs with another value or expression.

This is the syntax:

NVL(expression1, expression2)

And this is how it works: If expression1 is not NULL, then expression1 is returned. If expression1 is NULL then expression2 is returned.

In general, the return type will be that of the first expression.

Let’s see it in action. Suppose we have a simple table with this data:

And we run the following query:

SELECT Person_Id, Name, NVL(Work_Phone, Mobile_Phone) AS Phone
FROM ourTable;

This is what we would get:

The first expression (Work_Phone) was returned for the first two records, because it was not null, and since Work_Phone was null in record 3, the value from Mobile_Phone was returned.

Oracle NVL2

Like Oracle NVL, this is another Oracle-specific function, which lets you decide which value to return, based on whether a specified expression is null or not. There’s another function that can work in a very similar way (DECODE), but we will talk about it some other day.

This is NVL2’s syntax:

NVL2(expression1, expression2, expression3)

And this is how it works: Expression1 is the one that will be evaluated. If it is not null, then expression2 is returned. If it is null then expression3 is returned. See the difference?

Unlike the Oracle NVL function, the first expression in NVL2 is never returned.  It is only used to determine whether expression2 must be returned, or expression3.

Let’s see an example. Suppose our test table is now like below, and that persons with the Accepts_Calls column null must not be contacted by phone:

If we run this query:

SELECT name,
       NVL2(accepts_calls, 'Call ' || work_phone, 'Do not call') contact_method
FROM ourTable;

This is what we would get:

Now, what happens if ‘Frank Martin’ has his Accept_Calls column not null? Our previous query would return:

Why? because Work_Phone is null for that row. But this can be easily fixed by combining the NVL and NVL2 functions:

SELECT name,
       NVL2(accepts_calls, 'Call ' || NVL(work_phone,mobile_phone), 'Do not call') contact_method
FROM ourTable;

This way we get:

Pretty cool stuff.

COALESCE

This function is actually part of the SQL standard, and thus is implemented in most DBMSs. Basically, it will return the first non-null value from a list of expressions provided.

This is the syntax:

COALESCE(expression-1, expression-2, ... , expression-n)

And this is how it works: It will accept any number of expressions or parameters (at least 2) and will return the first of them that is not null (evaluating from left to right). If all of them are null, then it returns null.

To see an example, suppose our test table now has the following structure and data:

And we want to return each person’s first available contact data.

Our query would be as simple as this:

SELECT name,
       COALESCE(Work_Phone,Mobile_Phone,Email_Address,Twitter_Handle) AS Contact_data
FROM ourTable;

Giving us the desired result:

So, there you have it. Three very useful Oracle functions to handle nulls.

In case you want to take a look at the official documentation on these functions, here are the links:

• NVL
• NVL2
• COALESCE

Comments and questions are very welcome. Don’t be shy.

Don't want to miss any Oracle SQL tips?

Subscribe to be informed about new posts, tips and more awesome things.

As a welcome gift, I will send you a copy of my Basic SQL Developer Cheat Sheet for Developers - A small compilation of my favorite SQL Developer features and shortcuts.