SQL NVL Function

The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE. The following example uses NVL2 to produce the same results as the DECODE:

SELECT lname, NVL2(manager_emp_id, ‘WORKER BEE’, ‘HEAD HONCHO’) emp_type FROM employee;

LNAME          EMP_TYPE

SMITH            WORKER  BEE

ALLEN           WORKER  BEE

WARD            WORKER  BEE

JONES           WORKER  BEE

MARTIN          WORKER  BEE

BLAKE           WORKER  BEE

CLARK           WORKER  BEE

SCOTT           WORKER  BEE

KING               HEAD  HONCHO

TURNER        WORKER  BEE

ADAMS         WORKER  BEE

JAMES           WORKER  BEE

FORD              WORKER  BEE

MILLER          WORKER  BEE

NVL2 looks at the first expression, manager_emp_id in this case. If that expression evaluates to NULL, NVL2 returns the third expression. If the first expression is not NULL, NVL2 returns the second expression. Use NVL2 when you wish to specify alternate values to be returned for the case when an expression is NULL, and also for the case when an expression is not NULL.

The NVL function is most commonly used to substitute a default value when a column is NULL. Otherwise, the column value itself is returned. The next example shows the ID of each employee’s manager, but substitutes the word ‘NONE’ when no manager has been assigned (i.e., when manager_emp_id is NULL):

SELECT emp.lname employee, NVL(mgr.lname, ‘NONE’) manager FROM employee emp LEFT OUTER JOIN employee mgr ON emp.manager_emp_id = mgr.emp_id;

EMPLOYEE  MANAGER

FORD              JONES

SCOTT            JONES

JAMES            BLAKE

TURNER          BLAKE

MARTIN          BLAKE

WARD             BLAKE

ALLEN             BLAKE

MILLER           CLARK

ADAMS           SCOTT

CLARK            KING

BLAKE            KING

JONES            KING

SMITH            FORD

KING              NONE

Even though DECODE may be substituted for any NVL or NVL2 function, most people prefer to use NVL or NVL2 when checking to see if an expression is NULL, presumably because the intent is clearer. Hopefully, the next section will convince you to use CASE expressions whenever you are in need of if-then-else functionality. Then you won’t need to worry about which built-in function to use.