These functions work with any data type and pertain to the use of null values in the expression list. These functions use of null value and returns result value.
NVL : Converts null value to an actual value.
NVL2 : If first expression is not null, return second expression. If first expression is null, return third expression. the first expression can have any data type.
COALESCE : Return first not null expression in the expression list.
NULLIF : Compares two expressions and returns null if they are equal,returns the first expression if they are not equal.
Example
Sample Table : employees
ID SALARY COMMISSION 1 10000 0.05 2 8000 null 3 5000 0.1 select ID, SALARY, (NVL(COMMISSION,1)*SALARY)+SALARY as COM_SALARY from employees;
Result :
ID SALARY COM_SALARY 1 10000 10500 2 8000 8000 3 5000 5500 If you do not use NVL function, when COM_SALARY have a null value, print null.