CASE WHEN

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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
Options
• 'Trained'
• 'Veteran'
• 'Rookie'
• NULL

Hint : 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.

Items to match
• CASE WHEN power >= 70 THEN 'Medium' ELSE 'Weak' END
• CASE type WHEN 'electric' THEN 'Zap!' ELSE '...' END
• CASE WHEN wins > losses THEN 'Winning' END
• CASE WHEN level < 15 THEN 'Rookie' ELSE 'Trained' END
Definitions
• '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;
Options
• NULL
• 'Even'
• An error: a CASE needs an ELSE
• '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.

Starter code
-- Write your query below
Back to the course