CASE Expression
In this page:
Simple CASE Expression
A simple CASE expression compares one value against a list of options, similar to a switch statement. It acts like a switch statement in programming, matching the expression against each WHEN value in turn until one succeeds.
Example: Simple CASE Expression
CREATE TABLE orders (id INT, status TEXT);
INSERT INTO orders VALUES (1, 'P'), (2, 'S'), (3, 'C');
SELECT id, CASE status
WHEN 'P' THEN 'Pending'
WHEN 'S' THEN 'Shipped'
WHEN 'C' THEN 'Cancelled'
ELSE 'Unknown'
END AS status_label
FROM orders;Searched CASE Expression
A searched CASE expression evaluates complex logical conditions for each option instead of comparing a single value. It allows range checks and multiple operators, like grading scores into letter grades based on numeric ranges.
Example: Searched CASE Expression
CREATE TABLE scores (id INT, score INT);
INSERT INTO scores VALUES (1, 95), (2, 72), (3, 50);
SELECT id, CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 70 THEN 'B'
ELSE 'F'
END AS grade
FROM scores;Using CASE with Math Operations
You can include CASE inside math calculations to guard against invalid operations. This is useful for avoiding errors like division by zero, by returning NULL or a default instead of letting the calculation fail.
Example: Using CASE with Math Operations
CREATE TABLE items (id INT, total INT, quantity INT);
INSERT INTO items VALUES (1, 100, 5), (2, 50, 0);
SELECT id, CASE WHEN quantity = 0 THEN NULL ELSE total / quantity END AS unit_price
FROM items;Nested CASE Expressions
You can nest CASE expressions inside other CASE expressions for multi-level decision logic. This helps handle hierarchical check requirements, though deeply nested CASE statements can get hard to read and may be worth refactoring.
Example: Nested CASE Expressions
CREATE TABLE users (id INT, role TEXT, active INT);
INSERT INTO users VALUES (1, 'admin', 1), (2, 'admin', 0), (3, 'member', 1);
SELECT id, CASE
WHEN role = 'admin' THEN CASE WHEN active = 1 THEN 'Active Admin' ELSE 'Inactive Admin' END
ELSE 'Member'
END AS label
FROM users;CASE in SELECT with Aggregations
Using CASE inside aggregate functions like SUM allows you to perform conditional counts and custom calculations, such as summing only the orders marked paid within a single query instead of running separate filtered queries.
Example: CASE in SELECT with Aggregations
CREATE TABLE orders (id INT, status TEXT, total INT);
INSERT INTO orders VALUES (1, 'paid', 100), (2, 'unpaid', 50), (3, 'paid', 75);
SELECT SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_total
FROM orders;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: