Choosing Between NVL, NVL2, COALESCE, and DECODE

The Null Handling Landscape

NULL handling is one of the most frequent sources of subtle bugs in Oracle SQL and PL/SQL. Oracle provides several functions for dealing with NULL values, and while they overlap in some areas, each has distinct behavior that makes it the right choice in specific situations. Understanding these differences will help you write cleaner, faster, and more predictable code.

NVL: The Simple Replacement

NVL(expr1, expr2) evaluates expr1. If it is NULL, NVL returns expr2. If it is not NULL, NVL returns expr1. This is the most commonly used null handling function and the one most developers reach for by default.

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

One important behavior to understand: NVL always evaluates both arguments. Even if expr1 is not NULL, Oracle still evaluates expr2. For simple literal values this is irrelevant, but if expr2 is an expensive subquery or function call, this eager evaluation can hurt performance.

NVL2: The Conditional Branch

NVL2(expr1, expr2, expr3) evaluates expr1. If it is NOT NULL, it returns expr2. If it IS NULL, it returns expr3. Think of it as a compact IF/THEN/ELSE based on nullity:

SELECT employee_id,
       NVL2(commission_pct,
            'Earns Commission',
            'No Commission') AS commission_status
  FROM employees;

NVL2 is particularly useful when you want to return different values based on whether a column is populated, not just substitute a default for NULL. Many developers overlook this function and write more verbose CASE expressions instead.

COALESCE: The Multi Argument Champion

COALESCE(expr1, expr2, ..., exprN) returns the first non NULL expression in the list. Unlike NVL, COALESCE accepts more than two arguments and uses short circuit evaluation. It stops evaluating as soon as it finds a non NULL value:

SELECT employee_id,
       COALESCE(override_email,
                work_email,
                personal_email,
                'no.email@example.com') AS contact_email
  FROM employees;

The short circuit behavior makes COALESCE more efficient than nested NVL calls when you have multiple fallback values, because it will not evaluate subsequent expressions once it finds a non NULL result. This matters when those expressions involve function calls or subqueries.

Another subtle difference: COALESCE follows ANSI SQL standard datatype resolution rules, while NVL uses Oracle’s own implicit conversion rules. In rare cases involving mixed datatypes, NVL and COALESCE may produce different results or different implicit conversions.

DECODE: The Legacy Powerhouse

DECODE is Oracle’s original conditional function, predating CASE expressions. While it can handle NULL comparisons (DECODE treats NULL = NULL as true, unlike standard SQL), it is generally considered less readable than CASE and is often used in legacy code:

SELECT employee_id,
       DECODE(status,
              'A', 'Active',
              'I', 'Inactive',
              'T', 'Terminated',
              NULL, 'Unknown',
              'Other') AS status_desc
  FROM employees;

DECODE’s unique ability to treat NULL as equal to NULL can be useful in specific situations, but for new code, a CASE expression is almost always more readable and maintainable.

Quick Decision Guide

Use NVL when you have one value and one fallback, and the fallback is inexpensive to evaluate. Use NVL2 when you need different return values for the NULL and non NULL cases. Use COALESCE when you have multiple fallback values or when short circuit evaluation matters for performance. Avoid DECODE for new code unless you specifically need its NULL equality behavior.

Performance Comparison

-- Benchmark: NVL with expensive fallback (evaluates subquery even when not needed)
SELECT NVL(cached_value, (SELECT compute_expensive_result(id) FROM dual))
  FROM large_table;

-- Better: COALESCE short circuits and skips the subquery when cached_value is not NULL
SELECT COALESCE(cached_value, (SELECT compute_expensive_result(id) FROM dual))
  FROM large_table;

In a table where 95% of rows have a non NULL cached_value, the COALESCE version will be dramatically faster because it avoids 95% of the expensive subquery evaluations that NVL would execute regardless.