CASE WHEN
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — CASE WHEN: if / else in SQL
So far, every column you displayed came straight from the table or from a calculation. CASE lets a query choose a value depending on a condition — it is the if / else of SQL.
Meet the battle records of the lab. Notice the nickname column (mostly empty), the wins and losses columns, and Shadowchu's unknown power:
Let's label each Codémon according to its power:
SELECT name, power,
CASE
WHEN power >= 80 THEN 'Strong'
WHEN power >= 70 THEN 'Medium'
ELSE 'Weak'
END AS tier
FROM codemon;SQL reads the WHEN conditions from top to bottom and stops at the first one that is true. When none is true, the ELSE value is used.
Anatomy of a CASE
CASE WHEN condition THEN value WHEN other_condition THEN other_value ELSE default_value END. The whole block is one expression, so give it an alias like any computed column — and never forget the END!
Exercise 2 — Order matters, ELSE is optional
Because SQL stops at the first true condition, the order of your WHEN lines matters. Watch what happens when we test the smaller value first:
SELECT name, power,
CASE
WHEN power >= 70 THEN 'Medium'
WHEN power >= 80 THEN 'Strong'
ELSE 'Weak'
END AS tier
FROM codemon;Pyrochu and Rockachu are now Medium: the >= 70 test caught them before >= 80 was even checked. Always put the most specific condition first.
The ELSE part is optional. Without it, the rows that match no condition get NULL:
SELECT name, power,
CASE WHEN power >= 80 THEN 'Strong' END AS tier
FROM codemon;NULL never matches
Shadowchu's power is NULL. A comparison with NULL is never true, so Shadowchu never matches a WHEN: it falls into the ELSE (or gets NULL). The next chapter shows how to handle this on purpose.
Exercise 3 — Predict the label
Florachu is level 19. What does this expression return for it?
CASE
WHEN level >= 20 THEN 'Veteran'
WHEN level >= 15 THEN 'Trained'
ELSE 'Rookie'
END'Trained''Veteran''Rookie'NULLHint : 19 is not >= 20, so SQL moves on to the next condition.
Exercise 4 — Complete the tiers
Complete the expression so that each Codémon gets Strong (power 80 or more), Medium (70 or more) or Weak.
Hint : Think of the four keywords of a CASE, in order: open a condition, give its value, give the default, close the block.
Exercise 5 — The simple CASE and custom sorting
When you only compare one column to fixed values, there is a shorter form: the simple CASE. Put the column right after CASE, then list the values:
SELECT name, type,
CASE type
WHEN 'fire' THEN 'Hot'
WHEN 'water' THEN 'Wet'
ELSE 'Other'
END AS feel
FROM codemon;CASE is not limited to SELECT. In ORDER BY it lets you define your own order — here fire first, then water, then everyone else, alphabetically inside each group:
SELECT name, type
FROM codemon
ORDER BY CASE type
WHEN 'fire' THEN 1
WHEN 'water' THEN 2
ELSE 3
END,
name;Simple vs full form
The simple form only tests equality. For ranges (>=, BETWEEN), several columns or IS NULL, use the full form CASE WHEN condition THEN ... seen before.
Exercise 6 — Evaluate for Voltachu
Voltachu is an electric Codémon of level 12 and power 65, with 3 wins and 7 losses. Match each expression with its result for Voltachu.
CASE WHEN power >= 70 THEN 'Medium' ELSE 'Weak' ENDCASE type WHEN 'electric' THEN 'Zap!' ELSE '...' ENDCASE WHEN wins > losses THEN 'Winning' ENDCASE WHEN level < 15 THEN 'Rookie' ELSE 'Trained' END'Weak''Zap!'NULL'Rookie'Exercise 7 — No match, no ELSE
Florachu has 5 wins and 5 losses. What does this query return in the form column for Florachu?
SELECT name,
CASE
WHEN wins > losses THEN 'Winning'
WHEN wins < losses THEN 'Losing'
END AS form
FROM codemon;NULL'Even''Losing'Hint : Neither condition is true for 5 vs 5, and there is no ELSE to provide a default.
Exercise 8 — Your own order
Complete the query so that water Codémons come first, then fire Codémons, then all the others — each group sorted by name.
Hint : Water must get the smallest number, fire the next one, and the CASE must be closed before the second sort key.
Exercise 9 — Experience badges
Professor Pi hands out badges according to experience.
Write a query that returns the name, the level and a column named badge whose value is:
1. 'Veteran' when the level is 20 or more
2. 'Trained' when the level is between 15 and 19
3. 'Rookie' otherwise
Sort the result by level, highest first.
-- Write your query below