Let’s understand Null Value Function in SQL. In SQL, We have the inbuilt function to handle the Null values.
In Oracle SQL we have three functions-
NVL (exp1, exp2)
NVL2 (exp1, exp2, exp3)
NULLIF (exp1, exp2)
These functions are used to handle the Null value in SQL and perform the operation on Null values, present in the data table.
Let’s see the use of all functions.
Handle Null Value in SQL
NVL() function converts a null value to an actual value. The data types that can be used in NVL function are the date, character, and number.
The datatype of exp2 must match the datatype of exp1.
Example – NVL (commission_pct, 0) – It will replace the Null with 0.
NVL (hire_date, ’01-JAN-16′) – It will replace the null date with given date.
Query – Calculate the Annual salary of all employees with the commission.
So in this case first we will convert the Null commission with some value then calculate the Annual Salary.
SQL> select First_name, ((Salary*12)+ (salary*12*NVL(commission_pct,0))) As “Annual Salary” from employees;
Syntax : NVL2 (exp1, exp2, exp3)
In NVL2 function if exp1 is not null then it will return the exp2 if exp1 is Null then function will return the exp3.
The Format Model :
The data type of all expressions must be same.
Query – Find salary of all employees with the commission.
SQL> Select salary, commission_pct, NVL2(commission_pct, salary+commission_pct, salary) as “Full Salary” from employees;