SQL CASE WHEN Explained — Conditional Logic Made Simple

⭐ Learn SQL through Job-Ready Project-based Hands-on Courses →

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 Video!

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;

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;

Note how the searched CASE allows range checks and overlapping conditions — the first true WHEN wins.

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;

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;

This updates the Tier column for all active customers in one statement — efficient and clear.

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;

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;

This orders 'High' tasks first even if Priority is a text column.

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

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

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

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.

⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!

SQL CASE

SQL CASE Explained