SQL CASE Explained

SQL CASE WHEN Explained — Conditional Logic Made Simple



⭐ Want to learn SQL step-by-step? Check out our completely FREE Hands-On Practice 👉 SQL course for beginners .



The SQL CASE expression is the standard way to implement conditional logic inside queries. Think of it as an IF/ELSE for SQL — it lets you return different values depending on conditions. This beginner-friendly guide explains the two CASE forms (simple and searched), nested CASE, practical uses in SELECT and UPDATE, performance tips, and common mistakes.


📽 Watch the Video Tutorial | See the CASE Statement in Action!

Why use CASE?



• Use CASE when you need to:



  • • 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
  


Use simple CASE when you compare one expression to constant values.



2. Searched CASE



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


Use searched CASE for complex conditions (ranges, multiple columns, functions).



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;


How this query works:

  • SELECT OrderID: Displays the order ID for each record.

  • CASE Status: Checks the value stored in the Status column.

  • WHEN 1 THEN 'Pending': If Status equals 1, displays 'Pending'.

  • WHEN 2 THEN 'Processing': If Status equals 2, displays 'Processing'.

  • WHEN 3 THEN 'Shipped': If Status equals 3, displays 'Shipped'.

  • ELSE 'Unknown': If none of the listed values match, displays 'Unknown'.

  • END AS OrderStatus: Ends the CASE expression and names the new calculated column OrderStatus.

  • FROM Orders: Retrieves the records from the Orders table.


📽 Watch the Video Tutorial | See the CASE Statement in Action!


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;


How this query works:

  • SELECT StudentID, Score: Displays each student's ID and exam score.

  • CASE: Begins a conditional expression that evaluates multiple conditions.

  • WHEN Score >= 90 THEN 'A': Assigns an A to scores of 90 or higher.

  • WHEN Score >= 80 THEN 'B': Assigns a B to scores of 80 through 89.

  • WHEN Score >= 70 THEN 'C': Assigns a C to scores of 70 through 79.

  • ELSE 'F': Assigns an F to any score below 70 that does not match an earlier condition.

  • END AS Grade: Ends the CASE expression and names the calculated column Grade.

  • FROM ExamResults: Retrieves the student records and scores from the ExamResults table.


Important: Notice how the searched CASE expression allows you to evaluate ranges of values and use different comparison operators. SQL Server evaluates the WHEN conditions from top to bottom and returns the result for the first condition that is true. Once a match is found, the remaining conditions are skipped.

Nested CASE



You can nest CASE expressions when logic requires multiple levels:



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;


How this query works:

  • SELECT ProductID, Price: Displays the product ID and price for each product.

  • CASE: Begins the outer CASE expression, which evaluates the product's price.

  • WHEN Price IS NULL THEN 'No Price': Checks whether the price is missing. If it is NULL, displays 'No Price'.

  • WHEN Price < 100 THEN 'Budget': If the price is less than 100, classifies the product as 'Budget'.

  • WHEN Price BETWEEN 100 AND 500: Checks whether the price falls between 100 and 500, including both endpoints.

  • Nested CASE: If the price is between 100 and 500, a second CASE expression checks the Discount column.

  • WHEN Discount > 0 THEN 'Discounted Mid': If the product has a discount greater than zero, displays 'Discounted Mid'.

  • ELSE 'Mid': If there is no discount, displays 'Mid'.

  • ELSE 'Premium': If none of the earlier price conditions match, classifies the product as 'Premium'.

  • END AS PriceCategory: Ends the outer CASE expression and names the calculated column PriceCategory.

  • FROM Products: Retrieves the product records from the Products table.


Nested CASE can be helpful but keep readability in mind — long nested logic is a maintenance risk.



Using CASE in UPDATE Statements



CASE is powerful in UPDATE to set values conditionally in bulk:



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;


