NVL Function in Oracle SQL

Oracle SQL / PLSQL uses NVL function to substitute a value whenever a NULL is encountered.

Syntax for the NVL function in Oracle SQL / PLSQL is:

SELECT NVL(string1, replace_with)
FROM table_name;

‘string1’ is the field or column for testing NULL value
‘replace_with’ is the string with which the NULL values will be substituted with.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000
104	Emp D	30000
105	Emp E	32000

Example 1: Using Oracle SQL / PLSQL NVL Function with numbers and string

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL(department,'No Department')
       ,NVL(commission,0)
FROM employee;

We will get the following output:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	No Department	0
104	Emp D	30000	No Department	0
105	Emp E	32000	No Department	0

Using the Oracle SQL /PLSQL NVL Function in example above we have substituted a string and number type in ‘department’ and ‘commission’ columns respectively values ‘No Department’ and ‘0’.