A searched CASE evaluates Boolean conditions in order and returns the result associated with the first condition that evaluates to true.
Business rules often depend on ranges or combinations of conditions rather than exact values. Searched CASE handles these scenarios.
Classify customers based on their total purchase amount.
SELECT
CustomerName,
TotalPurchase,
CASE
WHEN TotalPurchase >= 100000 THEN 'Platinum'
WHEN TotalPurchase >= 50000 THEN 'Gold'
WHEN TotalPurchase >= 10000 THEN 'Silver'
ELSE 'Bronze'
END AS CustomerCategory
FROM Customers;Searched CASE is useful for risk bands, customer segmentation, salary ranges, performance ratings, and business classifications.
- Searched CASE evaluates conditions.
- Conditions are evaluated from top to bottom.
- The first true condition determines the result.
- It supports ranges and complex rules.
- ELSE handles remaining records.
What is a searched CASE?
A searched CASE evaluates independent conditions and returns the result for the first condition that is true.
Why does the order of WHEN conditions matter?
CASE stops at the first matching condition, so conditions must be ordered carefully to produce the intended result.