Recipe

CASE with multiple conditions

Short answer: CASE has two forms: simple (matches one column to literals) and searched (full predicates per branch). Use the searched form for any condition more complex than equality: ranges, multiple columns, AND / OR, IS NULL. Branches evaluate top-to-bottom and the first matching branch wins. Always include ELSE to handle uncovered cases.

The two forms #

sql

-- Simple form: column matched against literals (equality only).
SELECT
  id,
  status,
  CASE status
    WHEN 'paid'      THEN 'completed'
    WHEN 'shipped'   THEN 'completed'
    WHEN 'pending'   THEN 'in flight'
    ELSE                  'other'
  END AS status_group
FROM orders;

-- Searched form: full predicates — any expression goes in WHEN.
SELECT
  id,
  status,
  CASE
    WHEN status IN ('paid', 'shipped')                                 THEN 'completed'
    WHEN status = 'pending' AND created_at < CURRENT_DATE - INTERVAL '7 days' THEN 'stale'
    WHEN status = 'pending'                                            THEN 'in flight'
    WHEN created_at IS NULL                                            THEN 'missing data'
    ELSE                                                                    'other'
  END AS status_group
FROM orders;

Example #

Loading SQL editor...

Common patterns #

sql

-- Bucket numeric ranges (price tiers, age groups).
CASE
  WHEN total <  50  THEN 'small'
  WHEN total < 200  THEN 'medium'
  WHEN total < 1000 THEN 'large'
  ELSE                   'enterprise'
END

-- Cross-column conditions.
CASE
  WHEN region = 'US' AND plan = 'pro'  THEN 'us-pro'
  WHEN region = 'US' AND plan = 'free' THEN 'us-free'
  WHEN region = 'EU'                    THEN 'eu-' || plan
  ELSE                                       'other'
END

-- "Coalesce-like" choosing between columns.
CASE
  WHEN custom_email IS NOT NULL THEN custom_email
  WHEN work_email   IS NOT NULL THEN work_email
  ELSE personal_email
END
-- Equivalent (and shorter): COALESCE(custom_email, work_email, personal_email)

-- Inside aggregates: count or sum conditionally.
SELECT
  region,
  SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)        AS paid_revenue,
  COUNT(CASE WHEN total > 1000 THEN 1 END)                    AS big_orders   -- NULL skipped
FROM orders
GROUP BY region;

-- Replace deprecated values.
SELECT
  CASE old_status
    WHEN 'pending'    THEN 'in_review'
    WHEN 'pending2'   THEN 'in_review'
    WHEN 'on_hold'    THEN 'in_review'
    WHEN 'archived'   THEN 'closed'
    ELSE old_status                          -- pass-through
  END AS new_status
FROM legacy_orders;

Common mistakes #

Order-of-evaluation mistake: WHEN total > 100 THEN 'medium' before WHEN total > 1000 THEN 'large' means every row > 100 (including > 1000) hits the first branch. Order narrowest-to-widest, or use range bounds (100 < total < 1000).
Forgetting ELSE: rows that don't match any WHEN return NULL. Make this explicit with ELSE NULL or, better, ELSE 'unknown'.
Type mismatch across branches: THEN 1 mixed with THEN 'unknown' errors on most engines (can't reconcile to a single type). Cast or rephrase.
Equality with NULL: WHEN col = NULL never matches. Use WHEN col IS NULL — see NULL comparison.
Massively long CASE: at 10+ branches, you have business logic embedded in SQL. Move to a lookup table joined into the query, where adding a new value is a row insert, not a SQL change.

Dialect notes #

CASE is ANSI standard, identical syntax across every major engine.
MySQL also has IF(cond, then, else) as a 2-branch shortcut: IF(status = 'paid', 'done', 'pending') is equivalent to CASE WHEN status = 'paid' THEN 'done' ELSE 'pending' END. Not portable; CASE is preferred.
SQL Server has IIF as a similar shortcut: IIF(status = 'paid', 'done', 'pending').
CASE in ORDER BY and GROUP BY works on every engine — useful for custom sorts and conditional grouping.
Postgres FILTER clause: COUNT(*) FILTER (WHERE status = 'paid') is cleaner than SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END). Same result, less noise.

Tip

Once a CASE has more than ~6 branches, refactor. A long CASE chain encodes business rules that probably belong in a configuration table — status_mapping(old_status, new_status) joined into the query. Benefits: rules become data (editable without SQL changes), the same mapping is reusable across queries, and a missing case is a missing row instead of a silent NULL from the ELSE. The CASE is the right tool for inline conditional logic; for taxonomy and mapping, use a join. Detail: CASE.