Oracle PL/SQL: NVL / NVL2 / COALESCE / CASE / IF / DECODE

Both NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences are: NVL accepts only 2 arguments whereas COALESCE can take multiple arguments. NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.

The function NVL2(expr1, expr2, expr3) examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned i.e. If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.

You should always use COALESCE instead of NVL, if parameter 2 of the NVL function is a function call or a SELECT statement.

The NVL function always evaluates both parameters before deciding which one to use. This can be harmful if parameter 2 is either a function call or a select statement, as it will be executed regardless of whether parameter 1 contains a NULL value or not. The COALESCE function does not have this drawback. 

--Bad

select nvl(dummy, funtional_call()) from dual;

--Good

select coalesce(dummy, funtional_call()) from dual;

Oracle CASE expression allows you to add if-else logic to SQL statements without having to call a procedure. The CASE expression evaluates a list of conditions and returns one of the multiple possible results.

Oracle CASE expression has two formats: the simple CASE expression and the searched CASEexpression.

Simple CASE expression

The simple CASE expression matches an expression to a list of simple expressions to determine the result.

The following illustrates the syntax of the simple CASE expression:

CASE e

    WHEN e1 THEN

          r1

    WHEN e2 THEN

          r2

    WHEN en THEN

          rn

    [ ELSE r_else ]

END

In this syntax, Oracle compares the input expression (e) to each comparison expression e1, e2, …, en.

If the input expression equals any comparison expression, the CASE expression returns the corresponding result expression (r).

If the input expression e does not match any comparison expression, the CASE expression returns the expression in the ELSE clause if the ELSE clause exists, otherwise it returns a null value.

Oracle uses short-circuit evaluation for the simple CASE expression. It means that Oracle evaluates each comparison expression (e1, e2, .. en) only before comparing one of them with the input expression (e). Oracle does not evaluate all comparison expressions before comparing any them with the expression (e). As the result, Oracle never evaluates a comparison expression if a previous one equals the input expression (e).

Searched CASE expression

The Oracle searched CASE expression evaluates a list of Boolean expressions to determine the result.

The searched CASE statement has the following syntax:

CASE

    WHEN e1 THEN r1

    [ WHEN e2 THEN r2]

    ...

    [ELSE

        r_else]

END

The searched CASE expression evaluates the Boolean expression (e1, e2, …) in each WHEN clause in the order that the Boolean expressions appear. It returns the result expression (r) of the first Boolean expression (e) that evaluates to true.

If no Boolean expression is true, then the CASE expression returns the result expression in the ELSEclause if an ELSE clause exists; otherwise, it returns a null value.

Like the simple CASE expression, Oracle also uses short-circuit evaluation for the searched CASEexpression. In other words, Oracle evaluates each Boolean condition to determine whether it is true, and never evaluates the next condition if the previous one is true.