🎓 A1Learner
🔍

SQL Case

CASE is an expression that picks a value for each row. It tests conditions in order and returns the value beside the first test that passes.

One product, one label

The Desk Lamp holds no stock at all. The first test passes, so the label reads Out of stock.

SELECT name, stock, CASE WHEN stock = 0 THEN 'Out of stock' ELSE 'In stock' END AS stock_label
FROM products
WHERE name = 'Desk Lamp';

A label for every product

Three branches cover the stock levels here. A stock of 0 reads Out of stock, a stock under 50 reads Low, and the rest read In stock.

SELECT name, stock, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END AS stock_label
FROM products
ORDER BY product_id;

Price bands

The tests can run on any column, not just stock. These bands sort the products by price range instead.

SELECT name, price, CASE WHEN price < 10 THEN 'Under 10' WHEN price < 30 THEN '10 to 29.99' ELSE '30 or more' END AS price_band
FROM products
ORDER BY product_id;

Example

SELECT CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END AS stock_label, COUNT(*) AS product_count
FROM products
GROUP BY CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END
ORDER BY stock_label;

Key points

  • WHEN: the part of CASE that holds one test for a row
  • THEN: the part that gives the value when its test passes
  • ELSE: the part that gives the value when no test passes
  • END: the word that closes a CASE expression
  • The tests run in order, and the first passing test wins.
  • Use AS to give the picked values a column name.
  • CASE picks one value per row from the tests it runs in order.

In this example, CASE labels each product from its stock level. GROUP BY gathers the products under the label each one picked. COUNT then reports five products in stock, two low, and one out of stock.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

In a CASE expression, which value does a row get when no WHEN test passes?