A searched CASE evaluates Boolean conditions in sequence and returns the result associated with the first condition that evaluates to true.
Searched CASE is more flexible than simple CASE because each WHEN can contain a different condition.
Classify employees based on salary and department.
SELECT
EmployeeName,
Department,
Salary,
CASE
WHEN Department = 'IT' AND Salary >= 80000 THEN 'Senior IT'
WHEN Salary >= 80000 THEN 'High Salary'
WHEN Salary >= 50000 THEN 'Medium Salary'
ELSE 'Low Salary'
END AS Category
FROM Employees;It is useful for complex business rules, risk classification, performance bands, customer segmentation, and conditional reporting.
- Searched CASE evaluates conditions.
- Each WHEN can contain a different condition.
- Conditions are evaluated in order.
- The first true condition determines the result.
- ELSE handles unmatched rows.
What is a searched CASE?
A searched CASE evaluates independent Boolean conditions in sequence.
Which WHEN result is returned when multiple conditions are true?
The result associated with the first true WHEN condition is returned.