NVL Function in SQL

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.

How to Use the NVL Function in SQL?

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

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.

Wait! What happened just now?

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

Various NVL Functions in SQL

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

• NVL2
• DECODE
• COALESCE
• NULLIF
• NANVL

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.

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.

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