CASE in WHERE and HAVING Clauses
What you'll learn: How to use CASE expressions to create flexible, conditional filtering logic directly in your WHERE and HAVING clauses.
The Core Idea
While you've already seen CASE expressions transform data in SELECT statements and aggregate functions, they can also power conditional filtering. By embedding CASE logic in WHERE or HAVING clauses, you can build dynamic filters that change behavior based on row values—essentially creating "if-then" rules that determine which rows make it through your filter.
Think of it like a bouncer at a club who applies different entry rules depending on the night: "On Fridays, check age > 21; on Saturdays, check VIP status." Your WHERE clause can enforce different conditions for different scenarios in a single query.
How It Works
In WHERE clauses, CASE returns a value (often a boolean-like 1/0) that you then compare to determine filtering:
WHERE CASE
WHEN category = 'Premium' THEN price > 100
WHEN category = 'Standard' THEN price > 50
ELSE price > 20
END
In HAVING clauses, you filter aggregated results conditionally:
HAVING CASE
WHEN department = 'Sales' THEN COUNT(*) >= 10
ELSE COUNT(*) >= 5
END
This lets you apply different thresholds to different groups without running multiple queries or using complex subqueries.
Why Use CASE for Filtering?
Instead of writing multiple queries with different WHERE conditions, or chaining endless OR statements, CASE lets you consolidate logic into one readable expression. It's particularly powerful when your filtering criteria depend on the data itself.
Key Takeaway: CASE expressions in WHERE and HAVING clauses enable conditional filtering logic, allowing different rows or groups to be evaluated against different criteria within a single query.