What is the CASE expression? | Interview Question

Q: What is the CASE expression?

**Target Audience:** Beginners who can write SELECT, WHERE and GROUP BY and need "if this, then that" logic inside a query. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; SQL Server and MySQL notes are marked with their source. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output and error messages were produced by actually running the queries; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A post office sorts parcels with a simple card taped to the wall: 1. Heavier than 20 kg? Freight bay. 2. Heavier than 2 kg? Parcel shelf. 3. Anything else: letter tray. The clerk reads the card **from the top** and stops at the first line that fits. A 25 kg crate is "heavier than 2 kg" too, but it never gets that far: line 1 already s

The problem

Question presented to candidate: "SQL has no if statement inside a query, yet reports constantly need 'if this then that'. Explain the CASE expression: its two forms, how it decides which branch wins, what happens when nothing matches, and show me two or three places you would use it."

What a strong answer should cover:

CASE is an expression that returns one value per row, usable anywhere a value is: SELECT, WHERE, ORDER BY, GROUP BY, inside aggregates, in UPDATE ... SET
The searched form (CASE WHEN condition THEN ...) and the simple form (CASE expr WHEN value THEN ...); WHEN branches are tested top to bottom and the first true one wins
No ELSE means NULL when nothing matches; the simple form compares with =, so WHEN NULL never matches and you need WHEN x IS NULL
Common uses: bucketing values, custom sort orders, conditional aggregation (pivot-style counts), guarding division by zero
Engine details: all branches must have compatible types in PostgreSQL (SQLite does not care); PostgreSQL may evaluate a constant in an unused branch at plan time; SQLite has iif(), PostgreSQL does not

Code / implementation expected: Yes — a searched CASE that buckets a value, ideally also a CASE inside SUM or COUNT.

Schema used by the examples:

case

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who can write SELECT, WHERE and GROUP BY and need "if this, then that" logic inside a query. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; SQL Server and MySQL notes are marked with their source.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. All output and error messages were produced by actually running the queries; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A post office sorts parcels with a simple card taped to the wall:

Heavier than 20 kg? Freight bay.
Heavier than 2 kg? Parcel shelf.
Anything else: letter tray.

The clerk reads the card from the top and stops at the first li

The solution is hidden so you can try first

11 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSearched CASE: bucket order sizes, and see why branch order matters

SQLSimple CASE, the missing ELSE, and the WHEN NULL trap

SQLCASE in ORDER BY (custom sort) and inside aggregates (conditional counts)

SQLCASE in UPDATE: change several values in one statement

PostgreSQL: CASE guards a division that would otherwise raise division by zero

SELECT id, amount, items,
       CASE WHEN items = 0 THEN NULL ELSE amount / items END AS per_item
FROM orders
ORDER BY id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.