Oracle NVL2 Function

Summary: in this tutorial, you will learn how to use the Oracle NVL2() function to substitute NULL with different values.

Oracle NVL2() function overview #

In Oracle, the NVL2() function allows you to return different values depending on whether an expression is NULL or not

Here’s the syntax of the NVL2 function:

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

• If e1 is not NULL, NVL2 returns e2.
• If e1 is NULL, NVL2 returns 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.

Using the Oracle NVL2() function with a numeric data type example #

The following statement uses NVL2 function to return 2 because the first argument is NULL:

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

Try it

Output:

    RESULT
----------
         2

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') -- 'ABC'
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

NVL
---
ABC

Using 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);Code language: SQL (Structured Query Language) (sql)

Third, calculate the total compensation 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)

Output:

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
ENDCode 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 return different values depending on whether an expression is NULL or not.