SQL NVL

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.