SQL CASE WHEN Explained: Conditional Logic in Queries

Every query so far either kept a row or dropped it. CASE is how a query decides something about a row instead: it looks at each row, tests your conditions in order, and produces a value based on the first one that matches. It is an expression, not a statement — it produces a value wherever a value can go, which means it works inside SELECT, WHERE, ORDER BY, GROUP BY and even inside aggregate functions.

The searched CASE — test any condition

The most common form lists conditions with WHEN, each paired with a result after THEN. Conditions are checked top to bottom, and the first one that is true wins — later matches are never looked at:

SELECT first_name, salary,
       CASE
         WHEN salary >= 120000 THEN 'Senior band'
         WHEN salary >= 90000  THEN 'Mid band'
         ELSE 'Starting band'
       END AS pay_band
FROM Employees
ORDER BY salary DESC;

Notice that an employee earning 142,000 also satisfies the second condition — but they are labelled 'Senior band' because the first match ends the search. This is why you order the WHEN branches from most specific to least specific. The ELSE branch is optional; without it, rows that match nothing get NULL.

The simple CASE — compare one column against values

When every branch compares the same column against a plain value, there is a shorter form: name the column once after CASE, then list the values:

SELECT name, unit_price,
       CASE category
         WHEN 'Hardware' THEN 'Physical product'
         WHEN 'Software' THEN 'Licence'
         ELSE 'Other'
       END AS kind
FROM Products;

The two forms do the same job; the simple form just reads better when it fits. It cannot express ranges or combined conditions, though — WHEN salary >= 90000 needs the searched form.

CASE in ORDER BY — sorting by your own rules

Because CASE is an expression, it can sit in ORDER BY and define a custom sort order that no column naturally provides. Here, customer tiers sort by importance rather than alphabetically:

SELECT company, tier,
       CASE tier WHEN 'Enterprise' THEN 1 WHEN 'Growth' THEN 2 ELSE 3 END AS tier_rank
FROM Customers
ORDER BY tier_rank, company;

Alphabetical order would put 'Enterprise' before 'Growth' by luck, but the moment a tier called 'Basic' appeared it would sort first. Mapping each tier to a number makes the intended order explicit and stable.

Counting with CASE — one row of answers

This is the pattern that makes CASE genuinely powerful. COUNT counts non-NULL values, and a CASE with no ELSE produces NULL for non-matching rows — put the two together and each column counts a different subset in a single pass over the table:

SELECT
  COUNT(CASE WHEN status = 'Shipped'   THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'Pending'   THEN 1 END) AS pending,
  COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled
FROM Orders;

Without CASE this takes three separate queries, or a GROUP BY that returns one row per status stacked vertically. The CASE version returns one row with the statuses side by side — the shape a report usually wants.

CASE inside SUM — conditional totals

The same trick works with SUM to split a total by condition. Here each employee's shipped revenue and cancelled revenue land in separate columns:

SELECT e.first_name,
       SUM(CASE WHEN o.status = 'Shipped'
                THEN o.order_total ELSE 0 END) AS shipped_revenue,
       SUM(CASE WHEN o.status = 'Cancelled'
                THEN o.order_total ELSE 0 END) AS lost_revenue
FROM Employees e
JOIN Orders o ON o.emp_id = e.emp_id
GROUP BY e.first_name
ORDER BY shipped_revenue DESC;

With SUM the ELSE 0 matters: an ELSE-less CASE would feed NULLs into the addition, which SUM skips — that happens to work too, but being explicit about the zero makes the intent readable.

Three rules worth remembering

First match wins

Branches are tested in the order written, and evaluation stops at the first true condition. Overlapping conditions are fine — as long as you wrote the most specific one first.

No ELSE means NULL

A row that matches no branch gets NULL, silently. Sometimes that is exactly right (as in the COUNT pattern above); in output people will read, an explicit ELSE with a label like 'Other' is usually kinder.

Every branch must return a compatible type

One branch returning text and another returning a number is an error in strict databases and a source of quiet weirdness in lenient ones. Keep all THEN results the same kind of value.

Frequently asked questions

Related reading

• The SQL WHERE Clause: Filtering Data with Every Operator
• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL Cheat Sheet: Every Clause and Function on One Page