Oracle NVL2 Function: A Simple Guide

The NVL2 function in Oracle SQL is a powerful "if-then-else" function for handling NULL values. It's an extension of the basic NVL function.

NVL2 checks an expression. It returns one value if the expression is NOT NULL, and a different value if the expression IS NULL. This is very useful for conditional logic.

What is the NVL2 Function in Oracle?

The NVL2(expr1, expr2, expr3) function is a concise way to write a CASE statement.

It is logically identical to this:

CASE 
  WHEN expr1 IS NOT NULL THEN expr2
  ELSE expr3 
END

This is extremely useful for:

• Calculating a value only if a component is not null (e.g., salary + commission).
• Returning one text string if a value exists (e.g., 'Has Phone') and another if it's null (e.g., 'No Phone').

NVL2 Function Syntax

The syntax for NVL2 requires three arguments:

NVL2(expr1, expr2, expr3)

Let's break that down:

• expr1: The expression to check (e.g., commission_pct).
• expr2: The value to return if expr1 is NOT NULL.
• expr3: The value to return if expr1 is NULL.

The data types of expr2 and expr3 must be compatible (or convertible).

Oracle NVL2 Function Examples

Here are two practical examples of how to use NVL2.

Example 1: Calculating "Total Pay" with Commission using NVL2

This is the classic example for NVL2. You want to calculate an employee's total income. If they have a commission, the income is salary + (salary * commission_pct). If they do not have a commission (it's NULL), their income is just their salary.

Query:

SELECT 
  last_name, 
  salary, 
  commission_pct,
  NVL2(
    commission_pct,                       -- 1. The value to check
    salary + (salary * commission_pct),   -- 2. Return this if it's NOT NULL
    salary                                -- 3. Return this if it's NULL
  ) AS "Income"
FROM employees
WHERE last_name LIKE 'B%';

Result: (Notice how Banda's and Bernstein's income is calculated, while Baer's just defaults to their salary.)

LAST_NAME                     SALARY COMMISSION_PCT     Income
------------------------- ---------- -------------- ----------
Baer                           10000         (NULL)      10000
Baida                           2900         (NULL)       2900
Banda                           6200             .1       6820
Bates                           7300            .15       8395
Bell                            4000         (NULL)       4000
Bernstein                       9500            .25      11875
...

Example 2: Returning a Descriptive String using NVL2

This example shows how to use NVL2 to return a simple, human-readable status string based on whether a value is NULL or not.

Query:

-- We will simulate a 'contacts' table
WITH contacts AS (
  SELECT 'Alice' AS name, '555-1234' AS phone FROM DUAL UNION ALL
  SELECT 'Bob' AS name, NULL AS phone FROM DUAL
)
SELECT
  name,
  NVL2(
    phone,             -- 1. Check the phone column
    'Has Contact',     -- 2. Return this if it's NOT NULL
    'No Contact Info'  -- 3. Return this if it's NULL
  ) AS "Status"
FROM contacts;

Result:

NAME    Status
------- -----------------
Alice   Has Contact
Bob     No Contact Info