CASE evaluates conditions and returns different values based on those conditions. When used with JOINed data, it can classify or transform combined records.
Business reports often require categories such as High, Medium, Low, Active, Inactive, or Risk levels.
Classify customers based on their total sales.
SELECT
c.CustomerName,
SUM(o.OrderAmount) AS TotalSales,
CASE
WHEN SUM(o.OrderAmount) >= 100000 THEN 'High'
WHEN SUM(o.OrderAmount) >= 50000 THEN 'Medium'
ELSE 'Low'
END AS CustomerSegment
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerName;Used in customer segmentation, financial reporting, risk analysis, and dashboard preparation.
- CASE can classify JOINed data.
- Conditions are evaluated in order.
- CASE can be combined with aggregates.
- Business rules can be represented directly in SQL.
- Always define sensible ELSE logic.
Why use CASE with JOINs?
CASE can classify or transform data after information from multiple tables has been combined.
Can CASE use aggregate expressions?
Yes. CASE can evaluate aggregate expressions such as SUM or COUNT when used in the appropriate query context.