Oracle SQL – NVL and NVL2 Function

Last Updated on :October 23, 2019

NVL Function

NVL function allow you to check an expression whether it is NULL or not.  If the expression is NULL then NVL function substitutes the NULL value with another non-NULL value.

The NVL Function takes two parameter.

NVL (expr1, expr2) -
If expr1 is NULL then expr2 else expr1

Example

The following query list out employees and name of their managers. NVL function substitute with ‘NONE’ when no manager is assigned.

SELECT e.ename employee, NVL (m.ename, 'NONE') manager
  FROM emp e LEFT OUTER JOIN emp m ON e.mgr = m.empno;

Output

EMPLOYEE,MANAGER
"JONES","KING"
"CLARK","KING"
"BLAKE","KING"
"JAMES","BLAKE"
"TURNER","BLAKE"
"MARTIN","BLAKE"
"WARD","BLAKE"
"ALLEN","BLAKE"
"MILLER","CLARK"
"FORD","JONES"
"SCOTT","JONES"
"ADAMS","SCOTT"
"SMITH","FORD"
"KING","NONE"

NVL2 Function

NVL2 function is an extension of NVL function. It allows you to return one value when value of specified expression is NULL or another value when value of specified expression is NOT NULL.

NVL2 function takes three parameters

NVL2 (expr1, expr2, expr3) - 
If expr1 is NULL then expr3 else expr2

Example

The following query retrieves order_id and order status from ORDERS table. NVL2 function will look into order_status column value . if order_status is NULL it will return as ‘Cancelled’ otherwise it will return as ‘Completed’.

SELECT 
order_id, NVL2 (order_status, 'Completed', 'Cancelled') 
FROM ORDERS;