SQL Notes
Learn how to use the CASE expression in SQL for conditional logic within queries, understand simple and searched CASE syntax, and apply it in real-world scenarios.
SQL does not have IF-ELSE statements inside queries the way programming languages do. Instead, it has the CASE expression — a powerful tool that lets you add conditional logic directly within your SELECT, WHERE, ORDER BY, and UPDATE statements.
The CASE expression evaluates conditions and returns a value based on which condition is true. Think of it as a switch statement or an inline IF-ELSE that works right inside your SQL queries, transforming data on the fly without changing what is stored in the table.
Why is this important? In practice, raw database values rarely match what users want to see on screen. A status column might store 'P', 'S', and 'D', but the user interface needs to display 'Pending', 'Shipped', and 'Delivered'. Numeric marks need to be converted into letter grades. Salary numbers need to be categorized into ranges. CASE handles all these transformations directly in the query without requiring application code to process each row individually. It is one of the most frequently used SQL features in real-world reporting and data presentation.
Simple CASE Example
The simple CASE compares one expression against a list of values:
| EmpID | Name | Department | TeamName |
|---|---|---|---|
| 1 | Rahul | Engineering | Tech Team |
| 2 | Priya | Marketing | Growth Team |
| 3 | Amit | HR | People Team |
| 4 | Sneha | Sales | Revenue Team |
| 5 | Vikas | Finance | Other |
Searched CASE Example
The searched CASE evaluates multiple independent conditions — much more flexible:
| StudentID | Name | Marks | Grade |
|---|---|---|---|
| 1 | Rahul | 92 | A+ |
| 2 | Priya | 78 | B+ |
| 3 | Amit | 55 | C |
| 4 | Sneha | 35 | F |
The conditions are evaluated top to bottom. The first true condition wins, and the rest are skipped.
CASE in SELECT — Data Categorization
One of the most common uses is categorizing data into groups:
Data categorization is something you will do constantly in reporting and analytics. Business users think in categories — they want to know how many "premium" products sold versus "budget" ones. They want to see employees grouped by salary band. They want orders classified by urgency. CASE expressions let you create these categories on the fly without adding extra columns to your tables. The categories are computed at query time based on whatever rules the business defines, and those rules can easily change by modifying the CASE expression.
CASE in WHERE Clause
You can use CASE in filtering logic:
SELECT * FROM Orders
WHERE CASE
WHEN @FilterType = 'recent' THEN OrderDate >= DATEADD(DAY, -7, GETDATE())
WHEN @FilterType = 'large' THEN TotalAmount > 10000
WHEN @FilterType = 'pending' THEN Status = 'Pending'
ELSE 1 = 1
END;CASE in ORDER BY
Sometimes the natural alphabetical or numerical sort order does not match the business priority order. A status field sorted alphabetically puts 'Delivered' before 'Pending' and 'Urgent', which makes no sense from a workflow perspective. CASE in ORDER BY lets you define custom sort priorities so that the most important items appear first regardless of their alphabetical position. This is invaluable for task management systems, support ticket queues, and order processing dashboards.
Sort results conditionally:
CASE in UPDATE Statements
Apply different updates based on conditions:
-- Give raises based on performance rating
UPDATE Employees
SET Salary = CASE
WHEN PerformanceRating = 'Excellent' THEN Salary * 1.15
WHEN PerformanceRating = 'Good' THEN Salary * 1.10
WHEN PerformanceRating = 'Average' THEN Salary * 1.05
ELSE Salary
END;-- Update order status based on delivery date
UPDATE Orders
SET Status = CASE
WHEN DeliveryDate IS NOT NULL AND DeliveryDate <= CURRENT_DATE THEN 'Delivered'
WHEN ShipDate IS NOT NULL THEN 'Shipped'
WHEN PaymentDate IS NOT NULL THEN 'Confirmed'
ELSE 'Pending'
END;CASE with Aggregate Functions
One of the most powerful CASE patterns is using it inside aggregate functions to create pivot-style reports without using the database-specific PIVOT syntax. By wrapping CASE inside SUM or COUNT, you can count or total rows that meet different conditions, effectively turning row-based data into column-based summaries. This technique is used extensively in dashboard queries where you need to show breakdowns — how many orders are pending versus shipped versus delivered — all in a single row per group.
Create pivot-style reports using CASE inside aggregate functions:
SELECT
Department,
COUNT(*) AS TotalEmployees,
SUM(CASE WHEN Salary > 80000 THEN 1 ELSE 0 END) AS HighEarners,
SUM(CASE WHEN Salary BETWEEN 50000 AND 80000 THEN 1 ELSE 0 END) AS MidEarners,
SUM(CASE WHEN Salary < 50000 THEN 1 ELSE 0 END) AS LowEarners,
AVG(CASE WHEN Gender = 'M' THEN Salary END) AS AvgMaleSalary,
AVG(CASE WHEN Gender = 'F' THEN Salary END) AS AvgFemaleSalary
FROM Employees
GROUP BY Department;This creates a cross-tabulation report without needing PIVOT syntax.
Real-World Example: E-Commerce Order Dashboard
SELECT
DATE(OrderDate) AS OrderDay,
COUNT(*) AS TotalOrders,
SUM(TotalAmount) AS Revenue,
SUM(CASE WHEN PaymentMethod = 'UPI' THEN TotalAmount ELSE 0 END) AS UPIRevenue,
SUM(CASE WHEN PaymentMethod = 'Card' THEN TotalAmount ELSE 0 END) AS CardRevenue,
SUM(CASE WHEN PaymentMethod = 'COD' THEN TotalAmount ELSE 0 END) AS CODRevenue,
ROUND(
SUM(CASE WHEN Status = 'Returned' THEN 1.0 ELSE 0 END) / COUNT(*) * 100, 2
) AS ReturnRate
FROM Orders
WHERE OrderDate >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY DATE(OrderDate)
ORDER BY OrderDay DESC;Real-World Example: Student Results with Pass/Fail
Nested CASE Expressions
You can nest CASE inside CASE for complex logic:
CASE with NULL Handling
CASE is useful for handling NULL values gracefully:
Note: For simple NULL replacement, COALESCE is shorter:
SELECT Name, COALESCE(Phone, 'No phone on file') AS ContactPhone FROM Customers;Common Mistakes to Avoid
Mistake 1: Forgetting the ELSE clause. Without ELSE, unmatched cases return NULL. Always include ELSE for predictable results.
Mistake 2: Wrong condition order in searched CASE. Conditions are evaluated top to bottom. Put more specific conditions first:
Mistake 3: Using CASE when simpler functions exist. For NULL handling, use COALESCE or IFNULL. For simple two-way conditions, use IF (MySQL).
Mistake 4: Forgetting END. Every CASE must end with END. Missing it causes syntax errors.
Best Practices
- Always include ELSE for predictable, complete results
- Order conditions from most specific to least specific
- Use column aliases with AS for readability
- Keep CASE expressions readable — break onto multiple lines
- Use simple CASE when comparing one value to many options
- Use searched CASE when conditions involve different columns or operators
The CASE expression is one of SQL's most versatile tools. It brings conditional programming logic into your queries, enabling data transformation, categorization, and complex reporting without needing multiple queries or application-side processing.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for CASE Expression in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, case, expression
Related SQL Complete Guide Topics