CASE lets a query translate raw values into business categories, labels and conditional metrics. Learn searched and simple CASE, first-match behavior, ELSE handling and the common pattern used in reporting queries.
Key Takeaways at a Glance:
- •CASE provides IF-THEN-ELSE conditional logic directly inside SQL query expressions.
- •Searched CASE evaluates boolean conditions in order and returns the first TRUE result: CASE WHEN condition THEN result ... END.
- •If no condition matches and no ELSE clause is provided, the CASE expression returns NULL.
- •Conditional aggregation (SUM/COUNT with CASE) allows pivoting rows into columnar reporting metrics in a single pass.
In Simple Words (The Mental Model)
CASE is SQL's version of an if-else statement. As the database processes each row, it tests your conditions from top to bottom. As soon as it finds one that is TRUE, it grabs the corresponding value and moves on. If none match, it uses the ELSE fallback.
Real-World Analogy
Think of sorting mail into pigeonholes at a post office. The postal clerk picks up an envelope, checks the zip code: 'If zip = 10001, put in NYC bin; else if zip = 90210, put in Beverly Hills bin; else put in General Delivery bin'. Each piece of mail gets exactly one destination.