Oracle DECODE and NVL Functions

Oracle AI Database 26ai supports several expressions and functions for transforming values inside a SQL statement. DECODE chooses a result by comparing one expression with a series of search values. NVL supplies a replacement when an expression is null. Both can make a query concise, but their compact syntax also makes datatype rules and implicit conversions easy to overlook.

These functions change the values returned by a query; they do not update the stored data. DECODE remains useful in established Oracle applications and in short equality mappings. Standard SQL CASE is usually clearer for new conditional logic, especially when conditions involve ranges or more than one expression. Similarly, COALESCE is a flexible alternative to NVL when several possible fallback values must be tested.

The general form of DECODE is:

DECODE(expr,
       search1, result1
       [, search2, result2]...
       [, default])

Oracle evaluates expr and compares it with each search value in order. The result paired with the first match is returned. If no search value matches, Oracle returns default; if the optional default is omitted, Oracle returns null. This is sequential equality matching, not a general-purpose test of arbitrary Boolean conditions.

Search expressions are evaluated only when Oracle reaches them, which gives DECODE short-circuit search evaluation. Oracle also treats two null values as equivalent inside DECODE, a notable difference from an ordinary SQL comparison such as NULL = NULL. The complete function can contain no more than 255 components, including the main expression, search-result pairs, and default value.

Datatype selection depends on the first pair. Before comparing values, Oracle converts expr and the other search values to the datatype of the first search value. Oracle converts the returned value to the datatype of the first result. If that first result is null or has character type, the function returns VARCHAR2. Keep search values compatible and make the first result representative of the intended output to avoid surprising conversions or conversion errors.

The following query classifies Alaska and Hawaii as noncontiguous states. Every other state receives the default label. The query selects only the required columns; a bare asterisk cannot be appended after another select-list expression.

SELECT state,
       DECODE(
         state,
         'HI', 'Noncontiguous US state',
         'AK', 'Noncontiguous US state',
               'Contiguous US state'
       ) AS location
FROM   customer;

STATE	LOCATION
NE	Contiguous US state
HI	Noncontiguous US state
CA	Contiguous US state
HI	Noncontiguous US state

For each row, Oracle first compares state with 'HI'. If that comparison fails, it compares the same value with 'AK'. When neither value matches, the final unpaired argument supplies the default. The alias location becomes the heading of the calculated column.

This query demonstrates the special null-equivalence rule inside DECODE:

SELECT DECODE(NULL, NULL, 'Match', 'No match') AS null_comparison
FROM   dual;

NULL_COMPARISON
Match

A simple CASE expression is the closest standard SQL equivalent for the state-code mapping. It compares one expression with several possible values and returns the result belonging to the first match:

CASE state
  WHEN 'HI' THEN 'Noncontiguous US state'
  WHEN 'AK' THEN 'Noncontiguous US state'
  ELSE 'Contiguous US state'
END

Expression	Best use	Condition style
DECODE	Compact Oracle-specific mappings and established code	Equality comparisons against one expression
Simple CASE	Portable mappings with explicit keywords	Equality comparisons against one expression
Searched CASE	Ranges, inequalities, null tests, and combined conditions	Independent Boolean conditions

Use searched CASE for a condition such as temperature > 32. DECODE cannot directly represent that inequality because it matches values for equality. CASE also communicates the conditional structure more explicitly to readers who do not work exclusively with Oracle SQL.

A search-result pair can return a measure for matching rows and zero for all other rows. An aggregate then adds only the measure associated with the selected category. This query creates January, February, and March sales columns for 2025:

SELECT SUM(DECODE(EXTRACT(MONTH FROM sales_date),
                  1, total_sale_amount,
                  0)) AS january,
       SUM(DECODE(EXTRACT(MONTH FROM sales_date),
                  2, total_sale_amount,
                  0)) AS february,
       SUM(DECODE(EXTRACT(MONTH FROM sales_date),
                  3, total_sale_amount,
                  0)) AS march
FROM   customer_sale
WHERE  sales_date >= DATE '2025-01-01'
AND    sales_date <  DATE '2026-01-01';

JANUARY	FEBRUARY	MARCH
0	20.47	108.03

For the January expression, rows from month 1 contribute total_sale_amount; all other rows contribute zero. The other expressions apply the same method to months 2 and 3. The half-open date range includes every instant in 2025 without applying a formatting function to the filtered column. That form is clear and allows an index on sales_date to remain useful when the optimizer considers an indexed access path.

Conditional aggregation with CASE can express the same report and is often easier to extend. Oracle's PIVOT clause is another option when rows must systematically become columns. Choose the form that makes the report's categories and maintenance requirements clearest.

The NVL function accepts two expressions:

NVL(expr1, expr2)

If expr1 is null, Oracle returns expr2. Otherwise, it returns expr1. This is useful in displayed reports and in arithmetic where a missing value must deliberately be treated as zero or another business-defined default. It does not change the original column or make the stored value non-null.

