CASE and Conditional Logic

Branch inside a query with CASE, and reshape rows into columns with conditional aggregation.

• Intermediate
• 14 min read
• 3 objectives

Before this lessonLesson 6: Subqueries and EXISTS

SQL has no if statement, because a query is not a program that steps through rows. What it has is CASE, an expression that produces a different value depending on the row it is looking at.

CASE WHEN ... THEN ... ELSE

SELECT name, age,
       CASE
         WHEN age < 30 THEN 'under 30'
         WHEN age < 40 THEN '30s'
         ELSE '40 and up'
       END AS band
FROM customers;

name  | age | band
------+----+----------
Ada   | 36  | 30s
Linus | 28  | under 30
Grace | 45  | 40 and up

Branches are tested top to bottom and the first match wins, so order them from most to least specific. Without an ELSE, a row matching nothing gets NULL.

CASE works anywhere an expression does

It is not limited to the select list. Sorting by a CASE is how you get a custom order that is not alphabetical.

SELECT id, status, total
FROM orders
ORDER BY CASE status
  WHEN 'paid' THEN 1
  WHEN 'refunded' THEN 2
  ELSE 3
END, total DESC;

id | status   | total
---+---------+------
10 | paid     | 120
11 | paid     | 35.5
12 | refunded | 80

Conditional aggregation: rows into columns

This is the single most useful trick in the lesson. Put a CASE inside an aggregate and each branch becomes its own column — a pivot table, without any special pivot syntax.

SELECT country,
       COUNT(*) AS trans_count,
       SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
       SUM(amount) AS trans_total,
       SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total
FROM transactions
GROUP BY country;

country | trans_count | approved_count | trans_total | approved_total
--------+------------+---------------+------------+---------------
DE      | 2           | 1              | 2500        | 2000
US      | 3           | 2              | 5000        | 3000

That is LeetCode 1193 Monthly Transactions I almost verbatim. Note the two shapes: SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts matches, and SUM(CASE WHEN cond THEN value ELSE 0 END) totals them.

Flipping a value in place

A CASE in an UPDATE swaps values in one pass, with no temporary table.

UPDATE customers
SET country = CASE country WHEN 'UK' THEN 'US' WHEN 'US' THEN 'UK' ELSE country END;
SELECT id, name, country FROM customers;

3 rows affected.

id | name  | country
---+------+--------
1  | Ada   | US
2  | Linus | FI
3  | Grace | UK

COALESCE, NULLIF and IIF

• COALESCE(a, b, c) returns the first argument that is not NULL.
• NULLIF(a, b) returns NULL when the two are equal — the standard guard against dividing by zero.
• IIF(cond, yes, no) is shorthand for a two-branch CASE.

SELECT name,
       COALESCE(phone, 'no phone') AS phone,
       IIF(age >= 40, 'senior', 'regular') AS tier
FROM customers;

name  | phone            | tier
------+-----------------+--------
Ada   | +44 20 7946 0958 | regular
Linus | no phone         | regular
Grace | +1 202 555 0143  | senior

Key takeaways

• CASE is an expression, so it works in SELECT, WHERE, ORDER BY and UPDATE.
• First matching branch wins; no ELSE means NULL.
• SUM(CASE ...) turns rows into columns — conditional aggregation.
• COALESCE and NULLIF are the portable NULL tools.

-- Write your solution here