CASE .. WHEN expression in Oracle SQL
Mastering CASE WHEN Expressions in Oracle SQL for Conditional Logic

Explore the power and flexibility of Oracle SQL's CASE WHEN expression to implement conditional logic directly within your queries, enhancing data manipulation and reporting capabilities.
The CASE WHEN
expression in Oracle SQL is a powerful tool that allows you to introduce conditional logic directly into your SQL statements. Similar to IF-THEN-ELSE
constructs in procedural languages, CASE WHEN
enables you to return different values based on specified conditions. This is incredibly useful for data transformation, complex reporting, and dynamic column generation without needing to resort to PL/SQL or application-level logic.
Understanding the Two Forms of CASE WHEN
Oracle's CASE WHEN
expression comes in two primary forms: the simple CASE
expression and the searched CASE
expression. Each serves slightly different purposes and is suitable for different scenarios.
1. Simple CASE Expression
The simple CASE
expression compares a single expression against multiple possible values. It's concise and easy to read when you're checking for equality against a fixed set of values. If the expression matches a WHEN
value, the corresponding THEN
result is returned. If no match is found, the ELSE
result is returned (or NULL
if ELSE
is omitted).
SELECT
employee_id,
first_name,
job_id,
CASE job_id
WHEN 'IT_PROG' THEN 'IT Programmer'
WHEN 'SA_REP' THEN 'Sales Representative'
WHEN 'AD_VP' THEN 'Administration VP'
ELSE 'Other Job Role'
END AS job_title_description
FROM
employees;
Example of a simple CASE expression to map job IDs to descriptions.
ELSE
clause in a CASE
expression is optional, but it's good practice to include it to handle cases where none of the WHEN
conditions are met. If omitted and no WHEN
condition matches, the expression returns NULL
.2. Searched CASE Expression
The searched CASE
expression is more flexible, allowing you to specify different conditions for each WHEN
clause. These conditions can be complex, involving comparison operators (<
, >
, <=
, >=
, !=
), logical operators (AND
, OR
, NOT
), and even subqueries. This form is ideal when your conditions are not based on a single equality check but rather on a range of values or multiple criteria.
SELECT
product_name,
price,
CASE
WHEN price < 50 THEN 'Low Price'
WHEN price >= 50 AND price < 200 THEN 'Medium Price'
WHEN price >= 200 THEN 'High Price'
ELSE 'Price Not Available'
END AS price_category
FROM
products;
Example of a searched CASE expression to categorize products based on price ranges.
flowchart TD A[Start Query] --> B{Evaluate Product Price?} B -- "Price < 50" --> C[Category: Low Price] B -- "50 <= Price < 200" --> D[Category: Medium Price] B -- "Price >= 200" --> E[Category: High Price] B -- "No Price" --> F[Category: Price Not Available] C --> G[End Query] D --> G E --> G F --> G
Decision flow for categorizing product prices using a searched CASE expression.
Common Use Cases and Best Practices
The CASE WHEN
expression is incredibly versatile. Here are some common scenarios where it shines, along with best practices to ensure efficient and readable code.
CASE WHEN
expressions are evaluated sequentially. The first WHEN
condition that evaluates to TRUE
will have its THEN
clause executed, and subsequent WHEN
clauses will not be checked. This is important for ordering your conditions, especially in searched CASE
expressions.1. Data Transformation
Use CASE WHEN
to transform data values on the fly, such as converting numeric codes to descriptive strings or standardizing inconsistent data entries.
2. Conditional Aggregation
Implement conditional aggregation by using CASE WHEN
inside aggregate functions like SUM
, COUNT
, or AVG
to count or sum specific subsets of data. For example, SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END)
.
3. Dynamic Ordering
Control the order of results dynamically using CASE WHEN
in the ORDER BY
clause. This allows you to sort by different columns or in different directions based on a parameter or condition.
4. Reporting and Pivoting
Generate complex reports or pivot data by creating new columns based on conditional logic. This is often used in conjunction with aggregate functions to create cross-tab reports.
SELECT
department_id,
SUM(CASE WHEN job_id = 'IT_PROG' THEN salary ELSE 0 END) AS it_prog_salary,
SUM(CASE WHEN job_id = 'SA_REP' THEN salary ELSE 0 END) AS sales_rep_salary,
SUM(salary) AS total_department_salary
FROM
employees
GROUP BY
department_id
ORDER BY
department_id;
Conditional aggregation example to sum salaries by specific job roles within departments.
CASE WHEN
expressions with too many WHEN
clauses, as they can become difficult to read and maintain. Consider breaking down complex logic into subqueries or views if necessary.