SQL CASE WHEN: The Complete Guide With Examples (2026)

Simple CASE:

SELECT case_id, status,
  CASE status
    WHEN 'open'   THEN 'Active Investigation'
    WHEN 'closed' THEN 'Case Resolved'
    WHEN 'cold'   THEN 'Inactive'
    ELSE 'Unknown'
  END AS status_label
FROM crime_cases;

Simple CASE checks status against each WHEN value using equality (=). Clean and readable for straightforward lookups.

Searched CASE:

SELECT suspect_id, name, prior_offenses,
  CASE
    WHEN prior_offenses > 5 THEN 'High Risk'
    WHEN prior_offenses > 2 THEN 'Medium Risk'
    WHEN prior_offenses > 0 THEN 'Low Risk'
    ELSE 'Clean Record'
  END AS risk_level
FROM suspects;

Searched CASE evaluates each WHEN as a standalone condition. Supports ranges, comparisons, LIKE, IN, IS NULL, and anything else you can put in a WHERE clause.

Order matters. Watch this:

-- ❌ WRONG: General condition catches everything first
SELECT name, prior_offenses,
  CASE
    WHEN prior_offenses > 0 THEN 'Some Risk'
    WHEN prior_offenses > 5 THEN 'High Risk'  -- Never reached!
  END AS risk_level
FROM suspects;

-- ✅ RIGHT: Specific conditions first
SELECT name, prior_offenses,
  CASE
    WHEN prior_offenses > 5 THEN 'High Risk'
    WHEN prior_offenses > 0 THEN 'Some Risk'
    ELSE 'Clean Record'
  END AS risk_level
FROM suspects;

In the wrong version, a suspect with 8 prior offenses matches > 0 first and gets "Some Risk" instead of "High Risk." Always put specific conditions before general ones.

Full example:

SELECT
  case_id,
  location,
  victim_count,
  CASE
    WHEN victim_count > 3 THEN 'Critical'
    WHEN victim_count > 1 THEN 'Major'
    ELSE 'Minor'
  END AS severity
FROM crime_scenes
ORDER BY victim_count DESC;

Each row gets a severity label based on victim_count. Always use AS alias after END to name the computed column.

Conditional SUM example:

SELECT district,
  SUM(CASE WHEN status = 'closed' THEN evidence_count ELSE 0 END) AS solved_evidence,
  SUM(CASE WHEN status = 'open' THEN evidence_count ELSE 0 END) AS unsolved_evidence
FROM crime_cases
GROUP BY district;

SUM with CASE lets you sum different subsets in the same query. Use ELSE 0 with SUM to avoid NULL in the totals.

Custom sort with ORDER BY CASE:

-- Show armed suspects first, then by name
SELECT name, status
FROM suspects
ORDER BY
  CASE WHEN status = 'armed' THEN 0
       WHEN status = 'wanted' THEN 1
       ELSE 2
  END,
  name ASC;

CASE in ORDER BY lets you define a custom priority. Armed suspects appear first regardless of alphabetical order.

Group by computed categories:

SELECT
  CASE
    WHEN date_opened >= '2025-01-01' THEN 'Recent'
    WHEN date_opened >= '2020-01-01' THEN 'Older'
    ELSE 'Cold Case'
  END AS case_age,
  COUNT(*) AS total
FROM crime_cases
GROUP BY
  CASE
    WHEN date_opened >= '2025-01-01' THEN 'Recent'
    WHEN date_opened >= '2020-01-01' THEN 'Older'
    ELSE 'Cold Case'
  END;

The CASE expression must be repeated in both SELECT and GROUP BY (or use a subquery/CTE to avoid repetition).

CASE with NULL handling:

-- CASE for explicit NULL handling
SELECT name, phone,
  CASE
    WHEN phone IS NULL THEN 'No phone on file'
    ELSE phone
  END AS contact_phone
FROM suspects;

-- COALESCE is a shortcut for this exact pattern
SELECT name, COALESCE(phone, 'No phone on file') AS contact_phone
FROM suspects;

COALESCE returns the first non-NULL value. When your CASE just replaces NULL with a default, use COALESCE instead.

-- ❌ General before specific
CASE WHEN score > 0 THEN 'Has Score'
     WHEN score > 100 THEN 'High Score'  -- Never reached
END

-- ✅ Specific before general
CASE WHEN score > 100 THEN 'High Score'
     WHEN score > 0 THEN 'Has Score'
END

-- ❌ No ELSE: unmatched rows return NULL
CASE WHEN status = 'open' THEN 'Active'
     WHEN status = 'closed' THEN 'Resolved'
END  -- What about 'cold' status? Returns NULL!

-- ✅ Always include ELSE unless you want NULL
CASE WHEN status = 'open' THEN 'Active'
     WHEN status = 'closed' THEN 'Resolved'
     ELSE 'Other'
END