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() 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 types 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

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:

Orders & Employees Tables

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)
Oracle NVL2 example

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)
Oracle NVL2 example 2

In this example, we used the NVL2() function to implement a logic that if the commission IS NOT NULL, then the total compensations 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.

Was this tutorial helpful?