Beginner

657. JOIN with CASE

Use CASE expressions with JOINed data to create business classifications.

📘 Lesson 657 of 728 ⏱ 14 min 🎯 Intermediate

CASE evaluates conditions and returns different values, allowing JOINed records to be categorized according to business rules.

Reports often require derived categories such as High Value, Medium Value, and Low Value.

Classify customers according to their total sales.

SQL 
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;

Used in dashboards, customer segmentation, risk reporting, HR analytics, and financial MIS.

  • CASE can classify JOINed results.
  • CASE can use aggregate expressions.
  • Business thresholds should be clearly defined.
  • NULL values should be considered.
  • CASE can be used in SELECT, ORDER BY, and other clauses.
Can CASE be used after a JOIN?

Yes. CASE can evaluate columns and calculated values from JOINed datasets.

Can CASE use aggregate results?

Yes. CASE can evaluate expressions such as SUM, COUNT, or AVG in grouped queries.

Have you finished this lesson?

Introduction to SQL – All Lessons