Oracle NVL() Function By Practical Examples

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 implicitly 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.

If both e1 and e2 are NULL, the NVL() function returns NULL.

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)

Try it

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)

Try it

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

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

SELECT order_id, NVL(first_name, 'Not Assigned') salesman 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)

Try it

In this example, we retrieved all sales orders in 2016. If a sales order does 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 the salesman if there was a salesman assigned to the sales order, otherwise, it returned the literal string Not Assigned.

Oracle NVL() function and CASE expression #

The NVL() function is similar to the CASE expression when it comes to testing 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 ENDCode 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)

Try it

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.

Summary #

• Use the Oracle NVL() function to substitute NULL with more meaningful information.

Send Cancel