Copyright

SQL: NVL Function

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

We're off to NVL-land: an option available only in Oracle databases, it is a useful function that can replace a blank value with some other value. This lesson will cover the syntax of the function and provide examples.

NVL-Land

NVL is available only in Oracle, and not in MySQL or SQL Server. Unlike a lot of programming/database terms, NVL is not an acronym for anything. It's just NVL, though it can help to think of it as Null VaLue.

NVL is a substitution function; that is, it is used to display one value if another value is NULL. And not just zero, but NULL, empty, void. Think of an employee data table: If a certain data field is NULL, such as cell phone number, you could use NVL to substitute in a generic number (maybe their desk phone, or 999-9999).

The syntax is:


NVL(value, substitute);


The concept is like this: If the value is NULL, NVL will display the value stated in the substitute key word. The data types don't matter. If the data types are different, however, Oracle will change the substitute to the data type of the original value.

Examples

NVL accepts almost any data type (except objects), including numeric and string. Here we will take a look at some examples of using NVL.

Simple Query

Let's say we have the following music table (tblAlbum).

Artist_Name Album_Title Genre Rating
Journey Raised on Radio Rock 9.5
Debussy La Mer Classical
Kate Wolf A Poets Heart Folk 9

And we want to replace the null value of the rating with a 5 (we can assume an album rated as a 5 hasn't been rated/reviewed yet; it's sort of a neutral value). The following code will return 5's for any unrated albums.


SELECT NVL(Rating, 5) FROM tblAlbum;


Summing Values

If you try to add/sum a column in which there are null values, you will probably get some errors. That is because the database system doesn't see a NULL as a 0; it's a NULL. Empty. Void. Undefined. Therefore, if you try to sum up a column with NULL values, it won't work.

The following example adds up the inventory counts from an Inventory table. We know we found a couple of hair-dryers laying in the stockroom, so let's replace the NULL with a value, say 5. The table below:

Product Count
Combs 100
Brushes 10
Dryers NULL
Curlers 500

And the SQL needed to sum everything:


SELECT SUM(NVL(Inventory, 5)) FROM Inventory;


The total would then be 615 since we replaced the NULL with 5.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support