Oracle interview question Difference between null functions | NVL vs NVL2 vs NULLIF vs COALESCE
difference between null functions| nvl,nvl2, nullif and coalesce in Oracle sql pl SQL Interview Questions and Answers.
“It is most frequent question asked for PLSQL Developer and Oracle production support interview questions and answers. Every company is asking this question."
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.
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 possibility of implicit conversion. If the datatype does not undergo implicit conversion, then an error is thrown out during the result.
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 possibility of implicit conversion. If the datatype does not undergo implicit conversion, then an error is thrown out during the result.
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)
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. It will return the first not null value in the order.
Biggest advantage is that it can take multiple alternate values for an evaluation.
It can accept n 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;
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;