Learn SQL: CASE
Choosing with CASE
Sanne wants an overview with labels: thin or thick, on time or late, Dutch or English. Those labels aren't anywhere in the database. You make them.
CASE chooses per row
CASE works like a series of if-thens: WHEN condition THEN value, and ELSE for the rest. It always ends with END. With AS you give the new column a name.
The first WHEN that holds wins. The others aren't even looked at.
SELECT title, pages, CASE WHEN pages < 200 THEN 'thin' WHEN pages < 400 THEN 'medium' ELSE 'thick' END AS thickness FROM books| title | pages | thickness |
|---|---|---|
| De stille rivier | 312 | medium |
| Nachtwacht in Brugge | 288 | medium |
| Het laatste licht | 401 | thick |
| Draken van het Noorden | 624 | thick |
| Koken met restjes | 176 | thin |
| The Glass Harbour | 352 | medium |
| Zomer in Zeeland | 256 | medium |
| De code van Mercator | 336 | medium |
Handle NULL first
A comparison with NULL is never true. So a book that is still out falls through every WHEN with < or > and ends up in the ELSE.
That's why you put the WHEN ... IS NULL first: then every row gets the right label.
SELECT id, due_on, returned_on, CASE WHEN returned_on IS NULL THEN 'still out' WHEN returned_on > due_on THEN 'late' ELSE 'on time' END AS status FROM loans| id | due_on | returned_on | status |
|---|---|---|---|
| 1 | 2024-11-25 | 2024-11-22 | on time |
| 2 | 2024-12-23 | 2024-12-27 | late |
| 3 | 2025-01-29 | 2025-01-22 | on time |
| 4 | 2025-02-10 | 2025-02-19 | late |
| 5 | 2025-02-24 | 2025-02-24 | on time |
| 6 | 2025-03-08 | 2025-02-24 | on time |
| 7 | 2025-03-22 | 2025-03-21 | on time |
| 8 | 2025-04-02 | 2025-04-26 | late |
COALESCE, a short CASE for NULL
If all you want is a fallback value for NULL, COALESCE is shorter. COALESCE(email, 'no email') is the same as CASE WHEN email IS NULL THEN 'no email' ELSE email END.
You can give several values: COALESCE takes the first one that isn't NULL.
SELECT TRIM(name) AS name, COALESCE(email, 'no email') AS email FROM members| name | |
|---|---|
| Sanne Willems | sanne.willems@mail.be |
| Jef Wouters | Jef.Wouters@Mail.be |
| Amira El Idrissi | amira.elidrissi@mail.be |
| Pieter Desmet | pieter.desmet@mail.be |
| Lotte Vandamme | LOTTE.VANDAMME@MAIL.BE |
| Bert Claeys | no email |
| Nina De Clercq | nina.declercq@mail.be |
| Hamid Benali | hamid.benali@mail.be |
Counting with CASE
CASE can also go inside a function. SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts per group how many rows meet a condition: every row that matches counts for 1.
That way you get the total and a part of it in one query.
SELECT LOWER(genre) AS genre, COUNT(*) AS books, SUM(CASE WHEN language = 'en' THEN 1 ELSE 0 END) AS in_english FROM books GROUP BY LOWER(genre)| genre | books | in_english |
|---|---|---|
| comic | 2 | 0 |
| fantasy | 3 | 0 |
| non-fiction | 3 | 0 |
| novel | 7 | 1 |
| thriller | 5 | 1 |
Try it yourself
Show per member (member_id) how many loans came back late, and how many loans the member had in total.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson