Course contentsShow
← Data Science
Lesson 1034 of 2,14526. Advanced SQLFree lesson

CASE in WHERE and HAVING Clauses

Use CASE expressions to implement conditional filtering logic in WHERE and HAVING clauses.

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.