5.4 Conditional Expressions: CASE and DECODE

5.4 Conditional Expressions: CASE and DECODE

SQL is a declarative language designed to express what data to retrieve rather than how to procedurally fetch it. However, real-world reporting and data manipulation frequently require conditional logic—the SQL equivalent of IF-THEN-ELSE branching statements found in procedural languages.

Oracle SQL provides two primary mechanisms for implementing conditional expressions:

1. The CASE Expression: An ANSI SQL compliant, highly flexible expression supporting both equality matching and complex boolean range evaluations.
2. The DECODE Function: An Oracle-proprietary built-in function providing compact equality-based mapping with unique NULL-handling semantics.

---

The CASE Expression

The CASE expression complies with ANSI SQL standards and can be utilized anywhere a standard scalar expression is valid (e.g., SELECT lists, WHERE clauses, ORDER BY clauses, GROUP BY expressions, and HAVING filters).

Oracle supports two distinct forms of the CASE expression:

                                  +----------------------------+
                                  |      CASE EXPRESSION       |
                                  +----------------------------+
                                                 |
                   +-----------------------------+-----------------------------+
                   |                                                           |
                   v                                                           v
     +----------------------------+                              +----------------------------+
     |        SIMPLE CASE         |                              |       SEARCHED CASE        |
     | Evaluates exact equality   |                              | Evaluates independent      |
     | against a single selector. |                              | boolean conditions.        |
     +----------------------------+                              +----------------------------+

1. Simple CASE Expression

A Simple CASE expression evaluates a single test expression (the selector) against a sequence of discrete comparison values using strict equality (selector = comparison_expr):

CASE selector
    WHEN value_1 THEN result_1
    WHEN value_2 THEN result_2
    [WHEN value_N THEN result_N]
    [ELSE default_result]
END

Simple CASE Example:

SELECT employee_id, last_name, job_id,
       CASE job_id
           WHEN 'IT_PROG'  THEN 'Information Technology'
           WHEN 'SA_REP'   THEN 'Sales Representative'
           WHEN 'ST_CLERK' THEN 'Stock Clerk'
           ELSE 'Other Role'
       END AS job_category
FROM   employees;

Critical Exam Rule: Simple CASE CANNOT evaluate NULL values using WHEN NULL. Because Simple CASE tests equality (selector = NULL), the comparison yields UNKNOWN (falsy) for every row. To test for NULL, you must use a Searched CASE with IS NULL.

2. Searched CASE Expression

A Searched CASE expression does not use a selector; instead, each WHEN clause contains an independent boolean search condition. Oracle evaluates these conditions sequentially from top to bottom and returns the THEN result of the first condition that evaluates to TRUE:

CASE
    WHEN boolean_condition_1 THEN result_1
    WHEN boolean_condition_2 THEN result_2
    [WHEN boolean_condition_N THEN result_N]
    [ELSE default_result]
END

Searched CASE Example (Ranges, Compound Logic & NULL Testing):

SELECT employee_id, last_name, salary, commission_pct,
       CASE
           WHEN commission_pct IS NOT NULL THEN 'Commission Earner'
           WHEN salary >= 15000            THEN 'Executive Level'
           WHEN salary BETWEEN 8000 AND 14999 THEN 'Senior Staff'
           WHEN salary < 8000 AND department_id = 50 THEN 'Operations Regular'
           ELSE 'Standard Base'
       END AS compensation_tier
FROM   employees;

Structural Rules for CASE Expressions:

1. The Mandatory END Keyword: Every CASE expression must terminate with the END keyword. Omitting END results in an ORA-00905: missing keyword compilation error.
2. Optional ELSE Clause: The ELSE clause is optional. If ELSE is omitted and no WHEN condition evaluates to TRUE, the CASE expression returns NULL.
3. Datatype Consistency: All return expressions (result_1, result_2, ..., default_result) must share compatible datatypes. Oracle coerces subsequent return values to the datatype of the first THEN return expression (result_1).
4. Short-Circuit Evaluation: Oracle stops evaluating subsequent WHEN branches as soon as it encounters the first condition that evaluates to TRUE.