How this query works:

  • UPDATE Customers: Specifies that records in the Customers table will be updated.

  • SET Tier =: Identifies the Tier column that will receive the new values.

  • CASE: Begins the conditional expression used to determine each customer's new tier.

  • WHEN TotalSpent >= 10000 THEN 'Platinum': Assigns Platinum to customers who have spent 10,000 or more.

  • WHEN TotalSpent >= 5000 THEN 'Gold': Assigns Gold to customers who have spent between 5,000 and 9,999.99.

  • WHEN TotalSpent >= 1000 THEN 'Silver': Assigns Silver to customers who have spent between 1,000 and 4,999.99.

  • ELSE 'Bronze': Assigns Bronze to customers who do not meet any of the previous spending conditions.

  • END: Ends the CASE expression.

  • WHERE Active = 1: Limits the update to active customers. Customers who are not active are not changed.


This updates the Tier column for all active customers in one statement — efficient and clear. Always use a WHERE clause when you intend to update only selected records. Without it, every customer record would be updated.



CASE with Aggregates — Conditional Aggregation



Use CASE inside aggregates to count or sum conditionally:



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;


How this query works:

  • COUNT(*) AS TotalOrders: Counts all records in the Orders table and names the result TotalOrders.

  • SUM(): Adds together the values returned by each CASE expression.

  • CASE WHEN Status = 'Shipped' THEN 1: Returns 1 for every order with a Shipped status.

  • ELSE 0: Returns 0 for orders that are not shipped.

  • AS ShippedCount: Names the total number of shipped orders ShippedCount.

  • CASE WHEN Status = 'Cancelled' THEN 1: Returns 1 for every cancelled order.

  • ELSE 0: Returns 0 for orders that are not cancelled.

  • AS CancelledCount: Names the total number of cancelled orders CancelledCount.

  • FROM Orders: Retrieves the order records used to calculate all three totals.


Example: If the Orders table contains 100 orders, including 65 shipped orders and 10 cancelled orders, the query returns:

  • TotalOrders: 100
  • ShippedCount: 65
  • CancelledCount: 10


Notice that the query calculates all three results in a single SELECT statement without requiring separate queries. Conditional aggregation is extremely useful for dashboard metrics and reports.



ORDER BY and CASE



CASE can define custom sort order:



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


How this query works:

  • SELECT Name, Priority: Displays the task name and its priority level.

  • FROM Tasks: Retrieves the records from the Tasks table.

  • ORDER BY: Specifies how the query results should be sorted.

  • CASE Priority: Checks the value in the Priority column to determine its sorting position.

  • WHEN 'High' THEN 1: Assigns High priority a sorting value of 1.

  • WHEN 'Medium' THEN 2: Assigns Medium priority a sorting value of 2.

  • WHEN 'Low' THEN 3: Assigns Low priority a sorting value of 3.

  • ELSE 4: Assigns any other priority value a sorting position of 4.

  • END: Ends the CASE expression used for sorting.


Because ORDER BY sorts the calculated values in ascending order by default, tasks appear in this order: High, Medium, Low, followed by any other priority values.



Important: The CASE expression does not change the actual Priority values stored in the Tasks table. It simply creates temporary sorting values to control the order in which the query displays the results.


▶ IWantToLearnSQL.com
FREE SQL VIDEO TUTORIAL
SQL CASE Statement Video Tutorial
Watch: SQL CASE Statement Explained

Learn how to implement the SQL CASE Statement with practical examples. Follow along as we explain the queries step by step.

▶ WATCH THE FREE VIDEO

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?



A: Use simple CASE when you compare one expression to constants. Use searched CASE for range checks or complex conditions.



Q: Can CASE return different data types?



A: Ideally, all THEN/ELSE results should be compatible types. SQL will attempt conversion, but explicit CAST() is safer.



Q: Is CASE ANSI SQL?



A: Yes — CASE is part of the SQL standard and works across major databases (MySQL, Postgres, SQL Server, Oracle), with minor dialect differences.



Conclusion



The CASE expression is a versatile and essential tool for SQL developers — it brings conditional logic into SELECTs, UPDATEs, ORDER BY, and aggregates. Keep your CASE expressions readable, prefer searched CASE for complex conditions, and avoid unnecessary nesting.



Continue Learning SQL