How Does the CASE Expression Work?

Simple CASE compares one expression against a list of values and returns the matching branch. Searched CASE evaluates a condition for each branch in order and returns the first one that is true. Searched CASE is the more flexible form and the one you will use most.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1,  75.50, 'pending'),
  (3, 2, 940.00, 'pending'),
  (4, 2,  30.00, 'cancelled');

-- Searched CASE: a condition per branch, first match wins.
SELECT id,
       amount,
       CASE
         WHEN amount >= 500 THEN 'large'
         WHEN amount >= 100 THEN 'medium'
         ELSE 'small'
       END AS size_band
FROM orders
ORDER BY id;

The bands come out large, medium, large and small. Order 3 is large even though it is still pending, because the CASE only looks at amount — status is not part of the condition. Keep unrelated conditions out of a CASE or the branch order starts to matter in ways that are easy to misread.

Searched CASE stops at the first matching WHEN, so the branch order encodes a priority. If you write WHEN amount >= 100 before WHEN amount >= 500, every large order is classified as medium, because the first branch already matched.

Simple CASE is the switch statement shape: CASE status WHEN 'pending' THEN ... WHEN 'shipped' THEN ... END. It is neater when you are mapping one column through a fixed set of values, and it is the right choice when the mapping must be exhaustive — pair it with an ELSE branch so an unexpected status produces a visible value rather than NULL.

The rule that catches people out: CASE lives in the expression list, so it belongs in SELECT, ORDER BY, GROUP BY or inside another expression — but not in WHERE. To filter on a computed value, wrap the query in a CTE or a subquery and filter on the alias in the outer query.

The CASE produces the band, and a second pass groups by it. You cannot group by the alias directly in the same SELECT that defines it, so the band has to be computed in a CTE first.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1,  75.50),
  (3, 2, 940.00),
  (4, 2,  30.00),
  (5, 3, 510.00),
  (6, 3,  99.99);

WITH banded AS (
  SELECT id,
         amount,
         CASE
           WHEN amount >= 500 THEN 'large'
           WHEN amount >= 100 THEN 'medium'
           ELSE 'small'
         END AS size_band
  FROM orders
)
SELECT size_band,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS band_total
FROM banded
GROUP BY size_band
ORDER BY band_total DESC;

Three rows come back, ordered by band total. The large band holds two orders totalling 1450.00, the small band holds three orders totalling 205.49, and the medium band holds one order of 120.00. Ordering by the total rather than by the label puts the most valuable band first, which is what a revenue report usually wants.

Note the boundaries. The medium band starts at exactly 100.00 and the large band at 500.00, and because the branches are tested in order, an amount of exactly 500.00 is large. Decide whether a boundary belongs to the lower or the upper band and write the comparison accordingly — >= 500 puts it in the top band, > 500 would put it in the one below.

A detail worth raising in review: grouping by an ordered band is not the same as grouping by the band name, because the alphabet puts large, medium and small in a strange order. If the report needs a fixed display order, add a sort key column in the CTE and order by that, rather than relying on the alphabetical ordering of the labels.

This is the shape every segmentation query eventually needs: a computed default that a human can override. The trick is to test the override column for the presence of a value first, so it wins over the computed branch.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  lifetime_spend DECIMAL(12, 2) NOT NULL,
  manual_tier VARCHAR(20)
);

INSERT INTO customers (id, name, lifetime_spend, manual_tier) VALUES
  (1, 'Ada Lovelace',   5400.00, NULL),
  (2, 'Grace Hopper',  12500.00, 'platinum'),
  (3, 'Alan Turing',     480.00, NULL),
  (4, 'Joan Clarke',   5100.00, 'bronze');

SELECT id,
       name,
       lifetime_spend,
       manual_tier,
       CASE
         WHEN manual_tier IS NOT NULL THEN manual_tier
         WHEN lifetime_spend >= 10000 THEN 'platinum'
         WHEN lifetime_spend >= 1000  THEN 'gold'
         WHEN lifetime_spend >= 100  THEN 'silver'
         ELSE 'bronze'
       END AS effective_tier
FROM customers
ORDER BY id;

The effective tiers come out gold, platinum, silver and bronze. Grace Hopper would have qualified as platinum on spend alone, and her manual tier says platinum anyway, so the two agree; Joan Clarke is the interesting case — her spend of 5100.00 would make her gold, but the manual bronze flag wins and the spend-based branch never runs.

The manual_tier IS NOT NULL test has to come first, because searched CASE returns the first matching branch. Put it later and the spend-based branch would claim the row before the override was ever considered. Using IS NOT NULL rather than <> 'bronze' also avoids the classic trap: a comparison against NULL is UNKNOWN, so an override of NULL would fall through to the spend-based branch by accident rather than by design — which is the desired behaviour here, but only because the NULL was tested explicitly.

Two production notes. If an override can legitimately be set to an empty string as well as NULL, test for both, because an empty string is a real value and would otherwise be treated as a tier named the empty string. And consider whether an override should expire — a manual tier set two years ago may no longer be appropriate; adding an override_expires_at column and testing it in the same branch is usually worth it.