The CASE expression allows you to add conditional logic to your queries. It is similar to the if ... else or switch expressions in other programming languages.
Most database engines allow a CASE expression to return any data type (text, numbers, etc.) but each WHEN "branch" of the statement must return the same type as each other. SQLite allows any type to be returned but for compatibility, these examples will return the same type from all branches.
This example shows a common use case where we want to create custom groups or "buckets" for the data that are not part of the database schema.
SELECT name,
CASE
WHEN price < 5.00 THEN 'Budget'
WHEN price < 40.00 THEN 'Standard'
ELSE 'Luxury'
END AS category
FROM products;
| name | category |
|--------------------------|----------|
| Cool Splash Cologne | Luxury |
| More Spice Please Chips | Budget |
| Night Shift Coffee | Standard |
| Crunchy Munchy Trail Mix | Standard |
| Super Glide Floss | Budget |
| Big Bark Dog Food | Standard |
| Soft Purr Cat Food | Standard |
| Tiny Fresh Scent Tree | Budget |
The conditions inside a CASE statement can be compounded using logical operators like AND and OR.
Another thing to note about the CASE statement is that it is evaluated in order from top to bottom, left to right. For each row it will return a result for only the first match it encounters. If there are no matches and you supply an ELSE expression, that value will be the result. If you don't supply an ELSE, NULL is returned.
Notice how the "Night Shift Coffee" product could technically match all of the "Critical Restock", "Urgent Restock" and "Restock Soon" categories. However once it matches "Critical Restock" the rest of the CASE is not evaluated and we move on to the next product. You can utilize this behaviour or you can design your queries such that none of the branches in your CASE overlap.
SELECT name,
remaining_stock,
CASE
WHEN remaining_stock < 5
THEN 'Critical Restock'
WHEN remaining_stock <= 10 AND category = 'Food'
THEN 'Urgent Restock'
WHEN remaining_stock <= 20
THEN 'Restock Soon'
ELSE 'No Restock Needed'
END
AS needs_restock
FROM products
ORDER BY needs_restock;
The previous query is ordered alphabetically because our newly computed column needs_restock has a text data type. If we wanted to apply a custom order to the query we can also apply a CASE statement to our ORDER BY clause.
Notice that this example also shows that you can add an expression directly after the CASE keyword; this is called a "base" expression. In this case it is needs_restock. When a base expression is present it is evaluated once at the beginning. For each row, the value of needs_restock determines which integer is returned.
As an aside, a better way to structure this query would be to use Common Table Expressions to set up our results and ordering rules. An example of this can be seen at the bottom of the page.
SELECT name,
remaining_stock,
CASE
WHEN remaining_stock < 5
THEN 'Critical Restock'
WHEN remaining_stock <= 10 AND category = 'Food'
THEN 'Urgent Restock'
WHEN remaining_stock <= 20
THEN 'Restock Soon'
ELSE 'No Restock Needed'
END
AS needs_restock
FROM products
ORDER BY
CASE needs_restock
WHEN 'Critical Restock' THEN 1
WHEN 'Urgent Restock' THEN 2
WHEN 'Restock Soon' THEN 3
WHEN 'No Restock Needed' THEN 4
END
ASC;
CASE expressions can be used as part of aggregate queries using GROUP BY and aggregate functions like avg().
SELECT avg(quantity),
CASE
WHEN quantity <= 10 THEN 'Small'
WHEN quantity <= 50 THEN 'Medium'
WHEN quantity > 50 THEN 'Large'
END AS order_size
FROM orders
GROUP BY order_size;
| avg(quantity) | order_size |
|---------------|------------|
| 60 | Large |
| 37.5 | Medium |
| 10 | Small |
CASE can also be used as an input for aggregate functions. This technique is useful when you need to create "buckets" of data as above but group the data using another separate category. If you are familiar with spreadsheets, this technique is similar to that of a pivot table.
This example shows us how many of each price class of product each vendor has for sale.
Notice how in this example we use a LEFT JOIN which will include vendors that have no products for sale. The vendor "Vend-tertainment" will return a NULL value for p.price.
This causes our CASE expressions to fall back to their ELSE return value of 0.
SELECT
v.name AS vendor,
SUM(CASE
WHEN p.price < 5.00 THEN 1
ELSE 0
END) AS budget,
SUM(CASE
WHEN p.price < 40.00 THEN 1
ELSE 0
END) AS standard,
SUM(CASE
WHEN p.price >= 40.00 THEN 1
ELSE 0
END) AS luxury
FROM vendors v
LEFT JOIN products p
ON v.id = p.vendor_id
GROUP BY v.id;
Here is the example above that is restructured using Common Table Expressions and UNION to be easier to maintain and understand. Instead of using the order status labels as our column names to match on, we use the integer id we created which reduces the likelihood of human error by misspelling one of the labels somewhere.
When you need to re-use the same statements in multiple places in your query consider techniques like this that limit the amount of duplicate code you need to write.
WITH stock_statuses AS (
SELECT name,
remaining_stock,
CASE
WHEN remaining_stock < 5
THEN 1
WHEN remaining_stock <= 10 AND category = 'Food'
THEN 2
WHEN remaining_stock <= 20
THEN 3
ELSE 4
END
AS id
FROM products
),
status_labels AS (
SELECT 1 AS status_id,
'Critical Restock' AS label
UNION SELECT 2, 'Urgent Restock'
UNION SELECT 3, 'Restock Soon'
UNION SELECT 4, 'No Restock Needed'
)
SELECT ss.name,
ss.remaining_stock,
sl.label AS needs_restock
FROM stock_statuses ss
INNER JOIN status_labels sl
ON ss.id = sl.status_id
ORDER BY ss.id;