CASE provides conditional logic in SQL. When used with JOIN, it can classify related records based on values from one or more tables.
Business reports often require categories such as High Value, Medium Value, and Low Value based on joined data.
Classify customers based on their total order value.
SELECT
c.CustomerName,
SUM(o.OrderAmount) AS TotalSales,
CASE
WHEN SUM(o.OrderAmount) >= 100000 THEN 'High Value'
WHEN SUM(o.OrderAmount) >= 50000 THEN 'Medium Value'
ELSE 'Low Value'
END AS CustomerCategory
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;CASE with JOIN is useful for customer segmentation, risk classification, employee grading, sales categories, and business rule implementation.
- CASE adds conditional logic.
- CASE can use values from joined tables.
- CASE can work with aggregate results.
- It is useful for business classifications.
- Multiple conditions can be evaluated in order.
Can CASE use columns from joined tables?
Yes. CASE can reference columns and calculated values available from joined tables.
Can CASE be used with aggregate functions?
Yes. CASE can evaluate aggregate expressions such as SUM or COUNT.