Chapter 11 · basic · 7 min
CASE WHEN: conditional logic
So far, columns come out exactly as they're stored. CASE WHEN lets you compute a label based on a condition: "if balance is over 500, call it Platinum". It's an if/else chain that lives inside SELECT, and it shows up constantly: status labels, tiers, buckets, even conditional counting.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE fallback
ENDWe'll classify e-wallet balances into loyalty tiers.
The dataset
A `wallets` table: each user has a current balance in ringgit and a one-letter `wallet_type` code (P/B/J).
The basic shape
CASE WHEN condition THEN result ... ELSE fallback END reads almost like English. SQL checks each WHEN in order and uses the THEN of the first one that's true. If none match, it uses ELSE:
| user_name | balance | tier |
|---|---|---|
| Aisyah | 620.00 | Platinum |
| Bala | 150.00 | Gold |
| Chong | 45.00 | Standard |
| Devi | 500.00 | Platinum |
| Farid | 88.50 | Standard |
Tier by balance
Order matters
SQL stops at the first true condition; it doesn't check the rest. Swap the order (checking >= 100 before >= 500) and watch every Platinum wallet get mislabeled Gold, because 620 also satisfies >= 100:
| user_name | balance | tier |
|---|---|---|
| Aisyah | 620.00 | Gold |
| Bala | 150.00 | Gold |
| Chong | 45.00 | Standard |
| Devi | 500.00 | Gold |
| Farid | 88.50 | Standard |
Both Aisyah and Devi should be Platinum, and neither is. Write conditions from most exclusive to least.
Wrong order: see what breaks
No ELSE means NULL
ELSE is optional. Drop it, and any row that matches no WHEN gets NULL instead of a fallback label:
| user_name | balance | tier |
|---|---|---|
| Aisyah | 620.00 | Platinum |
| Bala | 150.00 | NULL |
| Chong | 45.00 | NULL |
| Devi | 500.00 | Platinum |
| Farid | 88.50 | NULL |
Sometimes that's exactly what you want: a NULL clearly flags "none of these conditions applied", rather than silently guessing.
No ELSE: everyone else is NULL
A shortcut: simple CASE
Everything so far is the "searched" form: a full condition after each WHEN. When you're only checking one expression against exact values, there's a shorter "simple" form:
| user_name | wallet_type | wallet_type_label |
|---|---|---|
| Aisyah | P | Personal |
| Bala | B | Business |
| Chong | P | Personal |
| Devi | J | Joint |
| Farid | B | Business |
CASE expression WHEN value1 THEN ... WHEN value2 THEN ... is equivalent to writing WHEN expression = value1 every time, just less repetitive, but it only works for equality checks, never ranges like >= 500.
Simple CASE: mapping codes to labels
Conditional counting
A powerful combo: put a CASE inside an aggregate. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts only the rows matching the condition:
| platinum_count | standard_count |
|---|---|
| 2 | 2 |
A single query can now report several different conditional counts side by side, the same trick powers the pivot-style summaries covered later in the tutorial.
If you've used Excel or Google Sheets
Searched CASE WHEN is the same shape as a nested IF() formula: =IF(balance>=500, "Platinum", IF(balance>=100, "Gold", "Standard")) is exactly the tier example above, just spelled differently. Simple CASE maps more directly to IFS() or SWITCH(), which check one expression against several exact values. SQL's version tends to stay readable for more branches than a deeply nested IF() does.
Ready to practice? Boost's wallet tier question below is this exact pattern, one company over.
CASE WHEN is how you translate raw data into business language: tiers, statuses, buckets. Combined with aggregation, it's also how you build pivot-style summaries without extra tools. Next: cleaning up messy text with string functions.