Null Functions in Oracle SQL
What is a NULL Value? A field with a NULL value is a field with no value.
Null Functions in Oracle SQL
NVL
The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
NVL(expr1, expr2);
NVL2
The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.
NVL2 (expr1, expr2, expr3)
expr1 is the source value or expression that may contain null -
expr2 is the value returned if expr1 is not null
expr3 is the value returned if expr1 is null
if exp1 is null
then
expe3
else
exp2
COALESCE
The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.
COALESCE (expr_1, expr_2, ... expr_n)
CASE
WHEN e1 IS NOT NULL THEN
e1
ELSE
e2
e3
END
NULLIF (expr_1, expr_2)
The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. You cannot specify the literal NULL for first expression.
Difference
SELECT
COALESCE(1,NULL)
FROM
dual;
SELECT
NVL(1,NULL)
FROM
dual;