The difference between several functions of Oracle NVL,NVL2,COALESCE

1.NVL (EXP1,EXP2)

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null and then NVL returns expr2. If expr1 is isn't null, then NVL returnsexpr1.

The function is to handle null values in an expression: Assuming that the expression EXP1 is null, the function returns the value of the expression Exp2,

Assuming that the expression EXP1 is not a null value, the function returns the value of the expression Exp1.

2.NVL2 (EXP1,EXP2,EXP3)

NVL2lets determine the value returned by a query based on whether a specified expression was null or not NULL. Ifexpr1is isn't null, thenNVL2returnsexpr2. Ifexpr1is null and thenNVL2returnsexpr3.

This function is determined to return an expression when EXP1 is null and non-null values:

If EXP1 is a null value, the EXP3 is returned

assume that EXP1 is non-null and returns EXP2.

3.coalesce (EXP1,EXP2, ...)

COALESCEreturns the first non-null expr In the expression list. You must specify at least and expressions. If all occurrences expr of evaluate to NULL, then the function returns NULL.

The function returns the first non-null value in the full expression in parentheses, assuming that it is null, and returns a null value with a minimum of two expressions.

Example:

This article from "Angel Wings" blog, declined reprint!

The difference between several functions of Oracle NVL,NVL2,COALESCE