Function : NVL2 and COALESCE

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.
The following query shows NVL2 in action.

SQL> SELECT * FROM null_test_tab ORDER BY id;

        ID COL1       COL2       COL3       COL4

---------- ---------- ---------- ---------- ----------

         1 ONE        TWO        THREE      FOUR

         2            TWO        THREE      FOUR

         3                       THREE      FOUR

         4                       THREE      THREE

4 rows selected.

SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

               ID OUTPUT

---------- ----------

                1 TWO

                2 THREE

                3 THREE

                4 THREE

4 rows selected.

SQL>

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.

SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

        ID OUTPUT

---------- ----------

         1 ONE

         2 TWO

         3 THREE

         4 THREE

4 rows selected.

SQL>