The two expressions must be compatible. When expr1 is character data, Oracle converts expr2 to that character datatype and returns VARCHAR2 in the character set of expr1. For numeric expressions, Oracle determines the argument with the highest numeric precedence, converts the other argument, and returns that numeric datatype. An invalid implicit conversion can raise an error, so explicit conversion is safer when the desired output datatype is not obvious.

The following query displays zero when a product has no package identifier. The original column remains in the result so the difference between the stored null and the calculated replacement is visible:

SELECT product_id,
       package_id,
       NVL(package_id, 0) AS effective_package_id
FROM   product
WHERE  product_id BETWEEN 2 AND 6
ORDER  BY product_id;

PRODUCT_ID	PACKAGE_ID	EFFECTIVE_PACKAGE_ID
2	NULL	0
3	NULL	0
4	21	21
5	NULL	0
6	20	20

Rows 2, 3, and 5 receive the replacement because package_id is null. Rows 4 and 6 retain their existing identifiers. A zero replacement is appropriate only if zero has the intended meaning in the report; it should not conceal the distinction between “unknown,” “not applicable,” and a genuine numeric zero when those states matter to the application.

To display a numeric value together with text, convert the number explicitly. This avoids relying on session-sensitive implicit number-to-character conversion:

SELECT NVL(
         TO_CHAR(commission_pct),
         'Not Applicable'
       ) AS commission
FROM   employees;

Expression	Behavior	Typical choice
NVL(a, b)	Returns b when a is null	Concise two-value handling in Oracle SQL
COALESCE(a, b, c)	Returns the first non-null expression and uses short-circuit evaluation	Several fallbacks or standard SQL portability
CASE	Returns a value for explicit conditions	Business rules that require more than a null test

COALESCE is a generalization of a two-argument null replacement because it can test multiple expressions in order. Its datatype rules are not identical to every possible NVL expression, so replacing one mechanically can change conversion behavior. Verify the datatypes and representative data whenever existing production SQL is refactored.

A replacement value should reflect the meaning of the data, not merely make a null disappear. For example, treating a missing unit price as zero allows an arithmetic expression to produce a number, but that number may falsely imply that an item is free. If a missing price indicates a data quality problem, preserving the null or reporting the incomplete row separately is more accurate.

SELECT product_id,
       quantity,
       unit_price,
       quantity * NVL(unit_price, 0) AS calculated_amount
FROM   order_item;

In this example, calculated_amount becomes zero when unit_price is null. That behavior is technically valid, but it should be adopted only when the business rule defines a missing price as zero. SQL cannot decide whether null means unknown, unavailable, not yet entered, or not applicable; the query author must choose the replacement deliberately.

Be equally careful when placing NVL around a column in a predicate. The following predicate intentionally treats a null status as 'OPEN', so it returns both explicit open rows and rows without a status:

WHERE NVL(status, 'OPEN') = 'OPEN'

A more explicit form makes the two accepted conditions visible:

WHERE status = 'OPEN'
   OR status IS NULL

The forms can express the same rule, but applying a function to an indexed column may affect whether a normal index can support the predicate. Oracle can use a suitable function-based index when that design is warranted. Review the execution plan and data distribution instead of assuming that shorter SQL is faster.

Oracle currently treats a zero-length character string as null. Consequently, NVL(character_column, 'Missing') also replaces an empty string stored or produced in a character context. It does not replace strings containing spaces. Use TRIM together with a deliberate null test when whitespace-only input must be classified as missing.

Mistake	Why it causes trouble	Better approach
Using DECODE for >, <, or compound tests	DECODE performs equality matching against one expression.	Use a searched CASE expression.
Mixing incompatible text and numbers in NVL	Implicit conversion can fail or produce a datatype that was not intended.	Use TO_CHAR or TO_NUMBER explicitly at the correct boundary.
Deeply nesting DECODE calls	The positional search-result pairs become difficult to review and modify.	Use CASE, a lookup table, or a join when the rule has many branches.
Replacing every null with zero	Zero and unknown can represent different business facts.	Choose a default only after defining what the null means.

• DECODE compares one expression with search values in order and returns the result belonging to the first match.
• An optional final argument is the default; without it, an unmatched DECODE returns null.
• DECODE treats two nulls as equivalent and uses short-circuit evaluation for its search expressions.
• Use simple CASE for readable equality mappings and searched CASE for ranges, inequalities, or combined conditions.
• NVL replaces a null result with one fallback value, subject to Oracle's datatype-conversion rules.
• COALESCE is useful when more than one fallback expression should be considered.
• Explicit conversion and business-appropriate defaults make null handling predictable instead of merely convenient.

Use the exercise to write queries that apply DECODE and NVL to sample data.

Decode NVL Functions - Exercise

Click the Exercise link below to write a query using DECODE and NVL.
Decode NVL Functions - Exercise
The next lesson concludes this module.