Lesson Description
Use CASE WHEN to translate raw values into business-ready categories and labels. This lesson helps you express decision logic directly in SQL for cleaner reports and clearer analysis.
Lesson 05
Lesson Description
Use CASE WHEN to translate raw values into business-ready categories and labels. This lesson helps you express decision logic directly in SQL for cleaner reports and clearer analysis.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
So far every column you have produced either came straight from the table or was a single calculation applied the same way to every row. Real reports often need a rule that depends on the row. The store's merchandising team might ask: "Label every product as Budget, Standard or Premium based on its price." The hospital might want appointment statuses shown as friendly words on a patient portal. Neither label exists in the data. You have to derive it, row by row, from conditions.
CASE is SQL's if/else. It checks conditions in order and returns the value attached to the first one that is true. Because it is an expression, it can go anywhere a value can go: in SELECT, WHERE, ORDER BY, and inside aggregate functions. It is the last building block of this course, and it combines everything you have learned so far.
There are two forms. The searched form takes any condition, just like a WHERE clause:
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
ELSE fallback_result
END
The simple form compares one expression against a list of values with =:
CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
ELSE fallback_result
END
Rules that apply to both:
WHEN branches are checked top to bottom, and the first true branch wins. Later branches are not checked.ELSE is optional. If no branch matches and there is no ELSE, the result is NULL.CASE ends with END. Give the result an alias with AS, or PostgreSQL will name the column case.SELECT
name,
price,
CASE
WHEN price < 25 THEN 'Budget'
WHEN price < 100 THEN 'Standard'
ELSE 'Premium'
END AS price_band
FROM products
ORDER BY price DESC;
All 27 products come back with a new price_band column. The 4K Monitor (349), Espresso Machine (299) and Wireless Headphones (199.99) are at the top, labeled Premium. Across the whole table you get 5 Budget, 16 Standard and 6 Premium products.
Look at the second branch: WHEN price < 100 does not say price >= 25. It does not need to, because any product under 25 already matched the first branch and stopped there. Ordering branches from the narrowest bucket to the widest lets you write short conditions. It also means the order is part of the logic, as you will see in Common mistakes.
Conditions can compare dates just as easily. Classify hospital doctors by when they joined:
SELECT
first_name,
last_name,
hire_date,
CASE
WHEN hire_date < '2016-01-01' THEN 'Veteran'
WHEN hire_date < '2020-01-01' THEN 'Established'
ELSE 'Recent hire'
END AS tenure_group
FROM doctors
ORDER BY hire_date;
This returns 15 doctors: 4 Veterans (Olivia Bennett through Sarah Johnson), 8 Established (Yuki Tanaka through Marcus Wright), and 3 Recent hires (Tomás García, Hassan Ali and Frank O'Brien, all 2020 or later).
When you are mapping exact values to labels, the simple form reads more cleanly. Patient gender is stored as a single letter:
SELECT
first_name,
last_name,
CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Not recorded'
END AS gender_label
FROM patients;
John Anderson shows Male, Mary Williams shows Female, and so on for all 30 patients. Every row in the sandbox has M or F, so the ELSE never fires here, but keeping it is good defensive practice: if a new code like 'X' arrives, you get a clear label instead of a silent NULL.
Leave out ELSE when you only want to flag some rows. The purchasing team wants a stock alert:
SELECT
name,
stock,
CASE
WHEN stock < 20 THEN 'Reorder now'
WHEN stock < 50 THEN 'Running low'
END AS stock_alert
FROM products
ORDER BY stock;
The Espresso Machine (8), 4K Monitor (12) and Tennis Racket (18) say "Reorder now". Eight products between 20 and 49 units say "Running low". The remaining 16 products have 50 or more units, match no branch, and get NULL in stock_alert. That is often what you want, since a blank cell is easy to scan past. Just be aware of it before you filter or count on that column later.
Because CASE is an expression, you can sort by it. The hospital front desk wants upcoming work first: scheduled appointments, then no-shows to follow up, then cancellations, then completed visits.
SELECT id, appointment_date, status
FROM appointments
ORDER BY
CASE status
WHEN 'scheduled' THEN 1
WHEN 'no-show' THEN 2
WHEN 'cancelled' THEN 3
ELSE 4
END,
appointment_date;
All 66 appointments come back. The first 20 rows are the scheduled ones, starting with appointment 31 on 2025-04-08. Then the 2 no-shows, then the 1 cancellation, then the 43 completed visits. Alphabetical sorting could never produce this order; the CASE turns each status into a rank number, and ORDER BY sorts on that number. appointment_date breaks ties inside each group.
You will meet aggregate functions in the Aggregations course, but here is a preview of a pattern you will use constantly. Put a CASE inside SUM to count rows that meet a condition:
SELECT
SUM(CASE WHEN price >= 100 THEN 1 ELSE 0 END) AS premium_products,
COUNT(*) FILTER (WHERE price < 25) AS budget_products
FROM products;
One row comes back: 6 premium and 5 budget products. The FILTER clause is PostgreSQL's cleaner way to do the same thing.
CASE is evaluated once per row, at the point where it appears. In SELECT that is after WHERE has filtered the rows; in ORDER BY it is during sorting.WHEN. In general it also does not evaluate the THEN results of branches it did not pick, which is why CASE WHEN qty = 0 THEN NULL ELSE total / qty END is a common way to avoid division by zero.THEN and ELSE results must share one data type. PostgreSQL picks a common type from the branches, and if it cannot convert one of them, the query fails.CASE x WHEN v is shorthand for x = v. Since NULL = NULL is unknown, not true, a simple CASE manager_id WHEN NULL THEN ... never matches. Use the searched form with WHEN manager_id IS NULL.CASE
WHEN price < 100 THEN 'Standard'
WHEN price < 25 THEN 'Budget'
ELSE 'Premium'
END
This runs without error and gives 21 Standard, 6 Premium and 0 Budget products. A 12.50 jump rope is less than 100, so it matches the first branch and never reaches the second. Put narrower conditions first, or write each branch with both bounds (price >= 25 AND price < 100) so order no longer matters.
SELECT name,
CASE WHEN stock < 20 THEN 'Reorder' ELSE stock END
FROM products;
PostgreSQL fails with invalid input syntax for type integer: "Reorder". One branch is text and the other is an integer, and PostgreSQL tried to make everything an integer. Make both branches text, for example ELSE stock::text, or return the label in one column and the number in another.
CASE manager_id WHEN NULL THEN 'Top of org' ELSE 'Reports to someone' END labels Steven King, who has no manager, as "Reports to someone". Switch to CASE WHEN manager_id IS NULL THEN 'Top of org' ELSE 'Reports to someone' END.
Without AS, the output column is literally named case. That is confusing in a report and will fail any check that expects a specific column name, including the grader on this site.
CASE is part of the SQL standard and works the same way in MySQL, SQL Server, SQLite and Oracle. Some databases add shortcuts: MySQL has IF(condition, a, b) and SQL Server has IIF(condition, a, b). PostgreSQL has neither, so CASE is the portable choice. The FILTER (WHERE ...) clause shown above is supported by PostgreSQL and SQLite but not by MySQL or SQL Server, where you use the SUM(CASE ...) pattern instead.
The exercises below this lesson ask you to label HR employees by salary tier, translate store order statuses into customer-friendly text, and group hospital patients by birth date. Each one names the exact labels and the output column to produce, so copy them carefully, and think about which branch has to come first.
CASE returns the result of the first WHEN that is true, checked from top to bottom.ELSE, unmatched rows get NULL.IS NULL; use the simple form for exact code-to-label mapping.CASE is an expression, so it works in SELECT, WHERE, ORDER BY and inside aggregates. Always alias it.CASE is SQL's if/else. It turns raw values into categories a reader understands (salary bands, order sizes, appointment outcomes) and is the building block for conditional counts and pivot-style reports.
Bucket salaries into bands
SELECT first_name, salary,
CASE
WHEN salary >= 15000 THEN 'High'
WHEN salary >= 7000 THEN 'Mid'
ELSE 'Entry'
END AS salary_band
FROM employees;Conditions are checked top to bottom and the first true one wins, so the order of WHEN clauses matters.
Conditional counts in one pass
SELECT COUNT(*) AS total,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM appointments;Each CASE turns a row into 1 or 0, and SUM adds them up, giving several counts from a single scan.
'High'. The first condition, salary >= 15000, is true, and CASE stops at the first match.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name, last_name, salary, and a tier column: 'Junior' if salary < 5000, 'Mid' if salary < 10000, otherwise 'Senior'. Order by salary ascending.
No results yet
Run a query to see results here
From the store sandbox, return id (alias order_id) and a status_label column that maps: 'shipped' → 'On the way', 'delivered' → 'Complete', 'cancelled' → 'Cancelled', anything else → 'Processing'. Order by id.
No results yet
Run a query to see results here
From the hospital sandbox, return first_name, last_name, date_of_birth, and an age_group column: 'Born after 1990' if dob > 1990-01-01, 'Born 1960–1990' if dob > 1960-01-01, otherwise 'Born before 1960'. Order by date_of_birth DESC.
No results yet
Run a query to see results here
Final checkpoint
Pass mark: 80%. Review each explanation after you submit.
1. Which clause should you use to filter grouped results?
2. What is the safest way to avoid accidental row multiplication in joins?
3. Which feature is best for ranking rows within partitions?
4. Why should final analytical queries usually include ORDER BY?
5. When query logic gets complex, what improves readability most?