CASE in SQL

The two forms

The simple form compares one expression against a list of values. It is compact and reads well when you are translating a known set of codes into labels.

The searched form drops the subject and puts a full condition in each WHEN. It can test different columns in different branches, use ranges and combine conditions — everything the simple form cannot. When in doubt, write the searched form; it is never wrong, and it never has to be rewritten when the requirement grows a range.

-- simple: one expression, matched against values
CASE condition
  WHEN 'stable'  THEN 'no action'
  WHEN 'fragile' THEN 'monitor'
  ELSE 'schedule treatment'
END

-- searched: a full condition per branch
CASE
  WHEN valuation >= 50000 THEN 'strongroom'
  WHEN valuation >= 10000 THEN 'secure store'
  ELSE 'general store'
END

Turning codes into something readable

The everyday use: a column holds four condition codes and the report needs to say what to do about each. Run it and read down the action column.

Note what happens to the two damaged objects and the two unstable ones — both fall to the ELSE. That is fine here, and it is worth being deliberate about: ELSE is a catch-all, so a value nobody anticipated lands in it silently rather than announcing itself.

SELECT title, condition,
       CASE condition
         WHEN 'stable'  THEN 'no action'
         WHEN 'fragile' THEN 'monitor'
         ELSE 'schedule treatment'
       END AS action
FROM objects
ORDER BY title;

The ordering trap

WHEN branches are tested top to bottom and the first true one wins. Nothing after it is evaluated, whether or not it also matches.

Which means overlapping ranges have to run from the narrowest to the widest. Swap the first two branches below and the Portrait of a Glassblower — valued at 84,500 — comes back as "secure store", because 84,500 is greater than 10,000 and the query stops looking. Nothing errors. The report is simply wrong.

Run both versions. The second is the whole reason to be careful with ranges, and it is the kind of bug that survives review because the query reads plausibly.

-- correct: narrowest band first
SELECT title, valuation,
       CASE
         WHEN valuation >= 50000 THEN 'strongroom'
         WHEN valuation >= 10000 THEN 'secure store'
         ELSE 'general store'
       END AS storage
FROM objects ORDER BY valuation DESC;

-- wrong, and silent: nothing ever reaches 'strongroom'
SELECT title, valuation,
       CASE
         WHEN valuation >= 10000 THEN 'secure store'
         WHEN valuation >= 50000 THEN 'strongroom'
         ELSE 'general store'
       END AS storage
FROM objects ORDER BY valuation DESC;

Leaving out ELSE

ELSE is optional. Without it, a row that matches no branch gets NULL — not an empty string, not a zero.

That is often exactly what you want: a flag column that is set for the rows that qualify and blank for the rest reads better than one that says 'no' ten times. Just remember it is a NULL, with everything that follows — COUNT of that column counts only the flagged rows, and comparing it with = will not work.

SELECT title,
       CASE WHEN condition = 'damaged' THEN 'urgent' END AS flag
FROM objects
ORDER BY title;

CASE in ORDER BY: sorting by something that is not alphabetical

Condition sorts alphabetically as damaged, fragile, stable, unstable — which is meaningless, because the order that matters is by urgency. CASE gives you a sort key that does not exist in the table.

The same trick pins a particular value to the top of a list regardless of its natural order, which is how "show the flagged ones first, then everything else as normal" gets written.

SELECT title, condition
FROM objects
ORDER BY CASE condition
           WHEN 'damaged'  THEN 1
           WHEN 'unstable' THEN 2
           WHEN 'fragile'  THEN 3
           ELSE 4
         END,
         title;

Conditional aggregation — the reason to learn CASE properly

Put CASE inside SUM or COUNT and you can compute several differently-filtered numbers in a single pass over the table. This is the technique that pays for the whole page.

The query below returns, per wing: how many objects there are, how many need conservation work, and how much value is tied up in those. Doing it with WHERE would take three separate queries which you would then have to line up by hand — and a wing with nothing wrong in it would go missing from two of them.

SUM(CASE WHEN … THEN 1 ELSE 0 END) counts matching rows. COUNT(CASE WHEN … THEN 1 END) does the same thing by leaning on COUNT skipping NULLs. Both are idiomatic; the SUM form is harder to misread, because the ELSE 0 says out loud what happens to the rows that do not qualify.

SELECT wing,
       COUNT(*) AS objects,
       SUM(CASE WHEN condition IN ('damaged','unstable') THEN 1 ELSE 0 END) AS needing_work,
       SUM(CASE WHEN condition IN ('damaged','unstable') THEN valuation ELSE 0 END) AS value_at_risk
FROM objects
GROUP BY wing
ORDER BY wing;

CASE, COALESCE and NULLIF

COALESCE is CASE for one specific question — is this NULL, and if so what instead. When that is all you are asking, COALESCE says it in a quarter of the characters.

NULLIF is its mirror: NULLIF(a, b) returns NULL when a and b are equal. Its classic use is guarding a division, where NULLIF(denominator, 0) turns a divide-by-zero error into a NULL result.

Reach for CASE when the condition is anything other than nullness — a range, a comparison between two columns, a combination. Reach for the shorthands when it is not.

-- the same expression, three ways to write it
CASE WHEN maker IS NOT NULL THEN maker ELSE 'Unknown' END
COALESCE(maker, 'Unknown')

-- and the guard
total / NULLIF(count_of_items, 0)