Oracle NVL Function

Summary: in this tutorial, you will learn how to use the Oracle NVL() function to substitute null with a more meaningful alternative.

Introduction to Oracle NVL() function

The Oracle NVL() function allows you to replace null with a more meaningful alternative in the results of a query.

The following shows the syntax of the NVL() function:

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

The NVL() function accepts two arguments. If e1 evaluates to null, then NVL() function returns e2. If e1 evaluates to non-null, the NVL() function returns e1.

The two arguments e1 and e2 can have the same or different data types. If their data types are different, Oracle implicit converts one to the other according to the following rules:

  • If the data type of e1 is character, Oracle converts e2 to the data type of e1 before comparing them with null and returns VARCHAR2 in the character set of e1.
  • If the data type of e1 is numeric, Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
  • If Oracle cannot implicitly convert one data type to the other, then it issues an error.

Oracle NVL() function examples

The following example returns 100 because the first argument is not null.

SELECT NVL(100,200) FROM dual;
Code language: SQL (Structured Query Language) (sql)

The following example returns N/A because the first argument is null:

SELECT NVL(NULL, 'N/A') FROM dual;
Code language: SQL (Structured Query Language) (sql)

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

Orders & Employees Tables

The following query returns order id and the salesman assigned to each sales order.

SELECT order_id, NVL(first_name, 'Not Assigned') FROM orders LEFT JOIN employees ON employee_id = salesman_id WHERE EXTRACT(YEAR FROM order_date) = 2016 ORDER BY order_date;
Code language: SQL (Structured Query Language) (sql)
Oracle NVL function example

In this example, we retrieved all sales order in 2016. If a sales order did not have a value in the salesman_id column, then the first_name is null according to the LEFT JOIN operation. The NVL() function returned the first name of salesman if there was a salesman assigned to the sales order, otherwise, it returned the literal string Not Assigned.

Oracle NVL() and CASE expression

The NVL() function is similar to the CASE expression when it comes to test a value for NULL .

The following function call:

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

is equivalent to

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

You can use the CASE expression to rewrite the query that returns order id and salesman as follows:

SELECT order_id, CASE WHEN first_name IS NOT NULL THEN first_name ELSE 'Not Assigned' END FROM orders LEFT JOIN employees ON employee_id = salesman_id WHERE EXTRACT(YEAR FROM order_date) = 2016 ORDER BY order_date;
Code language: SQL (Structured Query Language) (sql)

Oracle NVL() vs. COALESCE()

The COALESCE() function is a generalization of the NVL() function.

The following function:

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

returns the same result as

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

However, the COALESCE() function evaluates its argument in order and stops evaluation when it can determine the result i.e., when it can find the first non-NULL argument. This feature is known as short-circuit evaluation. In contrast, the NVL() function evaluates all of its arguments to determine the result.

In this tutorial, you have learned how to use the Oracle NVL() function to substitute null with more meaningful information.

Was this tutorial helpful?