SQL CASE Expression – Conditional Logic in Queries

CASE Expression Syntax

There are two forms of the CASE expression. Both evaluate conditions top-to-bottom and return the result for the first matching branch. The ELSE clause is optional — if omitted and no branch matches, CASE returns NULL.

SQL

-- Simple CASE: compare one expression to multiple values
CASE column_or_expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

-- Searched CASE: evaluate arbitrary boolean conditions
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Simple CASE

Simple CASE compares a single expression to a list of values — like a switch/match statement in programming languages.

SQL

-- Map numeric status codes to readable labels
SELECT
  order_id,
  CASE status
    WHEN 1 THEN 'Pending'
    WHEN 2 THEN 'Processing'
    WHEN 3 THEN 'Shipped'
    WHEN 4 THEN 'Delivered'
    WHEN 5 THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

-- Map country codes to regions
SELECT
  customer_id,
  country_code,
  CASE country_code
    WHEN 'US' THEN 'North America'
    WHEN 'CA' THEN 'North America'
    WHEN 'GB' THEN 'Europe'
    WHEN 'DE' THEN 'Europe'
    WHEN 'JP' THEN 'Asia Pacific'
    ELSE 'Other'
  END AS region
FROM customers;

Searched CASE

Searched CASE evaluates a full boolean condition for each branch. More powerful than Simple CASE — use it when you need ranges, multiple columns, or complex logic.

SQL

-- Assign salary bands
SELECT
  employee_id,
  name,
  salary,
  CASE
    WHEN salary < 30000  THEN 'Entry Level'
    WHEN salary < 60000  THEN 'Mid Level'
    WHEN salary < 100000 THEN 'Senior'
    ELSE 'Executive'
  END AS salary_band
FROM employees;

-- Classify orders by size
SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount < 50    THEN 'Small'
    WHEN total_amount < 200   THEN 'Medium'
    WHEN total_amount < 1000  THEN 'Large'
    ELSE 'Enterprise'
  END AS order_size
FROM orders;

CASE in SELECT

The most common use of CASE: create a computed column based on row values. The result can be used in WHERE, ORDER BY, or GROUP BY by referencing the alias or repeating the expression.

SQL

-- Add discount tiers inline
SELECT
  product_name,
  price,
  CASE
    WHEN price > 500  THEN ROUND(price * 0.80, 2)
    WHEN price > 100  THEN ROUND(price * 0.90, 2)
    ELSE price
  END AS discounted_price,
  CASE
    WHEN price > 500  THEN '20% off'
    WHEN price > 100  THEN '10% off'
    ELSE 'No discount'
  END AS discount_label
FROM products;

-- Flag late orders
SELECT
  order_id,
  order_date,
  ship_date,
  CASE
    WHEN ship_date IS NULL                        THEN 'Not Shipped'
    WHEN ship_date > order_date + INTERVAL 3 DAY  THEN 'Late'
    ELSE 'On Time'
  END AS shipping_status
FROM orders;

CASE in ORDER BY

CASE inside ORDER BY enables custom sort sequences that are impossible with plain column ordering.

SQL

-- Custom priority order: Critical → High → Medium → Low
SELECT ticket_id, title, priority
FROM support_tickets
ORDER BY
  CASE priority
    WHEN 'Critical' THEN 1
    WHEN 'High'     THEN 2
    WHEN 'Medium'   THEN 3
    WHEN 'Low'      THEN 4
    ELSE 5
  END;

-- Put active records first, then sort by name
SELECT customer_id, name, status
FROM customers
ORDER BY
  CASE WHEN status = 'active' THEN 0 ELSE 1 END,
  name;

CASE in Aggregate Functions (Pivoting)

Combining CASE with SUM or COUNT is the standard SQL technique for pivoting rows into columns — creating a cross-tabulation without a PIVOT keyword.

SQL

-- Count orders by status in separate columns (pivot)
SELECT
  COUNT(*)                                     AS total_orders,
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- Monthly revenue by category in one row per month
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics,
  SUM(CASE WHEN category = 'Clothing'    THEN amount ELSE 0 END) AS clothing,
  SUM(CASE WHEN category = 'Books'       THEN amount ELSE 0 END) AS books
FROM order_items
JOIN products USING (product_id)
GROUP BY month
ORDER BY month;

-- Alternative: COUNT(CASE WHEN ...) returns 0 for no matches
-- because COUNT only counts non-NULL, and the WHEN returns NULL when false
SELECT
  COUNT(CASE WHEN score >= 90 THEN 1 END) AS grade_a,
  COUNT(CASE WHEN score >= 80 AND score < 90 THEN 1 END) AS grade_b,
  COUNT(CASE WHEN score < 80 THEN 1 END)  AS grade_c
FROM exam_results;

Summary

📋 Summary

Simple CASE — Compares one value to a list. Like a switch statement.
Searched CASE — Evaluates conditions in order. Returns first TRUE match.
ELSE is optional — Without ELSE, unmatched rows return NULL.
CASE in SELECT — Compute labels, tiers, flags, or derived columns.
CASE in ORDER BY — Create custom sort sequences for priorities or statuses.
CASE in SUM/COUNT — Pivot rows to columns. The core technique for cross-tabulation.
IIF() (SQL Server) — Shorthand: IIF(condition, true_val, false_val).
IF() (MySQL) — Shorthand: IF(condition, true_val, false_val).

FAQ

Does CASE short-circuit — does it stop evaluating after the first match?+

Yes, SQL CASE is guaranteed by the standard to evaluate conditions left-to-right and stop at the first TRUE condition. This matters when later conditions would cause errors — for example, CASE WHEN col IS NOT NULL WHEN col > 0 THEN 'positive' END safely avoids comparing NULL to 0.

Can I nest CASE expressions inside each other?+

Yes, CASE can be nested: CASE WHEN x > 10 THEN CASE WHEN y > 5 THEN 'High-High' ELSE 'High-Low' END ELSE 'Low' END. However, deeply nested CASE expressions become hard to read. Consider a flat searched CASE with compound conditions instead: CASE WHEN x > 10 AND y > 5 THEN 'High-High' ....

What is the difference between Simple CASE and Searched CASE?+

Simple CASE (CASE col WHEN val THEN ...) uses equality comparison only. Searched CASE (CASE WHEN condition THEN ...) evaluates any boolean expression — ranges, multiple columns, function calls, IS NULL. Simple CASE is more readable for exact value matching; Searched CASE is required for anything more complex.

Can I use CASE in a WHERE clause?+

Yes: WHERE CASE WHEN col IS NULL THEN 'unknown' ELSE col END = 'active'. However, this often prevents index usage. A more efficient alternative is usually: WHERE (col = 'active' OR col IS NULL). Use CASE in WHERE when the logic genuinely cannot be expressed with standard boolean operators.