NVL() Function

The NVL() function in Oracle is used to replace NULL values with a specified default value. It is widely used for handling missing data, ensuring calculations don’t fail, and improving query readability.

Syntax

NVL(expr1, expr2)

expr1 – The expression to check for NULL.
expr2 – The value to return if expr1 is NULL.

Behavior:

If expr1 is not null, NVL returns expr1.
If expr1 is null, NVL returns expr2.

Note: Both expr1 and expr2 must be of compatible data types.

Basic Example

SELECT NVL(NULL, 'Hello') AS result FROM dual;

Output:

RESULT
Hello

Explanation: Since the first argument is NULL, it returns the second argument 'Hello'.

Common Use Cases

1. Replace NULL with Default Value in SELECT

SELECT employee_id, salary, NVL(commission_pct, 0) AS commission
FROM employees;

If commission_pct is NULL, it is replaced by 0.
Useful for calculations where NULL would break arithmetic operations.

2. Use in Calculations

SELECT employee_id, salary, NVL(commission_pct, 0) * salary AS total_pay
FROM employees;

Ensures that multiplying by NULL doesn’t return NULL.
Replaces NULL with 0 for correct total salary computation.

3. Concatenate Strings Safely

SELECT first_name || ' ' || NVL(last_name, 'N/A') AS full_name
FROM employees;

If last_name is NULL, it uses 'N/A'.
Prevents concatenation from returning NULL.

4. Use in WHERE Clause

SELECT *
FROM employees
WHERE NVL(department_id, 0) = 10;

Replaces NULL in department_id with 0 to include in filtering conditions.

Important Notes

NVL works with exactly 2 arguments.
The data types of both arguments must be compatible.
- Example: NVL(number_col, ‘abc’) → Error
NVL is Oracle-specific and not ANSI standard. For ANSI-compliant code, use COALESCE.

Example with Employees Table

CREATE TABLE employees (
  employee_id NUMBER,
  name VARCHAR2(50),
  salary NUMBER,
  commission_pct NUMBER
);

INSERT INTO employees VALUES (1, 'Alice', 5000, NULL);
INSERT INTO employees VALUES (2, 'Bob', 7000, 0.10);
INSERT INTO employees VALUES (3, 'Charlie', 6000, NULL);
COMMIT;

Query: Replace NULL commission with 0

SELECT name, salary, NVL(commission_pct, 0) AS commission
FROM employees;