← All Kits · SQL Kit

The SQL CASE Expression, Explained for Beginners

SQL's if/else, taught on real messy data · Part of the Analyst Prep Kit

Sooner or later every analyst meets a query full of WHEN and THEN and wonders what they're looking at. That construct is the CASE expression — SQL's if/else — and it's one of the most-used tools in real analyst work: cleaning messy values, bucketing numbers into bands, building flags, and counting several things in one query. This guide explains how it thinks, the one rule that silently breaks it, and how a real cleaning rule gets built on real data.

What CASE is and how it decides

CASE produces one value per row by checking a list of tests top to bottom:

CASE
  WHEN <test 1> THEN <value 1>
  WHEN <test 2> THEN <value 2>
  ELSE <fallback value>
END

The first test that passes wins — its THEN value becomes the result and everything below is skipped. If nothing passes, the ELSE value is used (no ELSE means the result is NULL). This multi-branch form is officially called a searched CASE expression; analysts informally call the shape a CASE ladder — a stack of WHEN/THEN rungs. If you know other languages, it's an if / else-if / else chain, or a switch statement.

The rule that matters: order is logic

Because the first matching rung wins, the order of the rungs changes the answer. Two rungs can each be individually correct and still give wrong results if the general one sits above the specific one. The rule that follows:

Specific patterns go above the general patterns they contain.

Abstract, until you watch it bite on real data:

2 Chainz Featuring Drake
Elvis Presley With The Jordanaires
2Pac Duet With Mopreme
Patti Austin A Duet With James Ingram

CASE
  WHEN INSTR(performer, ' Featuring ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' Featuring ') - 1)
  WHEN INSTR(performer, ' With ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' With ') - 1)
  ELSE performer
END

CASE
  WHEN INSTR(performer, ' A Duet With ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' A Duet With ') - 1)
  WHEN INSTR(performer, ' Duet With ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' Duet With ') - 1)
  WHEN INSTR(performer, ' Featuring ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' Featuring ') - 1)
  WHEN INSTR(performer, ' With ') > 0
    THEN SUBSTR(performer, 1, INSTR(performer, ' With ') - 1)
  ELSE performer
END AS primary_artist

The four jobs analysts use CASE for

1. Cleaning and normalizing

Mapping messy source values onto clean ones — the Billboard ladder above. Any dataset where the same real-world thing appears under several spellings ends up needing one of these.

2. Bucketing

Turning a continuous number into named bands:

CASE
  WHEN age < 18 THEN 'minor'
  WHEN age < 65 THEN 'adult'
  ELSE 'senior'
END AS age_band

Revenue tiers, engagement bands, rating groups — all this shape. Note it's another order-matters ladder: age < 65 would also match a 10-year-old, but the more specific age < 18 above it catches them first.

3. Flags

A yes/no column computed from a test: CASE WHEN peak_position <= 10 THEN 1 ELSE 0 END AS top10_hit. Flags make later filtering, joining, and counting simple.

4. Conditional aggregation (the pivot trick)

CASE inside SUM or COUNT counts different things in one pass over the table:

SELECT
  SUM(CASE WHEN genre = 'Country' THEN 1 ELSE 0 END) AS country_songs,
  SUM(CASE WHEN genre = 'Pop'     THEN 1 ELSE 0 END) AS pop_songs
FROM songs;

One query, several tailored counts side by side — the workhorse behind most summary tables and dashboard tiles.

How to build a CASE rule that's actually correct