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

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:

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)

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