COALESCE knows how to answer a single question: "is it null?". All the others —is it expensive? is stock running low? which band does this customer fall into?— need something more general. CASE is that something: SQL's if, and the last piece of module 6. With it a query stops merely returning and transforming data and starts deciding: classifying a catalogue into price tiers, putting a traffic light on the stock, sorting an order's statuses by their flow order instead of alphabetically, or turning rows into columns for a management report.

1. CASE is an expression, not a statement
2. Simple CASE against searched CASE
3. ELSE, and what happens when it's missing
4. Evaluation order: the first true condition wins
5. CASE in the SELECT: classifying
6. CASE in the WHERE and in the ORDER BY
7. CASE in the GROUP BY
8. The pivot table pattern
9. CASE, FILTER and COALESCE: when to use each
10. Common Mistakes and Tips
11. Exercises
12. Module conclusion

The idea to fix before the syntax: CASE returns a value. It doesn't execute blocks of code, it doesn't jump, it doesn't control the program's flow. It's an expression, exactly like price * 1.21 or UPPER(name).

Everything else follows from that: it can go anywhere a value fits (SELECT, WHERE, ORDER BY, GROUP BY, HAVING, inside a function or an aggregate); all its branches have to return a compatible type, not a number in one and text in another; and it returns exactly one value per row, like every scalar function (06-01).

The second CASE sits inside a multiplication: it's just another operand. And notice promo_price's four decimals: NUMERIC(10,2) * NUMERIC(3,2) gives scale 4, and you'd round when presenting (06-02).

Don't confuse it with the procedural IF. PL/pgSQL —the language of stored procedures, module 10— does have an IF … THEN … END IF statement that executes code; SQL's CASE executes nothing, it gets evaluated and produces a value.

There are two forms and they aren't interchangeable. The simple CASE compares an expression against a list of values; the searched one, complete and independent conditions:

The simple CASE is perfect for translating a closed domain —payment_method, status, a country code— and the searched one is for everything else. And there's one case where the simple one can't do the job:

Order 1 has no sales rep and the first query says "With sales rep". The reason is 04-03 to the letter: the simple CASE compares with = and employee_id = NULL evaluates to UNKNOWN, never to TRUE. The WHEN NULL branch is dead code: it will never run, on any row.

Rule: the moment a NULL comes into play, searched CASE with IS NULL. The simple one can't detect the absence of a value and, worse, it raises no error: it silently returns the wrong branch.

ELSE is optional, and if you omit it and no condition holds, CASE returns NULL.

The shipped and pending statuses fit no branch and come out NULL. Those nulls are treacherous because they don't come from the data: your own expression manufactures them. If you then group by that column you'll have a NULL group nobody asked for; if you use it in a WHERE, those rows will disappear (04-03). Always write the ELSE, even if it's ELSE 'Other' or an explicit ELSE NULL: a hand-written ELSE NULL says "I thought about this case" and a missing ELSE says "I forgot", and six months from now you won't know which it was.

CASE evaluates its WHENs top to bottom and stops at the first one that's TRUE; the rest aren't even evaluated (it's the same laziness as COALESCE, which is, after all, a CASE in disguise). That makes the order part of the logic and produces the most frequent mistake with CASE: putting the widest range first.

All three products, from the cheapest to the most expensive, fall into the same tier: they all satisfy price < 25 and the other conditions are never evaluated. The query raises no error; it simply misclassifies the entire catalogue. The correct version orders the conditions from the most restrictive to the most general:

Since the first true one wins, each branch only needs its upper bound: there's no need to write WHEN price >= 5 AND price < 15. That's the elegance of a cascading CASE, and it's also its trap: if you reorder the lines, you change the result.

The main use. A stock traffic light combined with the price tier:

Product 13 —the only one in the catalogue with stock 0— gets identified without looking for it. That's the value of a traffic light: turning a number that has to be interpreted into a label that reads at a glance.

Since CASE returns a value, you can compare it:

Both return the 7 products under €5, but a CASE in the WHERE is longer, harder to read and —the important bit— it prevents the index from being used, because the column ends up wrapped in an expression (08-03). Almost always what you want is an OR, an IN or a BETWEEN. The only reasonable exception is a filter whose condition depends on a parameter (WHERE column = CASE WHEN $1 = 'all' THEN column ELSE $1 END), and even there better solutions exist.

Here CASE has no substitute. An order's statuses have a flow order —pending → paid → shipped → delivered, with cancelled off to one side— that doesn't match the alphabetical one:

Sorted alphabetically it would come out cancelled, delivered, paid, pending, shipped: a meaningless sequence forcing the reader to mentally reassemble the life cycle. Here the CASE is the information. Notice two details: it's a simple CASE, because it compares against the values of a closed domain, and the ORDER BY's expression doesn't appear in the SELECT, which is perfectly legal (02-05). Another very useful variant, "this first and the rest afterwards", is ORDER BY CASE WHEN status = 'pending' THEN 0 ELSE 1 END, order_date DESC.

If you can classify in the SELECT, you can group by the classification. The only rule is 04-05's: repeat the whole expression in the GROUP BY, not the alias.

7 + 10 + 3 = 20 products, and the catalogue's overall average is still €9.035. The duplicated expression is ugly but necessary: the GROUP BY is evaluated before the SELECT (module 2's logical order) and the alias tier doesn't exist yet.

