Simple CASE vs Searched CASE
You'll learn: The two flavors of CASE expressions—simple CASE for straightforward equality checks and searched CASE for complex logical conditions.
Understanding the Two CASE Styles
In SQL, CASE expressions come in two forms that solve different problems:
Simple CASE works like a switch statement in programming. You specify one column or expression once, then test it against different values. It's compact and readable when you're checking column = value1, column = value2, etc.
Searched CASE is more flexible—each WHEN clause can contain any boolean condition. Use this when you need comparison operators (>, <, !=), multiple columns, OR/AND logic, or anything beyond simple equality.
Simple CASE Syntax
CASE status
WHEN 'active' THEN 'Active User'
WHEN 'pending' THEN 'Awaiting Approval'
WHEN 'suspended' THEN 'Account Suspended'
ELSE 'Unknown'
END
This checks if status equals each value—clean and straightforward.
Searched CASE Syntax
CASE
WHEN age < 18 THEN 'Minor'
WHEN age >= 18 AND age < 65 THEN 'Adult'
WHEN age >= 65 THEN 'Senior'
ELSE 'Unknown'
END
Notice there's no column after CASE—each WHEN is a complete boolean expression.
When to Choose Which
Use simple CASE when checking one column against specific values (equality only).
Use searched CASE when you need:
- Range checks (age > 18)
- Multiple conditions combined with AND/OR
- Different columns in different conditions
- Complex logic
Think of simple CASE as a shortcut for the specific scenario where searched CASE would be repetitive.
Key Takeaway: Simple CASE is elegant for equality checks on one expression; searched CASE handles any complex conditional logic you need.