CASE Expression

CASE is like a set of if-then signs on a sorting machine: if the score is high it says A, if it is medium it says B, otherwise C.

In this page:

Simple CASE Expression
Searched CASE Expression
Using CASE with Math Operations
Nested CASE Expressions
CASE in SELECT with Aggregations

Syntax

sql

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

CASE column_name
  WHEN value1 THEN result1
  ELSE default_result
END

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

sql

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

sql

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

sql

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

sql

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

sql

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:

← Previous Lesson Logical Operators Next Lesson → IFNULL & COALESCE