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;

When to Use

Use CASE for:

• Complex conditions.
• Better readability and maintainability.
• Portability across different SQL databases.

Use DECODE for:

• Simpler, equality-based checks in Oracle-specific scenarios.
• When performance optimization for older systems is critical.

By understanding these constructs, you can enhance your SQL queries to handle conditional logic effectively.