The sales report needs a "Performance" column: orders above £1000 are "High", between £300-1000 are "Medium", below £300 are "Low". This column does not exist in the database. CASE WHEN creates it on the fly, in the query itself.

Simple vs searched CASE

Simple CASE compares one expression to multiple values. Searched CASE evaluates independent conditions. Use searched CASE for ranges and complex conditions.

-- Simple CASE (equality checks on one column)
CASE Region
  WHEN 'North' THEN 'N'
  WHEN 'South' THEN 'S'
  ELSE 'Other'
END

-- Searched CASE (independent conditions)
CASE
  WHEN Revenue > 1000 THEN 'High'
  WHEN Revenue > 300 THEN 'Medium'
  ELSE 'Low'
END AS Performance
💡
Analyst tip: CASE WHEN is evaluated top to bottom and stops at the first true condition. Put your most restrictive conditions first (highest value threshold first for range buckets).

CASE inside aggregate functions

This is the most powerful CASE WHEN pattern — pivoting rows into columns without a PIVOT clause.

SELECT
  Salesperson,
  SUM(CASE WHEN Region = 'North' THEN Revenue ELSE 0 END) AS NorthRevenue,
  SUM(CASE WHEN Region = 'South' THEN Revenue ELSE 0 END) AS SouthRevenue
FROM SalesFact
GROUP BY Salesperson;

CASE in ORDER BY

Custom sort orders — when alphabetical or numeric is not what you want.

ORDER BY
  CASE Category
    WHEN 'Electronics' THEN 1
    WHEN 'Furniture' THEN 2
    ELSE 3
  END
⚠️
Common mistake: Forgetting ELSE in a CASE expression. Without ELSE, unmatched rows return NULL. Always include ELSE — even if it is just ELSE NULL — to make the intent explicit.
🎯 Your Challenge

Write a query that classifies each order as 'Premium' (revenue above 2000), 'Standard' (500-2000), or 'Entry' (below 500). Then count how many orders fall in each tier using GROUP BY on your CASE expression.

FAQ

Yes — CASE WHEN is the standard SQL equivalent of IF. Some databases also have IIF() (SQL Server) or IF() (MySQL) as shorthand for two-branch conditions, but CASE WHEN is universally supported and more readable for anything beyond a simple true/false.
Yes. CASE WHEN in WHERE is valid but can usually be replaced with simpler AND/OR conditions. Use it when the condition itself is complex or when you need to compute a value in the filter: WHERE CASE WHEN Category = 'Electronics' THEN Revenue ELSE 0 END > 500.
`