SQL CASE Statement Examples — Conditional Logic in SELECT

SQL is a declarative language, but you still need if/else logic constantly — labeling an order as "shipped" or "pending", grouping customers into age bands, or converting a numeric score into a letter grade. The CASE WHEN expression is how SQL does conditional logic inside a query. This guide covers the syntax and walks through practical, copy-paste-ready examples.

CASE WHEN Syntax

A CASE expression checks a list of conditions in order and returns the value for the first one that matches. If none match, it falls back to the optional ELSE branch, or NULL if ELSE is omitted:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

SQL also supports a shorter "simple CASE" form when you are comparing one expression against exact values:

-- Simple CASE: compares order_status directly
CASE order_status
  WHEN 'P' THEN 'Pending'
  WHEN 'S' THEN 'Shipped'
  WHEN 'D' THEN 'Delivered'
  ELSE 'Unknown'
END AS status_label

Example 1: Age Bands

The "searched CASE" form supports ranges and comparisons, which is what you need for bucketing continuous values like age:

SELECT
  customer_name,
  age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 25 THEN '18-25'
    WHEN age BETWEEN 26 AND 40 THEN '26-40'
    WHEN age BETWEEN 41 AND 60 THEN '41-60'
    ELSE '60+'
  END AS age_band
FROM customers;

Conditions are checked top to bottom and stop at the first match, so put the most specific or most restrictive condition first when ranges could overlap.

Example 2: Order Status Labels

SELECT
  order_id,
  order_total,
  shipped_date,
  CASE
    WHEN shipped_date IS NOT NULL THEN 'Shipped'
    WHEN order_total = 0 THEN 'Cancelled'
    WHEN created_at < NOW() - INTERVAL '3 day' THEN 'Delayed'
    ELSE 'Processing'
  END AS status_label
FROM orders;

-- MySQL date arithmetic equivalent:
-- WHEN created_at < NOW() - INTERVAL 3 DAY THEN 'Delayed'

Example 3: Grading Scores

SELECT
  student_name,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS letter_grade
FROM exam_results
ORDER BY score DESC;

Example 4: Conditional Aggregation with SUM and CASE

Combining CASE with an aggregate function lets you compute multiple conditional totals in a single query, avoiding several separate queries:

SELECT
  region,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders,
  SUM(CASE WHEN status = 'paid' THEN order_total ELSE 0 END) AS paid_revenue
FROM orders
GROUP BY region
ORDER BY paid_revenue DESC;

This "pivot with CASE" pattern turns row values into separate summary columns and is one of the most common uses of CASE in reporting queries.

Using CASE in WHERE and ORDER BY

• CASE can drive custom sort order — put priority statuses first regardless of alphabetical order
• CASE can appear in WHERE, though a plain condition is usually simpler and more index-friendly
• CASE cannot span multiple SELECT statements — each query needs its own CASE expression

-- Custom sort: show 'Urgent' orders first, then 'Normal', then 'Low'
SELECT order_id, priority, order_total
FROM orders
ORDER BY
  CASE priority
    WHEN 'Urgent' THEN 1
    WHEN 'Normal' THEN 2
    WHEN 'Low' THEN 3
    ELSE 4
  END,
  order_total DESC;

Frequently Asked Questions