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 orders by size
SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 10000 THEN 'Enterprise'
    WHEN amount >= 1000  THEN 'Mid-Market'
    WHEN amount >= 100   THEN 'Small Business'
    ELSE 'Micro'
  END AS order_tier
FROM orders;

-- Order matters: a $15,000 order 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 VIP orders for expedited shipping
SELECT
  order_id,
  customer_type,
  amount,
  CASE
    WHEN customer_type = 'VIP' AND amount >= 5000
      THEN 'Priority Ship'
    WHEN customer_type = 'VIP' AND amount < 5000
      THEN 'Standard Ship'
    WHEN amount >= 5000
      THEN 'Expedited Ship'
    ELSE 'Standard Ship'
  END AS shipping_tier
FROM orders;

-- Row with VIP + $8,000: matches first clause
-- Row with VIP + $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
  account_id,
  balance,
  status,
  last_activity_date,
  CASE
    WHEN balance < 0 OR status = 'suspended'
      THEN 'Immediate Review'
    WHEN balance < 100 OR last_activity_date < CURRENT_DATE - 90
      THEN 'Routine Review'
    ELSE 'No Action'
  END AS review_action
FROM accounts;

-- Negative balance OR suspended status: same action needed
-- Low balance OR inactive for 90 days: 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 customer type and volume
SELECT
  customer_id,
  customer_type,
  units_ordered,
  unit_price,
  CASE
    WHEN customer_type = 'Enterprise' THEN
      CASE
        WHEN units_ordered >= 1000 THEN unit_price * 0.70
        WHEN units_ordered >= 100  THEN unit_price * 0.85
        ELSE unit_price * 0.90
      END
    WHEN customer_type = 'SMB' THEN
      CASE
        WHEN units_ordered >= 500 THEN unit_price * 0.90
        ELSE unit_price * 0.95
      END
    ELSE unit_price
  END AS final_price
FROM order_lines;

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 status = 'active' THEN salary ELSE 0 END) AS active_payroll,
  AVG(CASE
    WHEN tenure_years >= 5 AND performance_rating >= 4
    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