SQL CASE WHEN Multiple Conditions Guide

CASE evaluates WHEN clauses from top to bottom and returns the result for the first condition that is TRUE. Once a match is found, remaining clauses are skipped. This means the order of your WHEN clauses matters. Put the most specific or most restrictive conditions first. The ELSE clause handles any rows that do not match any WHEN condition.

-- Categorize transactions by size
SELECT
  transaction_id,
  total_amount,
  CASE
    WHEN total_amount >= 10000 THEN 'Enterprise'
    WHEN total_amount >= 1000  THEN 'Mid-Market'
    WHEN total_amount >= 100   THEN 'Small Business'
    ELSE 'Micro'
  END AS order_tier
FROM transactions;

-- Order matters: a $15,000 transaction matches the first clause
-- and is labeled 'Enterprise'. The second clause is never
-- checked for that row, even though $15,000 >= 1000 is true.

AND combines multiple conditions within a single WHEN clause. All conditions must be TRUE for the clause to match. This pattern handles business rules that depend on two or more factors simultaneously: an order is 'Priority' only if the amount is high AND the customer is VIP. Both conditions must hold.

-- Flag high-value orders from premium accounts for expedited shipping
SELECT
  t.transaction_id,
  u.account_status,
  t.total_amount,
  CASE
    WHEN u.account_status = 'premium' AND t.total_amount >= 5000
      THEN 'Priority Ship'
    WHEN u.account_status = 'premium' AND t.total_amount < 5000
      THEN 'Standard Ship'
    WHEN t.total_amount >= 5000
      THEN 'Expedited Ship'
    ELSE 'Standard Ship'
  END AS shipping_tier
FROM transactions t
JOIN users u ON u.user_id = t.user_id;

-- Row with premium + $8,000: matches first clause
-- Row with premium + $200: matches second clause
-- Row with regular + $6,000: matches third clause
-- Row with regular + $50: falls to ELSE

OR allows a single WHEN clause to match any of several conditions. The clause fires if at least one condition is TRUE. Use OR when multiple distinct scenarios should produce the same result. This reduces duplication compared to writing separate WHEN clauses that all return the same value.

-- Identify accounts needing review
SELECT
  user_id,
  account_status,
  signup_date,
  CASE
    WHEN account_status = 'closed' OR account_status = 'suspended'
      THEN 'Immediate Review'
    WHEN account_status = 'inactive' OR signup_date < '2020-01-01'
      THEN 'Routine Review'
    ELSE 'No Action'
  END AS review_action
FROM users;

-- Closed OR suspended status: same action needed
-- Inactive OR signed up before 2020: same review tier

A CASE expression can appear inside another CASE expression. The outer CASE handles the first decision. The inner CASE refines the result based on additional criteria. This pattern is useful when the logic has a natural hierarchy: first determine the category, then within that category determine the subcategory. Keep nesting to two levels maximum; deeper nesting becomes unreadable.

-- Tiered pricing based on account type and volume
SELECT
  oi.user_id,
  u.account_status,
  oi.quantity,
  oi.unit_price,
  CASE
    WHEN u.account_status = 'premium' THEN
      CASE
        WHEN oi.quantity >= 1000 THEN oi.unit_price * 0.70
        WHEN oi.quantity >= 100  THEN oi.unit_price * 0.85
        ELSE oi.unit_price * 0.90
      END
    WHEN u.account_status = 'active' THEN
      CASE
        WHEN oi.quantity >= 500 THEN oi.unit_price * 0.90
        ELSE oi.unit_price * 0.95
      END
    ELSE oi.unit_price
  END AS final_price
FROM order_items oi
JOIN users u ON u.user_id = oi.user_id;

Placing CASE WHEN inside COUNT, SUM, or AVG creates conditional aggregation. This computes different metrics from the same rows in a single pass. It replaces multiple filtered subqueries and is one of the most powerful reporting patterns in SQL. Combined with GROUP BY, it produces cross-tabulation reports.

-- Single query: multiple conditional metrics
SELECT
  department,
  COUNT(*) AS total_employees,
  COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS high_earners,
  SUM(CASE WHEN manager_id IS NOT NULL THEN salary ELSE 0 END) AS reports_payroll,
  AVG(CASE
    WHEN hire_date < '2020-01-01' AND salary >= 90000
    THEN salary
  END) AS avg_salary_senior_performers
FROM employees
GROUP BY department;

-- COUNT(CASE...) counts only rows where the condition is true
-- NULL results from unmatched CASE are ignored by COUNT
-- SUM uses ELSE 0 to avoid NULL in the sum