NVL, NVL2 and NULLIF Function

NVL, NVL2 and NULLIF Function

NVL(),NVL2() and NULLIF() :- These functions are used to treat the null value.

NVL():- This function is used when we want to put any value in place of null value in column.

Syntax:- nvl(column_name,value)

Example:- select empno,ename,job,sal,nvl(comm,10) from emp;

This will show value 10 in place of null value in comm column

NVL2():- This function is used when we want to put some value in place of null values and want to put some different value in place of not null values.

Syntax:- nvl2(column_name,value1,value2)

Example:-  select empno,ename,job,sal,nvl2(comm,10,20) from emp;

This will show value 10 where comm column have not null values and will show 20 where comm column has null value

NULLIF() :- This function is used to compare values of two columns.It returns null when values are same otherwise it will return column1 value.

Syntax:- nullif(column1,column2)

Example:- select empno,ename,job,sal,nullif(sal,comm) from emp;