How to use the NVL and NVL2 functions in Oracle SQL

As an SQL professional or developer, at some point, you’re going to encounter the infamous NULL value (or marker as some say). NULL‘s can be super tricky to get right until you halfway understand them. Fortunately, all the popular SQL vendors have various functions in place that help work with NULLs. In this post, I cover 2 such functions found in the Oracle database, NVL() and NVL2().

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

For the examples in this post, I am using the well-known Oracle HR sample database.

The LOCATIONS table has mock address information as shown with the following query and result set:

SELECT * FROM LOCATIONS;

oracle-hr-schema-locations-table
Sample data from the LOCATIONS table.

Oracle NVL() function

Several rows store NULL in the STATE_PROVINCE column. If you are generating a report or some other end-user display from this data, likely the last thing you want to do is output NULL in the results. Of course, you could absolutely use a CASE expression and provide something more meaningful.

However, this is one use case (among many) for the NVL() function. NVL() syntax is super simple:

NVL(expression_1, expression_2)

With NVL(), if the 1st expression is NULL, the 2nd expression is returned. Otherwise, the 1st expression is returned.

Pro Tip: Both parameters must be provided and should be of matching data types.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Let’s write a query and display ‘UNKNOWN_PROVINCE‘ instead of NULL for the STATE_PROVINCE column where that column is NULL.

SELECT CITY, STATE_PROVINCE,
NVL(STATE_PROVINCE, 'UNKNOWN_PROVINCE') AS BETTER_NAMED, COUNTRY_ID
FROM LOCATIONS;

oracle-sql-nvl-function
The NVL() function allows you to substitue a value for any NULL value that is encountered.

As shown in the query results, anywhere the STATE_PROVINCE column is NULL, ‘UNKNOWN_PROVINCE’ is returned. For those rows that do not have NULL, their original column value is retained.

Oracle NVL() function in math calculations

It is well-understood that math calculations involving any NULL values, always return NULL. To restrict that from happening, one option is leveraging a function like NVL() and use an alternate value in place of NULL where needed, so that actual math calculations are returned.

For a more manageable data set, I’ll filter the EMPLOYEES table to just those rows with JOB_ID of ‘SH_CLERK’:

SELECT *
FROM EMPLOYEES
WHERE JOB_ID = 'SH_CLERK';

EMPLOYEES table with JOB_ID of ‘SH_CLERK’.

We can see that all the rows have NULL for the COMMISSION_PCT column. If we were calculating the commission amount for each row in a report, these rows would display plain old boring NULL:

SELECT EMPLOYEE_ID, SALARY, SALARY * COMMISSION_PCT AS COMMISSION
FROM EMPLOYEES
WHERE JOB_ID = 'SH_CLERK';

Rows displaying NULL for the COMMISSION_PCT column value.

However, using NVL(), we can provide a 0 (zero) number value (or others) in order for the calculations to take place. In this example, the math returns 0 (zero) which is instead displayed:

SELECT EMPLOYEE_ID, SALARY,
SALARY * NVL(COMMISSION_PCT, 0) AS COMMISSION
FROM EMPLOYEES
WHERE JOB_ID = 'SH_CLERK';

Using NVL() function in math calculations.

Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously!

Oracle NVL2() Function

An alternative for NVL() is the similar-named NVL2() function which takes 3 parameters:

NVL2(expression, if_not_null, if_null)

NVL2() allows for a value to be returned no matter if the 1st argument expression evaluates to NULL or not.

Building off of the example using the STATE_PROVINCE column, in this query, we will use NVL2() and display ‘KNOWN_PROVINCE’ for those rows where the STATE_PROVINCE column value is not NULL. As before, rows having NULL will display ‘UNKNOWN_PROVINCE‘:

SELECT CITY, STATE_PROVINCE,
NVL2(STATE_PROVINCE, 'KNOWN_PROVINCE', 'UNKNOWN_PROVINCE') AS BETTER_NAMED,
COUNTRY_ID
FROM LOCATIONS;

oracle-sql-nvl2-function
Using the NVL2() function, you can specify a return value for both NULL and non-NULL values.

In this result set, NVL2() returns 1 of the 2 available values from the if_not_null or if_null parameters depending on if said row contains NULL for the specified expression.

Oracle NVL2() function in math calculations

As with the NVL() example math calculations, we can also use NVL2() in a similar sense. But, with NVL2(), we have more options.

Let’s visit the below arbitrary query:

SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(140,141,150,151,172);

Imagine you would like to apply any rows’ COMMISSION_PCT to a calculation with its SALARY. Where the SALARY is not NULL, go ahead and use that value. Yet, if a row does contain NULL for the COMMISSION_PCT, apply some value anyways. Say in this example, apply a COMMISSION_PCT value of 0.05 for any rows having NULL.

SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT,
SALARY * NVL2(COMMISSION_PCT, COMMISSION_PCT, 0.05) AS COMMISSION
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(140,141,150,151,172);

Again, a CASE expression (among other means) could be used in this type of query. However, using NVL2() is more succinct in my opinion, yet, still provides a bit of flexibility.

Oracle NVL() and NVL2() Closing and Further Reading

Although some of the examples in this post are oversimplified, hopefully, they provide you with ideas of how you can use NVL() and NVL2() in your queries.

Drop me a comment and tell me about some of the interesting ways you have used the NVL() or NVL2() functions.

If you have any questions or see anything incorrect in the code, please leave a comment in the comments section below. Thank you.

Similar Posts You Will Enjoy

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.



To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

2 thoughts on “How to use the NVL and NVL2 functions in Oracle SQL”

  2. Pingback: Two SQL Queries to find rows present in one table but not another. - Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.