CASE Expressions (Conditional Logic)

CASE is SQL's if/then/else. It lets a query make decisions — turning a raw score into a letter grade, a status code into a friendly label, or a number into a tier — all inside the query itself, without touching your application code.

Learn CASE Expressions (Conditional Logic) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn both the searched and simple forms, how to use CASE for custom sorting and conditional counting, and the rules that keep your branches predictable.

Four students and their exam scores — perfect raw material for turning numbers into grades and labels.

The searched form puts a full boolean condition after each WHEN . SQL checks them top to bottom and returns the value of the first branch that's true. The optional ELSE is the fallback, and END closes the expression.

CASE is a series of "if / else if / else" gates. The value falls through each WHEN until one catches it — exactly like a sorting machine dropping a parcel into the first matching bin.

When you're matching one expression against a set of exact values, the simple form is tidier. You write the expression once after CASE , then list WHEN value THEN result . It can't do ranges — for those, use searched CASE.

Fill in the keyword pair that introduces a branch and its result.

Because CASE is an expression, it works anywhere a value is expected. Two of the most useful spots: ORDER BY for a custom sort order, and inside SUM / COUNT for conditional totals — the classic "count how many passed" pattern.

Unscramble these CASE lines so they label a "band": 'Hot' for score ≥ 80, 'Warm' for ≥ 50, otherwise 'Cold'.

Why: branch order is everything. The >= 80 test must come before >= 50 , because a score of 90 also satisfies >= 50 — and the first true branch wins. ELSE catches everything below 50, and END closes the expression. Reversing the two WHENs would label every passing score 'Warm'.

Predict each result, then reveal. Use the students table.

B — 82 fails the >= 90 branch but passes >= 80 , which fires first and wins.

NULL — the missing ELSE means there's no fallback, so the expression yields NULL. Add an ELSE to avoid surprises.

C — 95 satisfies >= 70 , and since that branch now comes first, it wins before >= 90 is ever reached. Order your conditions most-specific first.

Use a searched CASE to label customers Gold / Silver / Bronze by total_spent . Expected shape is in the comments.

Practice quiz

What is CASE in SQL most like?

  • A loop
  • A join
  • If/then/else conditional logic
  • A table

Answer: If/then/else conditional logic. CASE is SQL's if/then/else — it lets a query return different values based on conditions.

How does a searched CASE differ from a simple CASE?

  • Searched evaluates a full boolean condition per WHEN; simple compares one expression to exact values
  • Searched matches one expression to exact values; simple uses full conditions
  • They are identical
  • Simple CASE handles ranges, searched does not

Answer: Searched evaluates a full boolean condition per WHEN; simple compares one expression to exact values. Searched CASE puts a full condition after each WHEN (handles ranges); simple CASE matches one expression to exact values.

When multiple WHEN branches are true, which one wins?

  • The last true branch
  • A random one
  • All of them combined
  • The first true branch, checked top to bottom

Answer: The first true branch, checked top to bottom. CASE evaluates WHEN branches top to bottom and returns the first one that is true.

What does a CASE return when no WHEN matches and there is no ELSE?

  • 0
  • NULL
  • An empty string
  • An error

Answer: NULL. With no matching WHEN and no ELSE, CASE returns NULL; add an ELSE as a catch-all.

Which CASE form best matches a status code to a fixed label, e.g. 'A' to 'Active'?

  • Simple CASE
  • Searched CASE
  • Neither works
  • A WHERE clause

Answer: Simple CASE. Simple CASE (CASE status WHEN 'A' THEN ...) is tidiest for exact-value matching.

Why must CASE WHEN score >= 90 come before CASE WHEN score >= 80?

  • Alphabetical order
  • It does not matter
  • Because 90 also satisfies >= 80, and the first true branch wins
  • 80 is invalid before 90

Answer: Because 90 also satisfies >= 80, and the first true branch wins. A score of 95 passes both tests; the most specific/highest condition must come first since the first match wins.

Where besides SELECT can a CASE expression be used?

  • Only in SELECT
  • In ORDER BY, WHERE, GROUP BY, and inside aggregates
  • Only in WHERE
  • Only in FROM

Answer: In ORDER BY, WHERE, GROUP BY, and inside aggregates. CASE is an expression, so it works anywhere a value is expected, including ORDER BY and SUM().

What does SUM(CASE WHEN score >= 70 THEN 1 ELSE 0 END) compute?

  • The average score
  • The highest score
  • The total of all scores
  • A count of rows where score >= 70

Answer: A count of rows where score >= 70. Each matching row contributes 1, so the SUM is a conditional count of passing rows.

What keyword must close every CASE expression?

  • STOP
  • END
  • DONE
  • CLOSE

Answer: END. Every CASE must close with END (usually followed by an alias); omitting it is a syntax error.

Why should all THEN/ELSE branches return the same data type?

  • For faster queries
  • It is just a style preference
  • Because a column has one type; mixing types can error or force conversion
  • To allow NULLs

Answer: Because a column has one type; mixing types can error or force conversion. A result column has a single type, so all branches should be type-compatible to avoid errors or coercion.