IntermediateLesson 13 of 305 min read

CASE: Decisions Inside a Query

Label rows, handle NULL on purpose, count several conditions in one pass and build custom sort orders with the CASE expression.

In this lesson you will

  • Write a CASE expression
  • Handle NULL inside CASE
  • Use conditional aggregation
  • Build a custom sort order

About 25 minutes

Teaching SQL to make decisions

Maya wants a price list with a label beside each product: premium, standard or budget. She wants to show stock as ‘out’, ‘low’ or ‘ok’, and she wants one row that counts orders by status across the page. Each of these is a decision made row by row, like an IF in a spreadsheet. In SQL the tool is the CASE expression. It is not a statement that does something. It is an expression that produces a value, so it can appear in the select list, in a WHERE clause, in an ORDER BY or inside an aggregate.

How CASE reads

A CASE expression is a list of tests, tried in order. CASE WHEN a condition THEN a result, repeated as needed, then an optional ELSE and a closing END. The first test that is true wins, and the rest are not looked at. If none is true and there is no ELSE, the result is NULL. Because the order matters, put the most specific tests first. Because the first match wins, a missing value needs its own test early on, or the later comparisons will silently treat it as ‘no match’.

Five CASE queries

-- 1
SELECT name, price,
  CASE WHEN price >= 16 THEN 'premium' WHEN price >= 8 THEN 'standard' ELSE 'budget' END AS band
FROM products ORDER BY price DESC

-- 2
SELECT name, stock,
  CASE WHEN stock IS NULL THEN 'unknown' WHEN stock = 0 THEN 'out'
       WHEN stock < 10 THEN 'low' ELSE 'ok' END AS stock_status
FROM products ORDER BY id

-- 3
SELECT SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders

-- 4
SELECT category,
  SUM(CASE WHEN price >= 8 THEN 1 ELSE 0 END) AS dearer,
  SUM(CASE WHEN price < 8 THEN 1 ELSE 0 END) AS cheaper
FROM products GROUP BY category ORDER BY category

-- 5
SELECT id, status FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END, id
-- 1
name | price | band
Orchid | 18.00 | premium
Glass pot | 15.00 | standard
Fern | 12.50 | standard
Succulent mix | 9.00 | standard
Clay pot | 8.50 | standard
Cactus | 6.00 | budget
Plant food | 5.50 | budget
Mist spray | 4.00 | budget
-- 2
name | stock | stock_status
Fern | 14 | ok
Cactus | 30 | ok
Orchid | 5 | low
Clay pot | 22 | ok
Glass pot | 0 | out
Plant food | 40 | ok
Mist spray | NULL | unknown
Succulent mix | 12 | ok
-- 3
shipped | pending | cancelled
7 | 2 | 1
-- 4
category | dearer | cheaper
care | 0 | 2
plant | 3 | 1
pot | 2 | 0
-- 5
id | status
7 | pending
10 | pending
1 | shipped
2 | shipped
3 | shipped
5 | shipped
6 | shipped
8 | shipped
9 | shipped
4 | cancelled

Query 1 sorts products into bands with two thresholds, 16 and 8. Only Orchid reaches premium, and Cactus, Plant food, Mist spray are budget. Query 2 turns stock into a status. The test for NULL comes first, which is why the Mist spray is labelled unknown rather than slipping into ‘ok’ or ‘low’. The Glass pot is out, and the Orchid is low. Query 3 is a useful pattern called conditional aggregation. Each SUM adds 1 for rows that match a condition and 0 for the rest, so a single pass over the table counts several groups side by side: 7 shipped, 2 pending and 1 cancelled. Query 4 uses the same trick per category, counting dearer and cheaper products on one row each. Query 5 uses CASE inside ORDER BY to create a custom order, so that pending orders come first, then shipped ones, then cancelled ones. The first two rows are orders 7 and 10.

Why conditional aggregation matters

A report often needs counts or totals for several conditions in columns, such as shipped, pending and cancelled. Without CASE you would run a separate query for each, or reshape the result afterwards. With CASE inside SUM or COUNT, one query does it. Some databases offer special pivot commands, but the CASE approach works nearly everywhere, which is why it is worth learning first.

Careful with thresholds and gaps

  • State whether each boundary is included. In query 1 a price of exactly 16 would count as premium, because the test says greater than or equal.
  • Choose thresholds that match the business, and write them in one place if the query repeats them.
  • Add an ELSE so that nothing silently becomes NULL, unless a NULL is what you want.
  • Test the edges: a value exactly on a threshold, a NULL, a zero.
  • Keep result types the same in every branch. Mixing text and numbers can fail or convert in surprising ways, depending on the database.
Common mistake: tests in the wrong order

Forgetting that the first true WHEN wins. If you test ‘price >= 8’ before ‘price >= 16’, every premium product is labelled standard. Test the narrowest condition first.

Made-up sample data

The products, orders and thresholds are made up. CASE is standard SQL and available everywhere, but some databases add shortcuts such as IIF or IF, which are not portable. Check your documentation before using them.

Write a query that shows each customer with a label, ‘new’ if they joined in 2026 and ‘established’ otherwise. Then count each label with conditional aggregation.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does a CASE expression return?

  1. Every matching row
  2. A value, chosen by the first true condition
  3. A new table
  4. An error

CASE produces a value for each row.

What does CASE return if no WHEN is true and there is no ELSE?

  1. Zero
  2. An empty string
  3. An error
  4. NULL

Add an ELSE if you do not want NULL.

Why was the mist spray labelled unknown?

  1. It was out of stock
  2. The NULL test came first in the CASE
  3. Its price was low
  4. It was not in the table

A missing stock value needs its own early test.

What does conditional aggregation do?

  1. Sorts rows
  2. Joins tables
  3. Deletes rows
  4. Counts or sums several conditions side by side in one pass

SUM of CASE gave shipped, pending and cancelled in one row.

Why does the order of WHEN lines matter?

  1. The first true condition wins
  2. The last true condition wins
  3. They are all combined
  4. Order never matters

Test the narrowest condition first.

Summary

  • CASE picks a value by the first true condition.
  • Put NULL tests and narrow tests first.
  • SUM of CASE counts several groups side by side.
  • CASE can also sit inside ORDER BY.

Next lesson

Lesson 14 covers dates and text functions, and where databases differ.

Questions

Is CASE the same as IF?

It plays the same role inside a query. CASE is standard SQL. Some databases also have IF or IIF functions, which are not portable.

Can I use CASE in WHERE?

Yes, because it is an expression. It is often clearer to write ordinary AND and OR conditions instead.

Why did the thresholds avoid exact product prices?

So that the example is clear. In real work, always decide which side of the boundary a value on the line belongs to.

Progress is saved in this browser.