NVL function helps you convert a null to an actual value.
Syntax:- NVL (expr1, expr2)
In the syntax:
expr1 is the source value or expression that may contain a null
expr2 is the target value for converting the null
Guidelines for NVL function:-
1) Data types that can be used are date, character and number.
2) Data types must match:
NVL (commission, 0) here commission must be of NUMBER type
NVL (hire_date, 11-JUL-14) here hire_date must be of DATE type
NVL (job_title, Software Developer), here job_title must be of CHAR or VARCHAR2
The above query calculates the annual salary of all employees by multiplying the monthly salary by 12 and then adding the commission to it.
NOTE: The annual compensation is calculated only for those employees who earn a commission.
If any column value in an expression is null, the result is null. To calculate values for all employees,
we convert the null value to a number before applying the arithmetic operator. In the above query
, the NVL function is used to convert null values to zero.