difference between null functions| nvl,nvl2, nullif and coalesce in Oracle sql pl sql Interview Questions and Answers for Experienced Candidates.

 "It is most frequent question asked for PLSQL Developer and Oracle production support interviews. Every company is asking this."

INTRODUCTION:

The below functions are called as the single row function which provide the result per row. They are called as the oracle functions which will replace the null value to the specified value.In other words,one can say that this function is built for dealing with the null values. Developers are using this function for data analysis and processing. It is called as the built-in functions.Decode is the specialized version of nvl and nvl2.Decode can be the substitution for NVL and NVL2.

NVL:

It is said to be the SQL general function which checks the first input parameter value, if the first input parameter value is null then it will return the second input parameter value as output.

NVL(expr1,expr2):It converts the null value to the actual value. Expr1 and Expr2 is having the same data type.If the expr1 which is having the source value as null then it will be replaced by the second argument and called as target value.

It converts the null value to the not null value, if the second expression is not null. It accepts the data type like character, number,date and time. It is most popular general functions in the SQL.It will hold the two input values and if we try to provide more than two input values, it will provide you an error.This function will be returning the first not null value in the search expression.

It is used in the oracle but it is not used in MYSQL and SQL Server. It is used to replace the null, void, empty and zero. Postgress does not support NVL.Every column have same data type to avoid error.It is faster than coalesce function. It is evaluated once but coalesce function is evaluated several times.

                                                                  

•                                   SELECT NVL('X','Y') AS OUTPUT FROM DUAL;

• SELECT NVL(NULL,'B') AS OUTPUFROM DUAL;

• SELECT NVL('NULL','NULL') AS OUTPUT

FROM DUAL;

• SELECT NVL(NULL,NULL) AS OUTPUT FROM DUAL;

TABLE EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	800		20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7566	JONES	MANAGER	7839	02-APR-81	2975		20
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-MAY-81	2850		30
7782	CLARK	MANAGER	7839	09-JUN-81	2450		10
7788	SCOTT	ANALYST	7566	19-APR-87	3000		20
7839	KING	PRESIDENT		17-NOV-81	5000		10
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	23-MAY-87	1100		20
7900	JAMES	CLERK	7698	03-DEC-81	950		30
7902	FORD	ANALYST	7566	03-DEC-81	3000		20
7934	MILLER	CLERK	7782	23-JAN-82	1300		10

• select sal,nvl(comm,0),(sal*12)+(sal*12*NVL(COMM,0))annual_salary from emp;

• SELECT ENAME,SAL,NVL(COMM,0),(SAL)+(SAL*NVL(COMM,0)) MONTHLY_SALARY FROM EMP;

• SELECT NVL(1,2) AS OUTPUT from dual;

• SELECT NVL(NULL,2) AS OUTPUT from dual;

• SELECT NVL(1.56,2.78) AS OUTPUT from dual;

• SELECT NVL(NULL,2.78) AS OUTPUT from dual;

• SELECT NVL(1,2,3) AS OUTPUT FROM DUAL;

NVL2:

It is said to be the SQL General function which checks the first input parameter value, if the first input parameter value is not null then it will return the second parameter.Otherwise,it will return the third input parameter value. It is an extended version of the NVL.

According to oracle data type, first expression will be any data type whereas the second and third expression will be any data type except long.If the second and third expression are having character or numeric data type, then there will be possiblity of implict conversion. If the datatype does not undergo implict conversion,then an error is thrown out during the result.

If the first expression is having character datatype, then the second expression will convert its datatype as character through implict conversion and return varchar2 in the character set.

If the first expression is having numeric datatype, then the second expression will convert its datatype on the basis of high precedence of datatype through implict conversion and will return high precedence data type.

NVL2(expr1,expr2,expr3)

NVL2 will return the second and third expression value depending on the value of first expression.If the first expression value is null,then it will return the value of third expression.If the first expression value is not null,then it will return the second expression value.

If the expr1 is not null then it will display the expr2 as output. If the expr1 is null then it will display the expr3 as output.This function will hold the three input values.If we try to provide more than three input values,it will provide an error.It will be returning the value after not null expression.

•    SELECT NVL2('X','Y','Z') AS OUTPUT FROM DUAL;

 

       

• SELECT NVL2(NULL,'Y','Z') AS OUTPUT FROM DUAL;

• SELECT NVL2(NULL,'Y','NULL') AS OUTPUT FROM DUAL;

• SELECT NVL2('X',NULL,'Z') AS OUTPUTFROM DUAL;

• select ename, sal,comm,NVL2(comm,'SAL+COMM','SAL') from emp;

• SELECT NVL2(1,2,3,4) AS OUTPUT from dual;

• SELECT NVL2(6.8,5.7,4.6) AS OUTPUT from dual;

NULLIF:

It is said to be SQL general function.

It will compare the first input parameter value with the second parameter value, if both the first input and second input parameter values are same,then,it will return as NULL as value.Otherwise,it will return first input parameter as the value.

NULLIF(expr1,expr2)

•                      SELECT NULLIF('X','X') AS OUTPUTFROM DUAL;

• SELECT NULLIF('X','Y') AS OUTPUT FROM DUAL;

• SELECT NULLIF(NULL,'X') AS OUTPUT FROM DUAL;

• SELECT NULLIF('NULL','X') AS OUTPUT FROM DUAL;

• SELECT LENGTH(ENAME) "expr1",LENGTH(JOB) "expr2",NULLIF(LENGTH(ENAME),LENGTH(JOB)) OUTPUT from emp;
  
  
  

COALESCE:

It is said to be SQL general function.

It will return the first input parameter which is not null in the given input parameters.

COALESCE (expr1,expr2,expr3,...exprn)

It checks the first expression which is not null then returns the first expression as output.Otherwise,it will return another expression in the order list which is not null. Below are the examples provided with the help of screenshot to understand the concept better. It will return the first not null value in the order list. Biggest advantage is that it can take multiple alternate values for an evaluation. It can accept n number of parameters/expression of input values.

It is used in the previous versions like oracle 9i, oracle 10g, oracle 11g and oracle 12c.It is similar and equivalent to if then else statement. It is comparing each and every expression one by one.

IF expression1 is not null THEN

OUTPUT := X;

ELSEIF expression2 is not null THEN

OUTPUT:=Y;

ELSEIF expression3 is not null THEN

OUTPUT:=Z;

ELSEIF expression4 is not null THEN

OUTPUT:=A;

ELSEIF expression5 is not null THEN

OUTPUT:=B;

ELSE

OUTPUT:=NULL:

END IF;

It is just like the case statement .

select choice,coalesce(Choice1,choice2,choice 3,choice 4) as output from dual;

select choice, case when choice1 is not null

then choice='Choice1',

case when choice2 is not null

then choice='Choice2',

case when choice3 is not null

then choice='Choice3',

•                                    SELECT COALESCE('X','Y','Z','A','B') FROM DUAL;

• SELECT COALESCE('NULL','Y','Z','A','B') FROM DUAL;

• SELECT COALESCE(NULL,NULL,'Z','A','B') FROM DUAL;

• SELECT COALESCE(NULL,NULL,NULL,'A','B') FROM DUAL;

• SELECT COALESCE(NULL,NULL,NULL,NULL,'B') FROM DUAL;

• select ename,COALESCE(comm,sal,10)comm from emp order by comm;