CASE Expressions

CASE is SQL's if/else: it returns different values depending on conditions, and it works anywhere a value can go.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Labelling values

SELECT name, price,
  CASE
    WHEN price < 50  THEN 'budget'
    WHEN price < 150 THEN 'mid-range'
    ELSE 'premium'
  END AS price_band
FROM products
ORDER BY price;

+-----------------------------+--------+------------+
|            name             | price  | price_band |
+-----------------------------+--------+------------+
| USB-C Hub                   |   35.5 | budget     |
| Laptop Stand                |     42 | budget     |
| Webcam                      |   59.9 | mid-range  |
| Mechanical Keyboard         |     89 | mid-range  |
| Noise-Cancelling Headphones |    199 | premium    |
| 27-inch Monitor             | 249.99 | premium    |
+-----------------------------+--------+------------+

Conditions are checked in order, and the first true one wins. Without an ELSE, unmatched rows get NULL.

Simple CASE

When comparing one value against several options:

SELECT id,
  CASE status
    WHEN 'delivered' THEN 'Done'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE 'In progress'
  END AS stage
FROM orders
ORDER BY id;

+----+-------------+
| id |    stage    |
+----+-------------+
|  1 | Done        |
|  2 | Done        |
|  3 | In progress |
|  4 | Done        |
|  5 | Cancelled   |
|  6 | In progress |
|  7 | In progress |
|  8 | In progress |
+----+-------------+

Conditional aggregation

CASE inside an aggregate counts or sums only matching rows — a powerful way to build summary tables:

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS open,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

+--------------+-----------+------+-----------+
| total_orders | delivered | open | cancelled |
+--------------+-----------+------+-----------+
|            8 |         3 |    4 |         1 |
+--------------+-----------+------+-----------+

PostgreSQL and SQLite also support the standard FILTER clause: COUNT(*) FILTER (WHERE status = 'delivered').

Custom sorting

SELECT id, status
FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'shipped' THEN 2
           WHEN 'delivered' THEN 3
           ELSE 4
         END, id;

+----+-----------+
| id |  status   |
+----+-----------+
|  6 | pending   |
|  8 | pending   |
|  3 | shipped   |
|  7 | shipped   |
|  1 | delivered |
|  2 | delivered |
|  4 | delivered |
|  5 | cancelled |
+----+-----------+

Practice

For each customer, show their name and a region column: 'UK' for London, 'USA' for New York and Boston, 'Other' for everything else (including unknown).

SELECT name,
  CASE
    WHEN city = 'London' THEN 'UK'
    WHEN city IN ('New York', 'Boston') THEN 'USA'
    ELSE 'Other'
  END AS region
FROM customers;

+-------------------+--------+
|       name        | region |
+-------------------+--------+
| Ada Lovelace      | UK     |
| Linus Torvalds    | Other  |
| Grace Hopper      | USA    |
| Alan Turing       | UK     |
| Margaret Hamilton | USA    |
| Tim Berners-Lee   | Other  |
+-------------------+--------+