CASE: Decisions Inside Queries

CASE lets a SQL query make a decision and return a different value for each row. It is the expression you reach for when raw values need labels, statuses, custom sort rules, or row-by-row signals for later summaries.

The shape looks wordy at first, but it reads like a small checklist:

SELECT name,
       price,
       CASE
         WHEN price < 3.00 THEN 'budget'
         WHEN price < 4.00 THEN 'everyday'
         ELSE 'treat'
       END AS price_band
FROM menu_items
ORDER BY price, name;

      name       | price | price_band
-----------------+-------+------------
 Sparkling Water |  2.20 | budget
 Espresso        |  2.50 | budget
 Earl Grey       |  3.00 | everyday
 Croissant       |  3.20 | everyday
 Sencha          |  3.40 | everyday
 Banana Bread    |  3.60 | everyday
 Cappuccino      |  3.80 | everyday
 Flat White      |  4.00 | treat
 Cinnamon Roll   |  4.10 | treat
 Cold Brew       |  4.50 | treat
(10 rows)

This is a searched CASE: each WHEN has a full condition. Postgres checks the branches from top to bottom. The first condition that is TRUE wins, and its THEN value becomes the value of the whole CASE expression for that row. If none match, ELSE supplies the fallback.

Notice the boundary points. Earl Grey is exactly 3.00, so it is not < 3.00; it falls to the second branch. Flat White is exactly 4.00, so it is not < 4.00; it falls to ELSE. CASE is not fuzzy. Your inequalities are the business rule.

CASE returns one value per row

CASE is an expression, just like price * 12 or name || ' (' || category || ')'. That means it can sit in the SELECT list, get an alias, and become a new column in the result grid. It does not filter rows by itself. It labels the rows you already selected.

Because CASE uses ordinary conditions, the NULL rule from the previous lesson applies: only TRUE wins. FALSE does not win, and UNKNOWN does not win. If you want a missing value to get its own label, say that with IS NULL:

SELECT name,
       bonus,
       CASE
         WHEN bonus IS NULL THEN 'undecided'
         WHEN bonus = 0 THEN 'none'
         WHEN bonus < 200 THEN 'small'
         ELSE 'standard'
       END AS bonus_status
FROM staff
ORDER BY id;

 name  | bonus  | bonus_status
-------+--------+--------------
 Rosa  |   NULL | undecided
 Ken   | 400.00 | standard
 Ada   | 120.00 | small
 Tomas |   NULL | undecided
 Yuki  | 250.00 | standard
 Priya |   0.00 | none
(6 rows)

Without that first branch, Rosa and Tomas would not match bonus = 0 or bonus < 200; those conditions would be UNKNOWN. If a CASE has no ELSE and no branch wins, the result is NULL. That can be useful, but it should be deliberate. Beginners often forget the fallback and accidentally create missing values.

Simple CASE: exact matches on one expression

When every branch asks "what exact value is this one expression?", the shorter form reads better:

SELECT name,
       category,
       CASE category
         WHEN 'coffee' THEN 'bar'
         WHEN 'tea' THEN 'bar'
         WHEN 'pastry' THEN 'bakery'
         ELSE 'front case'
       END AS station
FROM menu_items
ORDER BY category, name;

      name       | category |  station
-----------------+----------+------------
 Cappuccino      | coffee   | bar
 Cold Brew       | coffee   | bar
 Espresso        | coffee   | bar
 Flat White      | coffee   | bar
 Sparkling Water | drinks   | front case
 Banana Bread    | pastry   | bakery
 Cinnamon Roll   | pastry   | bakery
 Croissant       | pastry   | bakery
 Earl Grey       | tea      | bar
 Sencha          | tea      | bar
(10 rows)

Read it as: "look at category; when it is 'coffee', return 'bar'; when it is 'tea', return 'bar'; otherwise keep going." Simple CASE is great for code-to-label mappings, category cleanup, and small exact-value translations.

Use searched CASE when you need inequalities, IS NULL, IN, AND/OR, or different columns in different branches. Use simple CASE when one expression is being compared to exact values over and over. The result is the same kind of thing — one expression that returns one value per row — but the cleaner spelling changes with the problem.

Custom sort orders

ORDER BY can sort by an expression, and CASE is an expression. That makes it the usual way to sort by a business order instead of alphabetic order.

Suppose the menu should print coffee first, then pastries, then tea, then everything else:

SELECT name, category, price
FROM menu_items
ORDER BY CASE category
           WHEN 'coffee' THEN 1
           WHEN 'pastry' THEN 2
           WHEN 'tea' THEN 3
           ELSE 4
         END,
         price,
         name;

      name       | category | price
-----------------+----------+-------
 Espresso        | coffee   |  2.50
 Cappuccino      | coffee   |  3.80
 Flat White      | coffee   |  4.00
 Cold Brew       | coffee   |  4.50
 Croissant       | pastry   |  3.20
 Banana Bread    | pastry   |  3.60
 Cinnamon Roll   | pastry   |  4.10
 Earl Grey       | tea      |  3.00
 Sencha          | tea      |  3.40
 Sparkling Water | drinks   |  2.20
(10 rows)

Those numbers do not have to appear in the grid. They are just the invisible sort keys: coffee gets 1, pastry gets 2, tea gets 3, drinks fall to 4. The next sort keys, price and name, make the order stable inside each group.

A preview of conditional counting

Later, aggregate functions will collapse many rows into totals. CASE is one of the ways you prepare those totals because it can turn a condition into a per-row marker:

SELECT name,
       category,
       CASE
         WHEN category = 'coffee' THEN 1
         ELSE 0
       END AS coffee_marker
FROM menu_items
ORDER BY id;

      name       | category | coffee_marker
-----------------+----------+---------------
 Espresso        | coffee   |             1
 Cappuccino      | coffee   |             1
 Flat White      | coffee   |             1
 Cold Brew       | coffee   |             1
 Earl Grey       | tea      |             0
 Sencha          | tea      |             0
 Croissant       | pastry   |             0
 Cinnamon Roll   | pastry   |             0
 Banana Bread    | pastry   |             0
 Sparkling Water | drinks   |             0
(10 rows)

Do not worry about the counting part yet. The important idea is that CASE can manufacture the row-by-row signal: coffee rows get 1, every other row gets 0. Section 4 will show how aggregates use those signals.

CASE lets a query make decisions; casts give it explicit type conversions. Next, you will learn when SQL converts values for you, when it refuses, and when the convenient-looking conversion is the bug.