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 #

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

Here’s the syntax of the NVL2 function:

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

The NVL2() function accepts three arguments. If the first argument (e1) is not null, then it returns the second argument (e2).

If the second argument (e2) is NULL, then it returns the third argument (e3).

The e1 can be a value of any data type. The e2 and e3 can be values of any data type except LONG.

If the data types of e2 and e3 are different:

• If e2 is numeric, NVL2 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, NVL2 converts e3 to the data type of e2 before comparing them and returns VARCHAR2 in the character set of e2. NVL2 does not carry this conversion if 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.

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)

Try it

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)

Oracle NVL2() function with table data #

Suppose we have 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.

Using Oracle NVL2() function with expression #

First, create a new table called compensation 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, 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)

Third, calculate 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 use the NVL2() function to implement the following business logic:

• If the commission IS NOT NULL, then the total compensation is salary only.
• If 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)

The code indicates that the NVL2() function is more concise and less verbose.

Summary #

• Use the Oracle NVL2() function to substitute NULL with different options.