Dialect note: PostgreSQL and SQL Server require the expression to be repeated; MySQL, SQLite and MariaDB allow GROUP BY tier using the SELECT's alias, which is convenient and isn't standard. The clean alternative —naming the classification once— is a CTE, and it arrives in 10-02.

And now we get to CASE's most powerful use: putting it inside an aggregate to turn rows into columns. The starting problem is 04-05's report: revenue by category and year comes out as a long list with one row per combination, and management wants it as a two-way table. The idea is deceptively simple: SUM(CASE WHEN year = 2025 THEN amount ELSE 0 END) adds up only 2025's, because the other rows contribute a zero; repeat the trick with another condition and you have another column.

The columns agree with the course's canonical figures: €603.52 in 2025, €124.43 in 2026 and €727.95 in total. And the reading is immediate: Sustainable home has sold nothing in 2026 and Supplements has never sold anything — something that shows up far less clearly in a list of twelve rows by category and year.

Three things to understand about the pattern. The columns are fixed: if there's 2027 data tomorrow you have to edit the query, because SQL decides the column list while parsing, before reading a single row. The ELSE 0 is the engine of the trick: without it the non-matching rows would contribute NULL, and a whole group of nulls would give NULL instead of 0. And it works just the same with COUNT, AVG or MAX — with COUNT you write it without the ELSE, precisely because COUNT ignores nulls.

Another pivot, this time with COUNT: payment methods by the customer's country.

11 + 4 + 3 + 2 = 20 orders. Card dominates in Spain, while the two Portuguese customers with orders use PayPal exclusively: a commercial conclusion the flat list didn't let you see.

An alternative: crosstab. PostgreSQL's tablefunc extension brings crosstab(), which generates pivots from a three-column query (row, column, value). It saves you writing a CASE per column, but it forces you to declare the output types by hand and it's PostgreSQL-specific; you'll see it applied in the practical work of module 11. For three or four columns, SUM(CASE …) is still the clearest option and the only portable one.

In 04-04 you met FILTER (WHERE …) and it was said that its CASE equivalent would be explained here. These two expressions say the same thing:

The second-to-last row is often the deciding one: in the previous pivot, Supplements shows 0.00 because the ELSE 0 contributes zeros; with FILTER it would show *(null)* and you'd have to wrap it in a COALESCE (06-04). Neither is better: FILTER is more readable, CASE … ELSE 0 is more portable and controls the default value.

And the rule that closes the quartet:

COALESCE(cost, 0) and CASE WHEN cost IS NULL THEN 0 ELSE cost END are identical and the first is better; but as soon as the rule gets more complicated —"if the cost is null put 0, and if the product is also discontinued put −1"— COALESCE falls short and CASE doesn't.

• Putting the widest range first. The first true condition wins and the rest is dead code: from the most restrictive to the most general. And the simple CASE with NULL (CASE column WHEN NULL THEN …) never enters that branch: use CASE WHEN column IS NULL THEN ….
• Omitting the ELSE. The rows that don't fit come out NULL, and they're nulls your query manufactures, not the data.
• Mixing types across branches. They all have to return a compatible type; if not, ERROR: CASE types text and integer cannot be matched.
• Using the CASE's alias in the GROUP BY. In PostgreSQL you have to repeat the expression, because the GROUP BY is evaluated before the SELECT. And avoid putting a CASE in the WHERE when an OR will do: longer, less readable and no index (08-03).
• Forgetting the ELSE 0 in a SUM pivot. A group with no matching rows will give NULL instead of 0. And the other way round: with COUNT the ELSE 0 is superfluous and falsifies the count, because 0 isn't null and gets counted.
• Expecting a pivot to generate its columns by itself. The column list is fixed; a new year means editing the query.
• Tip: if the CASE has more than four branches, consider a reference table. A twenty-line CASE repeated across ten reports is a domain table somebody should have created (module 5).
• Tip: use FILTER in PostgreSQL and CASE when you need portability, and align the branches vertically: a well-indented CASE reads like a table.

