3

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 😉

NVL

This is an Oracle-only 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.

NVL2

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?

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 record. 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.

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:

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.

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.

Carlos

Carlos

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

Leave a Reply

3 Comments on "Oracle NVL, NVL2 and COALESCE functions"

avatar
Sort by:   newest | oldest | most voted
trackback

[…] NVL function is very useful in a lot of different situations  and is probably one of the most widely used […]

philix
Guest

great website.

trackback

[…] NVL function is very useful in a lot of different situations and is probably one of the most widely used among […]

wpDiscuz