Choosing Values with CASE

Sometimes a column should say different things for different rows: “cheap” for some games, “expensive” for others. That's what CASE is for. It goes through conditions one after another and gives the value of the first one that holds:

SELECT title, price,
       CASE
           WHEN price < 20 THEN 'cheap'
           WHEN price < 40 THEN 'regular'
           ELSE 'expensive'
       END AS category
FROM games
LIMIT 4;

Read it as: when the price is under 20, then “cheap”; when it's under 40, then “regular”; otherwise (ELSE) “expensive”. CASE starts the choice and END finishes it, and the whole thing is one value, which gets its name with AS like any calculation. The conditions are the ones you know from WHERE, so they can use AND, OR, IN, LIKE or IS NULL, and the values can be texts, numbers or calculations. Put each WHEN on its own line, so that you can read them like a list.

The order of the WHENs matters. SQLite checks them from the top and stops at the first one that holds. Moon Farm costs $20: the first condition doesn't hold, the second one does, so it's “regular”, and the rest is skipped. If you swapped the first two lines, so that WHEN price < 40 came first, Cube Logic for $5 would be “regular” too: 5 is less than 40, and SQLite would never get to the line with “cheap”. So when conditions overlap, put the narrowest one first.

Without ELSE, the rows that match no condition get NULL.

When all the conditions compare one column with a value, there's a shorter form: the column comes right after CASE, and each WHEN has just the value:

SELECT id, status,
       CASE status
           WHEN 'paid' THEN 'OK'
           WHEN 'refunded' THEN 'money returned'
       END AS note
FROM orders
WHERE id BETWEEN 6 AND 8;

Typical mistake: a forgotten END. Without it, SQLite doesn't know where the CASE ends, and it stops at the first word it didn't expect: near "AS": syntax error. When you get such an error near a CASE, check that it has its END.