Oracle NVL Function: A Simple Guide

The NVL function in Oracle SQL is one of the most common and essential functions for handling NULL values. A NULL value in a database means "unknown" or "empty," and it can cause problems in calculations or reports.

The NVL function provides a simple way to substitute a NULL value with a different, "default" value (like 0 or 'N/A').

What is the NVL Function in Oracle?

The NVL(expr1, expr2) function checks the first expression (expr1).

• If expr1 is not NULL, the function returns expr1.
• If expr1 is NULL, the function returns expr2.

This is a simple "if null, then what?" function. It is a simpler, two-argument version of the COALESCE function.

This is extremely useful for:

• Replacing NULL values with 0 before performing math to avoid errors.
• Replacing NULL strings with a default value like 'Not Applicable' for cleaner reports.

NVL Function Syntax

The syntax for NVL is:

NVL(expr1, expr2)

Let's break that down:

• expr1: The value or column to check for NULL (e.g., commission_pct).
• expr2: The "default" value to return if expr1 is NULL (e.g., 0 or 'Not Applicable').

The data types of expr1 and expr2 must be compatible. For example, if expr1 is a NUMBER, expr2 should also be a NUMBER (or a string that can be auto-converted to one).

Oracle NVL Function Examples

Here are two practical examples of how to use NVL.

Example 1: Replacing NULL in a Calculation with NVL

This is the most critical use of NVL. If you perform math on a NULL value, the result is always NULL. (e.g., 100 + NULL = NULL).

Let's use NVL to treat a NULL commission as 0 before adding it to a salary.

Query:

-- We'll simulate a table 'employees'
WITH employees AS (
  SELECT 'Alice' AS name, 50000 AS salary, 5000 AS commission FROM DUAL UNION ALL
  SELECT 'Bob' AS name, 60000 AS salary, NULL AS commission FROM DUAL
)
SELECT
  name,
  salary,
  commission,
  -- This will fail for Bob:
  salary + commission AS "Total_Pay_Incorrect",
  -- This will work for Bob by replacing NULL with 0:
  salary + NVL(commission, 0) AS "Total_Pay_Correct"
FROM employees;

Result:

NAME    SALARY     COMMISSION Total_Pay_Incorrect Total_Pay_Correct
------- ---------- ---------- ------------------- -----------------
Alice        50000       5000               55000             55000
Bob          60000     (NULL)              (NULL)             60000

Example 2: Replacing a NULL String with Text using NVL

This example shows how to clean up a report by replacing NULL values with a more user-friendly string.

Query: (This query will check the commission_pct column. If it's NULL, it will return 'Not Applicable'.)

SELECT
  last_name,
  -- We must use TO_CHAR to make the number (commission_pct)
  -- compatible with the string ('Not Applicable').
  NVL(TO_CHAR(commission_pct), 'Not Applicable') AS "Commission"
FROM
  employees
WHERE
  last_name LIKE 'B%';

Result: (The NULL values in the commission column are replaced with 'Not Applicable'.)

LAST_NAME                 Commission
------------------------- ----------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
...