Oracle DECODE Function: A Simple Guide

The DECODE function in Oracle SQL is a powerful and concise way to write conditional logic (like an IF...THEN...ELSE statement) directly within your SQL query.

It compares a value to a list of potential matches. If it finds a match, it returns a corresponding result. If no match is found, it returns a default value. This is a very common function, though in modern SQL, the CASE expression is often preferred for its readability.

What is the DECODE Function in Oracle?

The DECODE(expr, search, result, search, result, ..., default) function works like a series of IF checks:

• IF expr equals search1 THEN return result1.
• ELSE IF expr equals search2 THEN return result2.
• ...
• ELSE return default.

This is extremely useful for translating codes, IDs, or statuses into human-readable text directly in your SELECT statement.

Key Feature: Unlike most Oracle comparisons, DECODE considers two NULL values to be equivalent. So, if expr is NULL, it can be matched to a search value that is also NULL.

DECODE Function Syntax

The syntax for DECODE is a list of pairs, followed by an optional default:

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

Let's break that down:

• expr: The value or column you want to test (e.g., status_code, department_id).
• search1: The first value you want to compare against expr.
• result1: The value to return if expr equals search1.
• [search2, result2, ...] (Optional): You can add as many pairs as you need.
• [default] (Optional): The value to return if expr does not match any of the search values. If you omit default, the function will return NULL when no match is found.

Oracle DECODE Function Examples

Here are two practical examples of how to use DECODE.

Example 1: Translating a Code to a String with DECODE

This is the most common use case. Imagine you have an inventories table with a warehouse_id (a number), and you want to display the city name (a string) in your report.

Query:

-- This query checks the warehouse_id and returns a matching city name.
-- If no match is found (like for ID 99), it returns 'Unknown'.
WITH inventories AS (
  SELECT 1 AS warehouse_id FROM DUAL UNION ALL
  SELECT 2 AS warehouse_id FROM DUAL UNION ALL
  SELECT 3 AS warehouse_id FROM DUAL UNION ALL
  SELECT 99 AS warehouse_id FROM DUAL
)
SELECT 
  warehouse_id,
  DECODE(warehouse_id, 1, 'Southlake',
                       2, 'San Francisco',
                       3, 'New Jersey',
                          'Unknown') AS "Location"
FROM inventories;

Result:

WAREHOUSE_ID Location
------------ ---------------
           1 Southlake
           2 San Francisco
           3 New Jersey
          99 Unknown

Example 2: Handling NULL Values with DECODE

This example shows how DECODE can treat NULL as a value to be matched. Let's create a report that says 'On Commission' or 'Salary Only' based on the commission_pct column.

Query:

SELECT 
  last_name,
  DECODE(commission_pct, NULL, 'Salary Only', 
                             'On Commission') AS "Compensation_Type"
FROM 
  employees
WHERE 
  last_name LIKE 'B%';

Result: (The function correctly identifies the NULL values and returns 'Salary Only'.)

LAST_NAME                 Compensation_Type
------------------------- -----------------
Baer                      Salary Only
Baida                     Salary Only
Banda                     On Commission
Bates                     On Commission
Bell                      Salary Only
Bernstein                 On Commission
...