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 'Platinum' THEN 1
         WHEN 'Gold'     THEN 2
         WHEN 'Silver'   THEN 3
         ELSE 4
       END AS tier_rank
FROM Customers
ORDER BY tier_rank, company;

Sorted alphabetically, the tiers would come out Bronze, Gold, Platinum, Silver: the least important tier first and Platinum stuck in third place. Mapping each tier to a number makes the intended order explicit. The ELSE puts Bronze last here, and it would do the same for any tier added later that the CASE does not name yet, which is usually the safe place for it. The CASE sits in the SELECT list here so you can see each rank; written directly after ORDER BY instead, it sorts the rows the same way without showing the number.

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 more than it looks. Leave it out and every non-matching row contributes NULL, which SUM skips. That is harmless while a group has at least one matching row, but Tomas and Hiroshi have no cancelled orders at all, so their CASE produces nothing but NULLs, and a SUM of only NULLs is NULL, not 0:

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

A NULL in a revenue column reads as "unknown", when the truth is "none". ELSE 0 is what makes the zero appear; wrapping the whole thing in COALESCE(SUM(...), 0) works too.

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.

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Label the price bands Easy
• Support levels by tier Easy
• One-row status report Medium
• Revenue won and lost Hard

See all 42 exercises →

Frequently asked questions

Related reading

• SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX
• The SQL WHERE Clause: Filtering Data with Every Operator
• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL Cheat Sheet: Core Syntax, Joins and Functions on One Page