Difference between NVL NVL2 NULLIF and COALESCE functions

Learn about NULL value functions like NVL, NVL2, NULLIF and COALESCE functions with simple explanation and example queries.

NVL

• Replace NULL value with another value or expression

Syntax:

NVL(EXPR1,EXPR2).

• If expression1 is not NULL, then expression1 is returned. If expression1 is NULL then expression2 is returned. In general, the return type will be that of the first expression.

Query Examples

SELECT NVL('A','B') RESULT FROM DUAL;

RESULT
A

SELECT NVL(NULL,'B') RESULT FROM DUAL;

RESULT
B

SELECT NVL('A',NULL)RESULT FROM DUAL;

RESULT
A

SELECT NVL(NULL,NULL)RESULT FROM DUAL;

RESULT
NULL

SELECT STUDENT_NAME ,NVL(SALARY,0) SALARY FROM STUDENT_TBL

For the above query, if salary column contains null value , then it replace with value 0 when displaying the result.

NVL2

• decide which value to return, based on whether a specified expression is null or not.

Syntax

NVL2(expression1, expression2, expression3)

• Expression1 is the one that will be evaluated. If it is not null, then expression2 is returned. If it is null then expression3 is returned.
• The first expression in NVL2 is never returned, it is only used to determine whether expression2 must be returned, or expression3.

Query Examples

SELECT NVL2('A','B','C') RESULT FROM DUAL;

RESULT
B

SELECT NVL2(NULL,'B','C') RESULT FROM DUAL;

RESULT
C

SELECT NVL2('A','B',NULL) RESULT FROM DUAL;

RESULT
B

SELECT NVL2('A',NULL,'C') RESULT FROM DUAL;

RESULT
NULL

NULLIF

• Compares two expressions and returns null if they are equal,returns the first expression if they are not equal.

Syntax:

NULLIF(EXPR1,EXPR2).

Query Examples

SELECT NULLIF('A','A') RESULT FROM DUAL;

RESULT
NULL

SELECT NULLIF('A','B') RESULT FROM DUAL;

RESULT
A

SELECT NULLIF('A',NULL) RESULT FROM DUAL;

RESULT
A

SELECT NULLIF(NULL,'A') RESULT FROM DUAL;

Result

ORA-00932: inconsistent datatypes: expected – got CHAR
00932. 00000 – “inconsistent datatypes: expected %s got %s”

Note:

• First expression should not allow to take a NULL value in  NULLIF function.

COALESCE

• Return first not null expression in the expression list.
• Accept any number of expressions or parameters (at least 2) and will return the first of them that is not null (evaluating from left to right). If all of them are null, then it returns null.

Syntax:

SELECT COALESCE(expr1,expr2,expr3,expr4,....exprn) FROM DUAL;

Query Examples

SELECT COALESCE('A','B','C','D')RESULT FROM DUAL;

RESULT
A

SELECT COALESCE(NULL,'B','C','D') RESULT FROM DUAL;

RESULT
B

SELECT COALESCE(NULL,NULL,'C','D') RESULT FROM DUAL;

RESULT
C

SELECT COALESCE(NULL,NULL,NULL,NULL)RESULT FROM DUAL;

RESULT
NULL