---

The DECODE Function

The DECODE function is an Oracle-proprietary SQL function that emulates IF-THEN-ELSE logic specifically for equality comparisons.

Syntax:

DECODE(expr, search_1, result_1 
           [, search_2, result_2, ...]
           [, default_result])

SELECT employee_id, last_name, department_id,
       DECODE(department_id, 10, 'Administration',
                             20, 'Marketing',
                             30, 'Purchasing',
                             40, 'Human Resources',
                                 'Other Department') AS dept_name
FROM   employees;

Special Semantic Rules for DECODE:

1. Pairwise Arguments: DECODE requires at least 3 arguments (expr, search_1, result_1). If arguments are not properly paired, Oracle raises ORA-00938: not enough arguments for function.
2. Optional Default: The final default_result argument is optional. If omitted and expr does not match any search value, DECODE returns NULL.
3. NULL Equality Semantics: In standard SQL and CASE expressions, NULL = NULL evaluates to UNKNOWN. In DECODE, Oracle considers two NULL values to be equal!

   -- DECODE matches NULL successfully:
   SELECT last_name, 
          DECODE(commission_pct, NULL, 'No Commission', 'Commissioned') AS comm_status
   FROM   employees;
   

4. Datatype Coercion in DECODE: The return datatype of DECODE is determined by the datatype of result_1:
   • If result_1 is character, subsequent results are converted to VARCHAR2.
   • If result_1 is numeric, subsequent results are converted to NUMBER.
   • If result_1 is NULL, the return datatype is VARCHAR2.

---

Comparative Matrix: CASE vs. DECODE

---

Side-by-Side SQL Rewrites: DECODE to Searched CASE

Understanding how to translate DECODE expressions into ANSI CASE expressions (and vice versa) is a heavily tested skill on the 1Z0-071 examination.

Rewrite 1: NULL Matching

-- DECODE with NULL matching:
SELECT DECODE(manager_id, NULL, 'Top Executive', 'Subordinate') FROM employees;

-- Equivalent Searched CASE:
SELECT CASE 
           WHEN manager_id IS NULL THEN 'Top Executive' 
           ELSE 'Subordinate' 
       END 
FROM employees;

Rewrite 2: Simulating Range Evaluations with SIGN()

Before CASE was introduced in Oracle 8i, developers used the SIGN() function inside DECODE to evaluate numeric inequalities:

-- Legacy DECODE simulating ranges using SIGN(salary - 10000):
-- SIGN() returns 1 if positive, -1 if negative, 0 if equal.
SELECT DECODE(SIGN(salary - 10000), 1, 'High Earner',
                                    0, 'Exact Target',
                                       'Standard Earner') AS tier
FROM employees;

-- Clean, modern Searched CASE rewrite:
SELECT CASE 
           WHEN salary > 10000 THEN 'High Earner'
           WHEN salary = 10000 THEN 'Exact Target'
           ELSE 'Standard Earner'
       END AS tier
FROM employees;

---

Advanced Pattern: Conditional Aggregation

CASE and DECODE can be nested inside aggregate functions to pivot or calculate conditional metrics in a single pass over a table:

-- Calculating department headcounts by compensation level in a single query:
SELECT department_id,
       COUNT(*) AS total_employees,
       COUNT(CASE WHEN salary >= 10000 THEN 1 END) AS high_earners,
       COUNT(CASE WHEN salary < 10000  THEN 1 END) AS standard_earners,
       SUM(DECODE(job_id, 'IT_PROG', salary, 0))   AS it_payroll
FROM   employees
GROUP BY department_id
ORDER BY department_id;

Exam Trap: In conditional COUNT(), remember that COUNT(expr) only tallies non-null values. By omitting ELSE in CASE WHEN salary >= 10000 THEN 1 END, rows where salary is below 10,000 evaluate to NULL, which COUNT ignores.