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:

Person_Id Name Work_Phone Mobile_Phone
1 John Wick 3230000000 6260000000
2 Carlito Brigante 2130000000
3 Frank Martin 5450000000

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:

PersonId Name Phone
1 John Wick 3230000000
2 Carlito Brigante 2130000000
3 Frank Martin 5450000000

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 SQL Video Course Discount Offer

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:

PersonId Name Work Phone Mobile Phone Accepts_Calls
1 John Wick 3230000000 6260000000 Y
2 Carlito Brigante 2130000000 Y
3 Frank Martin 5450000000

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:

Name Contact_Method
John Wick Call 3230000000
Carlito Brigante Call 2130000000
Frank Martin Do not call

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

Name Contact_Method
John Wick Call 3230000000
Carlito Brigante Call 2130000000
Frank Martin Call

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:

Name Contact_Method
John Wick Call 3230000000
Carlito Brigante Call 2130000000
Frank Martin Call 5450000000

Pretty cool stuff.


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:

PersonId Name Work_Phone Mobile_Phone Email_Address Twitter_Handle
1 John Wick 3230000000 john@test.com
2 Carlito Brigante @CarlitoBrigante-55
3 Frank Martin 5450000000 Frank@transporter.com

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:

Name Contact_Data
John Wick 3230000000
Carlito Brigante @CarlitoBrigante-55
Frank Martin 5450000000

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:

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

Opt In Image
Don't want to miss any Oracle SQL tips?

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

0 0 votes
Article Rating


I've been working with Oracle databases on a daily basis for more than 10 years.

newest most voted
Inline Feedbacks
View all comments