NVL Function in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

NVL function is defined only in Oracle, not in SQL Server or MySQL. It works like the IFNULL function in MySQL. We can replace the value of NULL or any void present in the existing table with another value of our choice.

What is the NVL Function in SQL?

NVL function is not defined in MySQL or SQL servers but it is defined in Oracle. In SQL servers IFNULL is defined. NVL works the same as the IFNULL function in SQL. To replace the value of NULL, void, empty, or zero we use the NVL function. It returns either numeric or string values.

Syntax

The Syntax of NVL function: -

NVL function takes two arguments.

  • Value - The name of the expression whose value is to be changed.
  • Substitute - The value you want in place of the previous value. This value will be returned in the result.

If the value is null then NVL will return a substitute else it will return the value itself.

How to Use the NVL Function in SQL?

Suppose we have the following table named as an employee: -

employee_nameemployee_id
Sarah1012
AlbertNULL
Elliot1019
Nora1011

We want to replace the NULL value with the employee_id to 1025. Now, to do that we will use the NVL function as shown.

This will return the following result.

id
1012
1025
1019
1011

Wait! What happened just now?

The value of NULL just got replaced with the integer value that we wanted in place of NULL.

If you are working with MySQL workbench or SQL server then there will be an error saying the FUNCTION sys.nvl does not exist. It throws this error because the NVL function is defined only in Oracle. So, if you are working with SQL then use IFNULL.

Various NVL Functions in SQL

There are various functions that work like NVL function. They are: -

Let's understand their work and syntax. We won't be looking at these functions in detail here.

1. NVL2

This function first checks if the first expression is null or not. It takes 3 arguments as input. If the first expression is found null then it will return expression2 otherwise it will return expression3. This works for any data type.

Syntax

2. DECODE

It works similarly to IF-THEN-ELSE logic that is used in other programming languages. It will check if the value of an expression is the same as a search. If it is the same then the expression is returned otherwise it will return NULL where the value is not the same as the search value.

Syntax

3. COALESCE

COALESCE function can take multiple alternate values. It will check if the first expression is null or not. It returns the first non-null value mentioned in the list.

Syntax

4. NULLIF

In the NULLIF function, if the value of both expressions is the same, then it will return null otherwise it will return the value of the first expression.

Syntax

5. NANVL

NANVL is only used for floating numbers that are of the type binary_float or binary_double. If the value of n1 is found as nan then it will return the value of n2. But if the value of n1 is not nan, then it will return the value of n1.

Syntax

Example of NVL Function in SQL

Let's take the data of an employee.

employee_nameemployee_idaddress
Sarah1012NYC
Elliot1097California
Nora1024NULL
Alyesha1075Canada
Nathan1011NULL

Now, we have to replace the value of NULL with something valuable like for example "The address does not exist".

So, to do that we will write the following query.

The above query will give the following result.

employee_nameaddress
SarahNYC
ElliotCalifornia
NoraThe address is not known!
AlyeshaCanada
NathanThe address is not known!

In the above result, the value of NULL got replaced by the string we entered.

Conclusion

  • NVL function is used to replace NULL, zero, void, or empty with any value that you want it can be an integer or string.
  • NVL function does not exist in MySQL or SQL servers. It is defined only in Oracle.
  • Various functions work like the NVL function. For example - NULLIF, NANVL, DECODE, COALESCE, and NVL2.

See Also: