Summary -

In this topic, we described about the NVL Function with detailed example.

NVL function is used to replace NULL values in the existing table with specified value in result set. NVL function works on ORACLE database. NVL function returns numeric or string values based on the scenario.

Syntax -

SELECT NVL (Expression1, Expression2|Value) FROM table;
  • Expression1 – It represents the name of the expression or column name on which we want to test NULL values.
  • Expression2 – It represents the name of the expression or column name which we want to insert values on nullable column.
  • Value – It represents the value that we need to specify on required column which have the NULL values.

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Replacing NULL values in the existing table with specified value in result set by using NVL function.

Requirement - Replacing NULL values in employee_details with specified value in result set by using NVL function. The query was as follows – 

SELECT emp_id, emp_name, NVL(manager_id,'Employee0') manager_id 
FROM  employee_details;

By executing above query, we can get specified value on manager_id column in employee_details table because it consists NULL values. The output was as follows -

emp_id emp_name manager_id
001 Employee1 Employee0
002 Employee2 Employee0
003 Employee3 Employee1
004 Employee4 Employee2
005 Employee5 Employee3
006 Employee6 Employee3
007 Employee7 Employee3
008 Employee8 Employee4
009 Employee9 Employee4

Scenario – Replacing NOT NULL values in the existing table with specified value in result set by using NVL function.

Requirement - Replacing NOT NULL values in employee_details with specified value in result set by using NVL function. The query was as follows – 

SELECT NVL(emp_id,001)emp_id,emp_name FROM employee_details;

By executing above query, we can get existing emp_id values on employee_details because emp_id column does not have NULL values. The output was as follows -

emp_id emp_name
001 Employee1
002 Employee2
003 Employee3
004 Employee4
005 Employee5
006 Employee6
007 Employee7
008 Employee8
009 Employee9

Scenario – If we trying to combine multiple columns which have NULL values getting without NULL values in result set on existing table by using NVL function.

Requirement – If we trying to add emp_id, emp_name, manager_id, designation columns on employee_details table without getting NULL values by using NVL function. The query was as follows – 

SELECT emp_name+ NVL(manager_id, ' ')+ designation 'NAME WITH 
DESIGNATION' FROM employee_details;

By executing above query, we can get output without NULL values in result set from employee_details table. The output was as follows –

NAME WITH DESIGNATION
Employee1 Director
Employee2 Director
Employee3Employee1Manager
Employee4Employee2Manager
Employee5Employee3Analyst
Employee6Employee3Analyst
Employee7Employee3Clerk
Employee8Employee4Salesman
Employee9Employee4Salesman

Scenario – Replacing NULL values in the existing table with another specified column value in result set by using NVL.

Requirement – Replacing NULL values in employee_details with designation value in result set by using NVL. The query was as follows – 

SELECT  emp_id,emp_name,NVL(manager_id,designation )manager_id 
FROM employee_details;

By executing above query we can replace NULL values on manager_id column in existing employee_details table with designation column values. The output was as follows -

emp_id emp_name manager_id
001 Employee1 Director
002 Employee2 Director
003 Employee3 Employee1
004 Employee4 Employee2
005 Employee5 Employee3
006 Employee6 Employee3
007 Employee7 Employee3
008 Employee8 Employee4
009 Employee9 Employee4