CASE and DECODE in SQL

In Oracle SQL, CASE and DECODE are control-flow functions used to perform conditional logic within queries. They allow you to implement "if-then-else" logic to return different values based on specified conditions.

1. CASE Statement

The CASE statement is a flexible and readable conditional construct introduced in SQL92. It allows you to evaluate conditions and return corresponding values.

Syntax

Simple CASE


CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

expression: The value to be evaluated.
value1, value2: Values to compare the expression against.
result1, result2: Results returned if the condition matches.
default_result: Returned if no conditions match (optional).

Searched CASE


CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

condition1, condition2: Boolean conditions to evaluate.
This format is more flexible as it supports complex logical expressions.

Example

Simple CASE:

SELECT employee_id, 
       CASE department_id
           WHEN 10 THEN 'Sales'
           WHEN 20 THEN 'Marketing'
           WHEN 30 THEN 'Finance'
           ELSE 'Other'
       END AS department_name
FROM employees;

Searched CASE:

SELECT employee_id, 
       CASE 
           WHEN salary > 5000 THEN 'High'
           WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

2. DECODE Function

The DECODE function is Oracle-specific and provides a way to implement conditional logic. It is an older construct compared to CASE and is less readable but can achieve similar functionality.

Syntax


DECODE(expression, search1, result1, search2, result2, ..., default_result)

expression: The value to be compared.
search1, search2: Values to compare against the expression.
result1, result2: Results returned if the comparison matches.
default_result: Returned if no matches are found (optional).

Example with Gender Information:


SELECT emp_id, name, 
    DECODE(gender, 
           'M', 'Male', 
           'F', 'Female', 
           'Unspecified') AS gender_description
FROM employees;

Example of department_id


SELECT employee_id, 
       DECODE(department_id, 
              10, 'Sales', 
              20, 'Marketing', 
              30, 'Finance', 
              'Other') AS department_name
FROM employees;