Oracle NVL2 Function

Summary: in this tutorial, you will learn how to use the Oracle NVL2() function to substitute a null value with different options.

Oracle NVL2() function overview

The Oracle NVL2() function is an extension of the NVL() function with different options based on whether a NULL value exists.

The Oracle NVL2() function accepts three arguments. If the first argument is not null, then it returns the second argument. In case the second argument is null, then it returns the third argument.

The following illustrates the syntax of the Oracle NVL2() function:

NVL2(e1,e2,e3)Code language: SQL (Structured Query Language) (sql)

In this syntax, the first argument e1 can be a value of any data type. The second and third arguments can be values of any data type except LONG.

In case the data types of e2 and e3 are different:

• If e2 is numeric, Oracle implicitly converts either e2 or e3 to the data type of the higher precedence and returns a value of that data type.
• If e2 is character data, Oracle converts e3 to the data type of e2 before comparing them and returns VARCHAR2 in the character set of e2. Oracle does not carry this conversion in case e3 is a null constant because it is not necessary.

The following flowchart illustrates how the Oracle NVL2() function works.

Oracle NVL2() function examples

Let’s take some examples of using the Oracle NVL2() function to understand how it works.

A) Oracle NVL2() function with numeric data type example

The following statement returns two because the first argument is null.

SELECT
  NVL2(NULL, 1, 2) -- 2
FROM
  dual; 
Code language: SQL (Structured Query Language) (sql)

B) Oracle NVL2() function with character data type example

The following example returns the second argument which is the ABC string because the first argument is not null.

SELECT
  NVL2(1, 'ABC', 'XYZ')
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

C) Oracle NVL2() function with orders example

See the following employees and orders tables from the sample database:

The following query retrieves order id, order date, and the name of the sales employee of sales orders in 2017:

SELECT
  order_id,
  order_date,
  nvl2(first_name, first_name  || ' '  || last_name, 'Not assigned') salesman
FROM
  orders
LEFT JOIN employees
ON
  employee_id = salesman_id
WHERE
  extract(YEAR FROM order_date) = 2017
ORDER BY
  order_date DESC;
Code language: SQL (Structured Query Language) (sql)

In this example, the NVL2() function checks if the first name is not null, then it returns the full name of the sales employee assigned to the sales order. Otherwise, it returns a literal string Not assigned.

D) Oracle NVL2() function with expression example

First, the following statement creates a compensation table that stores employee’s salary and commission:

CREATE  TABLE compensations (
    employee_id    NUMBER,
    effective_date DATE,
    salary         NUMBER NOT NULL,
    commission      NUMBER,
    PRIMARY KEY (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, we insert some sample data into the compensations table for the demonstration:

INSERT INTO compensations (employee_id, effective_date, salary, commission)
VALUES(1,DATE '2017-01-01',100000, NULL);

INSERT INTO compensations (employee_id, effective_date, salary, commission)
VALUES(51,DATE '2017-01-01',100000, 20000);

INSERT INTO compensations (employee_id, effective_date, salary, commission)
VALUES(52,DATE '2017-01-01',100000, 10000);

INSERT INTO compensations (employee_id, effective_date, salary, commission)
VALUES(81,DATE '2017-01-01',700000, NULL);

INSERT INTO compensations (employee_id, effective_date, salary, commission)
VALUES(81,DATE '2017-01-01',700000, NULL);
Code language: SQL (Structured Query Language) (sql)

The following query calculates the total compensations of employees from the beginning of 2017:

SELECT
  employee_id,
  NVL2(commission, salary + commission, salary)
FROM
  compensations
WHERE
  effective_date >= 2017-01-01';
Code language: SQL (Structured Query Language) (sql)

In this example, we used the NVL2() function to implement a logic that if the commission IS NOT NULL, then the total compensation is just salary. In case the commission IS NULL, calculate the full payment as salary plus commission.

Oracle NVL2() function and CASE expression

The NVL2() function is logically equivalent to the following CASE expression:

CASE
WHEN e1 IS NOT NULL THEN
  e2
ELSE
  e3
END
Code language: SQL (Structured Query Language) (sql)

As you can see, the NVL2() function is more concise and less verbose.

In this tutorial, you have learned how to use the Oracle NVL2() function to substitute a null value with different options.