Why use CASE?

• Map numeric codes to human-readable text (e.g., 1 → 'Active', 0 → 'Inactive')



• Create buckets (e.g., score → 'Low', 'Medium', 'High')



• Compute conditional aggregates



• Apply different formatting or calculations in the result set

CASE Syntax — Two Forms

1. Simple CASE

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END
  

2. Searched CASE

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END
  

Examples — SELECT with CASE

Example 1: Map status codes to text (Simple CASE)

SELECT OrderID,
       CASE Status
         WHEN 1 THEN 'Pending'
         WHEN 2 THEN 'Processing'
         WHEN 3 THEN 'Shipped'
         ELSE 'Unknown'
       END AS OrderStatus
FROM Orders;
  

Example 2: Create score buckets (Searched CASE)

SELECT StudentID, Score,
       CASE
         WHEN Score >= 90 THEN 'A'
         WHEN Score >= 80 THEN 'B'
         WHEN Score >= 70 THEN 'C'
         ELSE 'F'
       END AS Grade
FROM ExamResults;
  

Nested CASE

SELECT ProductID, Price,
  CASE
    WHEN Price IS NULL THEN 'No Price'
    WHEN Price < 100 THEN 'Budget'
    WHEN Price BETWEEN 100 AND 500 THEN
      CASE WHEN Discount > 0 THEN 'Discounted Mid' ELSE 'Mid' END
    ELSE 'Premium'
  END AS PriceCategory
FROM Products;
  

Using CASE in UPDATE Statements

UPDATE Customers
SET Tier =
  CASE
    WHEN TotalSpent >= 10000 THEN 'Platinum'
    WHEN TotalSpent >= 5000  THEN 'Gold'
    WHEN TotalSpent >= 1000  THEN 'Silver'
    ELSE 'Bronze'
  END
WHERE Active = 1;
  

CASE with Aggregates — Conditional Aggregation

SELECT
  COUNT(*) AS TotalOrders,
  SUM(CASE WHEN Status = 'Shipped' THEN 1 ELSE 0 END) AS ShippedCount,
  SUM(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END) AS CancelledCount
FROM Orders;
  

ORDER BY and CASE

SELECT Name, Priority
FROM Tasks
ORDER BY
  CASE Priority
    WHEN 'High' THEN 1
    WHEN 'Medium' THEN 2
    WHEN 'Low' THEN 3
    ELSE 4
  END;
  

Performance Considerations

• CASE itself is evaluated per row — keep expressions simple for large tables.



• Avoid calling expensive functions inside CASE repeatedly; compute once if possible.



• Indexes won’t help CASE evaluations directly, but you can pre-filter with WHERE to reduce rows.



• For very large transformations, consider staging results in a temporary table and indexing it.

Common Mistakes & Gotchas

• Forgetting the END — syntax error.



• Using overlapping WHEN conditions in simple CASE (order matters in searched CASE).



• Expecting boolean algebra in simple CASE — simple CASE compares an expression to constants.



• Relying on implicit data type conversions — use explicit CAST() when needed.

Practical Exercises

1. Write a SELECT that maps order totals to categories: 'Small' < 50, 'Medium' 50–199, 'Large' >= 200.



2. Use CASE in an UPDATE to set an account_status based on last_login date.



3. Create a query that shows total sales per region and the percentage that comes from 'VIP' customers using conditional SUM.

Examples: Real-World Queries

Highest-priority active tasks first

SELECT TaskID, Title, Priority, IsActive
FROM Tasks
WHERE IsActive = 1
ORDER BY
  CASE Priority WHEN 'Critical' THEN 1 WHEN 'High' THEN 2 ELSE 3 END, CreatedAt DESC;
  

Conditional revenue buckets

SELECT
  ProductID,
  SUM(Amount) AS Revenue,
  CASE
    WHEN SUM(Amount) >= 100000 THEN 'Top Seller'
    WHEN SUM(Amount) >= 50000  THEN 'Strong'
    ELSE 'Standard'
  END AS PerformanceTier
FROM Sales
GROUP BY ProductID;
  

FAQ — CASE WHEN (Short Answers)

Q: Should I use simple CASE or searched CASE?

Q: Can CASE return different data types?

Q: Is CASE ANSI SQL?

Conclusion