Management wants the catalogue dashboard. Group the active products by tier (< 5 Budget, < 15 Mid, the rest Premium) and show the number of products, the average price and how many of them have fewer than 50 units in stock. (Hint: COUNT(CASE WHEN … THEN 1 END) or FILTER.)

A colleague has written this shipping classification and concludes that "every one of our orders carries a shipping charge":

(1) How many distinct labels can it really return and why? (2) Fix it so it genuinely distinguishes the three cases and give the count of each. (3) The query has no ELSE: why doesn't it show, and when would it?

Build the pivot table of orders by status and year: one row per status and columns p2025, p2026 and total, sorted by the flow order (pending, paid, shipped, delivered, cancelled) and not alphabetically. Write it twice, with SUM(CASE …) and with COUNT(*) FILTER (WHERE …), and explain how the results differ.

Solution 1

19 products, not 20: the WHERE active = TRUE excludes number 20 (Spirulina capsules, €16.40), which was Premium — that's why that tier drops from 3 to 2 and its average price rises to €20.45. The three products with critical stock are 8 (45 units), 13 (0) and 15 (40), split between Mid and Premium: it's the expensive products that run out of stock. And COUNT(CASE WHEN stock < 50 THEN 1 END) goes without an ELSE on purpose: the non-matching rows return NULL and COUNT ignores them; with ELSE 0 it would count all 19.

Solution 2

1. Only one: 'Has shipping'. Every shipping_cost in GreenStore is >= 0 —01-06's CHECK constraint guarantees it—, so the first condition is true for all twenty rows and the other two are dead code. It's section 4's mistake in its purest form: the most general condition first. 2. Ordering from the most restrictive to the most general:

20 orders and €118.25 of shipping, module 4's canonical figure: four orders with free delivery and three with €12.50 of shipping.

3. It doesn't show purely because the first condition captures every row. It's a time bomb: the day somebody inserts an order with a null shipping_cost —impossible today thanks to the NOT NULL, but schemas change (05-06)—, that row would return NULL in shipping_type and would be a phantom group in the report. Always write the ELSE.

Solution 3

The FILTER version changes only the two middle columns, which become COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM order_date) = 2025) AS p2025 and its 2026 equivalent. And here the results match exactly, zeros included, because COUNT over an empty set returns 0 and not NULL (04-04): FILTER with COUNT is safe. If instead of COUNT(*) you used SUM(shipping_cost) FILTER (…), the groups with no rows from that year would give *(null)* while SUM(CASE … ELSE 0 END) would give 0.00. The difference isn't FILTER against CASE: it's what each aggregate returns when it has nothing to aggregate.

The report, read out: GreenStore has 14 delivered orders, all of them from 2025, and the four from 2026 are still in progress —one pending, two paid, one shipped—.

With CASE you close module 6:

• CASE is an expression, not a statement: it returns a value and it fits in any clause, even inside a multiplication or an aggregate.
• You can tell the simple CASE —for closed domains— from the searched one —for ranges and conditions—, and you know the simple one can't detect NULL because it compares with =. You always write the ELSE, because without it the rows that don't fit return nulls manufactured by your own query.
• You order the conditions from the most restrictive to the most general: the first true one wins and the rest is dead code.
• You use it in the SELECT to classify, in the ORDER BY to impose a business order the alphabetical one can't give, and in the GROUP BY to aggregate by the classification, repeating the whole expression.
• You've mastered the pivot table pattern, SUM(CASE WHEN … THEN … ELSE 0 END), with its limits (fixed columns) and its modern equivalent FILTER. And you know how to choose between COALESCE, NULLIF, CASE and FILTER depending on what you're asking.

And with this lesson the whole of module 6 ends. You started out unable to join name and last_name into one column; now you compose text, extract sizes with regular expressions, round money without losing cents, group by month and by quarter, convert types deliberately, tame nulls and classify rows with conditional logic. Your queries no longer return data: they return answers.

But all those answers are computed by looking at one row at a time, or one group at a time. And there's a whole family of questions that doesn't work that way, because it needs to compare each row with the result of another query: which products are above the average of their category? which customers have an average order value above the overall average —the question left explicitly outstanding in 04-06 when you discovered that HAVING can't refer to a global aggregate—; which orders include the catalogue's most expensive product? which customers have never bought anything, without resorting to a LEFT JOIN with IS NULL? They all share the same shape: a query inside another query. In module 7, Subqueries, you'll learn to write them: scalar and list subqueries, correlated ones —which run once per row—, EXISTS and NOT EXISTS, subqueries in the SELECT, in the FROM and in the WHERE, and the criteria for deciding when a subquery is the right tool and when a JOIN is.