CASE Expression

Write conditional logic inline in SQL using CASE WHEN ... THEN ... ELSE ... END.

Overview

CASE is SQL's conditional expression it works like an if/else statement inline in a query. There are two forms: the searched form (CASE WHEN condition THEN result ...) and the simple form (CASE col WHEN value THEN result ...). CASE can appear in SELECT, WHERE, ORDER BY, and anywhere else an expression is valid. CASE is extremely useful for transforming data on the fly bucketing numeric ranges into labels, mapping status codes to human-readable strings, or computing derived boolean flags without modifying the actual data.

Key Points

•Searched form: CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ELSE default END
•Simple form: CASE col WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE default END
•ELSE is optional returns NULL if no condition matches and ELSE is omitted
•Can be used in SELECT, WHERE, ORDER BY, and GROUP BY
•CASE evaluates conditions in order stops at first TRUE

Examples

Searched CASE conditional bucketing

SELECT
  name,
  price,
  CASE
    WHEN price < 10    THEN 'Budget'
    WHEN price < 50    THEN 'Mid-range'
    WHEN price < 200   THEN 'Premium'
    ELSE                    'Luxury'
  END AS price_tier
FROM products;

Simple CASE value mapping

SELECT
  order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    ELSE          'Unknown'
  END AS status_label
FROM orders;

CASE Expression

Overview

Key Points

Examples

Exercises

Knowledge Check

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