CASE Statement in SQL

The CASE statement is an important SQL feature that helps in adding conditional logic to queries, making them dynamic and easier to read. It supports flexible data transformation, custom labeling, and simplifies complex filtering within a single SQL query. It is commonly used for data transformation, custom labeling, and handling complex filtering within a single query. In this blog, you’ll learn the purpose of the CASE statement, how to use it, and the best practices to follow in detail.

Table of Contents:

• What is the CASE Statement in SQL?
• Types of CASE Expressions in SQL
• Using CASE Statement in SQL Queries
• CASE Statement in SQL with Aggregate Functions
• CASE Statement vs IF…ELSE in SQL
• Common Mistakes When Using CASE Statement in SQL
• Best Practices for Using CASE Statements in SQL
• Practical Applications of CASE Statement in SQL
• Conclusion

What is the CASE Statement in SQL? 

The CASE statement in SQL is a powerful tool for adding conditional logic directly within your queries. Similar to an if-then-else structure in programming languages, it allows you to return different results based on specific conditions. This makes your SQL code more dynamic and eliminates the need for multiple complex queries to handle conditional scenarios.

Before diving into the types of CASE statements, let’s create a table to use in our examples:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    ProductCode INT,
    Price DECIMAL(10, 2)
);
INSERT INTO Products (ProductID, ProductName, ProductCode, Price) VALUES
(101, 'Laptop Pro', 1, 1200.00),
(102, 'Gaming Headset', 2, 85.50),
(103, 'Smartwatch X', 1, 299.99),
(104, 'Wireless Mouse', 3, 25.00),
(105, 'Mechanical Keyboard', 3, 110.75),
(106, 'Designer Jeans', 2, 75.00),
(107, 'Smart LED Bulb', 1, 15.99);
SELECT * FROM Products;

Output:

This is how the table will look after creation and after inserting the data into it.

Level Up Your Tech Skills – Take Our SQL Pro Course Now

Learn from basics to advanced queries with hands-on lessons, live Q&A, and earn a recognized certificate.

Explore Program

Types of CASE Expressions in SQL

There are two main types of CASE expressions in SQL, which are the Simple CASE expression and the Searched CASE expression. While nested CASE expressions are not a distinct type, they refer to using one CASE inside another for complex logic.

1. Simple CASE Expression

The Simple CASE expression evaluates a single expression against a set of defined literal values. It is usually compact, as it checks for specific, discrete values.

Example:

SELECT
    ProductName,
    ProductCode,
    CASE ProductCode
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Apparel'
        WHEN 3 THEN 'Home Goods'
        ELSE 'Miscellaneous'
    END AS ProductCategory
FROM
    Products;

Output:

Explanation: Here, if ProductCode is 1, it returns ‘Electronics’; if 2, it returns ‘Apparel’, and so on. If there’s no match, it defaults to ‘Miscellaneous’

2. Searched CASE Expression

The Searched CASE expression allows you to use different conditions for each WHEN clause instead of comparing just one value. It’s useful for handling things like price ranges, stock levels, or other custom conditions.

Example:

SELECT
    ProductName,
    Price,
    CASE
        WHEN Price < 50.00 THEN 'Budget-Friendly'
        WHEN Price BETWEEN 50.00 AND 200.00 THEN 'Mid-Range'
        WHEN Price > 200.00 THEN 'Premium'
        ELSE 'Price Not Listed' --- used when there is a NULL value 
    END AS PriceCategory
FROM
    Products;

Output:

Explanation: In this example, the CASE expression checks the price column to categorize it. If it’s under 50, it’s marked as budget-friendly; if between 50 and 200, it’s mid-range, and so on. 

3. Nested CASE Expression

Nested CASE expressions allow you to place one CASE inside another, typically within a WHEN or WHERE clause. This technique is useful when you need to handle more complex conditions or multiple layers of logic in your SQL queries.

Example:

SELECT
    ProductName,
    ProductCode,
    Price,
    CASE ProductCode
        WHEN 1 THEN 
            CASE
                WHEN Price > 200.00 THEN 'Electronics - High-End'
                WHEN Price BETWEEN 50.00 AND 200.00 THEN 'Electronics - Standard'
                ELSE 'Electronics - Basic'
            END
        WHEN 2 THEN 
            CASE
                WHEN Price > 100.00 THEN 'Apparel - Premium'
                ELSE 'Apparel - Regular'
            END
        WHEN 3 THEN 
            CASE
                WHEN Price > 75.00 THEN 'Home Goods - Quality'
                ELSE 'Home Goods - Value'
            END
        ELSE 'Uncategorized Product' 
    END AS DetailedProductCategory
FROM
    Products;

Output:

Explanation: In this example, the query first categorizes products by ProductCode. Then, for each product code, a nested CASE further classifies the products based on their price as high-end, regular, quality, value, or basic.

Using CASE Statement in SQL Queries

The CASE expression offers a simple way to apply conditions directly in SQL queries. It is especially useful for dynamic output, custom sorting, filtering, and conditional aggregation based on specific conditions.

Example: Let’s create a table to perform a CASE statement in SQL Queries

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50), 
    Salary DECIMAL(10, 2),
    HireDate DATE,
    PerformanceRating INT    
);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate, PerformanceRating) VALUES
(101, 'Anil', 'Sompal', 'IT', 75000.00, '2022-03-10', 3),
(102, 'Baba', 'Kadhar', 'HR', 60000.00, '2021-07-22', 2),
(103, 'Charu', 'Latha', 'Sales', 85000.00, '2023-01-05', 4),
(104, 'Dharan', 'Sai', 'IT', 90000.00, '2022-11-15', 4),
(105, 'Priya', 'Param', 'Marketing', 68000.00, '2021-09-01', 3),
(106, 'Kishore', 'Reddy', 'Sales', 72000.00, '2023-05-20', 2),
(107, 'Ravi', 'Arjun', 'HR', 62000.00, '2022-04-01', 3),
(108, 'Lokesh', 'Kanagaraj', 'Marketing', 70000.00, '2023-02-14', 1);
SELECT * FROM  Employees;

Output:

1. Using CASE Statement in SQL SELECT Clause

The CASE statement makes a new temporary column in the result set of the query when it’s used in the SELECT clause. The CASE statement helps you categorize or modify data temporarily during a query. It’s useful for adding labels or doing conditional calculations, but it does not affect the original table data.

Example:

SELECT
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary >= 80000 THEN 'High Earner'
        WHEN Salary >= 65000 AND Salary < 80000 THEN 'Mid-Range Earner'
        ELSE 'Entry-Level Earner'
    END AS SalaryBand
FROM
    Employees;

Output:

Explanation: Here, in the SELECT clause, the CASE statement creates a temporary column in the query output. It helps transform or classify raw data into readable labels or conditional values without changing the original table.

2. Using CASE Statement in SQL ORDER BY Clause

Using the CASE statement in the ORDER BY clause provides you with the ability to create a custom order. Instead of sorting only on the values of the columns, you can assign different “weights” or priorities to the values based on conditions, which allows for more complex sorting logic.

Example:

SELECT
    FirstName,
    LastName,
    Department,
    Salary
FROM
    Employees
ORDER BY
    CASE Department
        WHEN 'IT' THEN 1
        WHEN 'HR' THEN 2
        WHEN 'Sales' THEN 3
        WHEN 'Marketing' THEN 4
        ELSE 5 
    END,
    Salary DESC;

Output:

Explanation: In this example, using CASE in the ORDER BY clause helps create a custom sort order. By assigning temporary numeric priorities to specific values, you can control how the results are sorted, rather than relying on default alphabetical or numerical ordering.

3. Using CASE Statement in SQL WHERE Clause

By using the CASE statement in the WHERE clause, you can filter rows conditionally. This means that your filtering criteria can depend on the values of other data in the row, greatly increasing your queries’ flexibility to filter down to the conditions needed to be met to select particular slices of data.

Example: 

SELECT
    EmployeeID,
    FirstName,
    LastName,
    HireDate,
    PerformanceRating
FROM
    Employees
WHERE
    (CASE
        WHEN PerformanceRating >= 3 AND HireDate < '2022-07-01' THEN 1
        WHEN PerformanceRating < 3 THEN 1 
        ELSE 0
    END) = 1;

Output: 

Explanation: In this example, the CASE statement in the WHERE clause allows dynamic filtering by conditionally including or excluding rows. This makes it possible to group data based on user-defined logic, even leading to flexible GROUP BY scenarios.

4. Using CASE Statement with SQL GROUP BY Clause

The CASE statement can also be used in the GROUP BY clause to create custom groups instead of grouping by existing column values. This helps you summarize data using functions like SUM, COUNT, or AVG based on conditions you define.

Example: 

SELECT
    CASE
        WHEN HireDate < '2022-01-01' THEN 'Senior Employee'
        ELSE 'Junior Employee'
    END AS EmployeeTenure,
    COUNT(EmployeeID) AS NumberOfEmployees,
    AVG(Salary) AS AverageSalary
FROM
    Employees
GROUP BY
    CASE
        WHEN HireDate < '2022-01-01' THEN 'Senior Employee'
        ELSE 'Junior Employee'
    END;

Output:

Explanation: In this example, using a CASE statement in the GROUP BY clause lets you group data based on custom categories defined at runtime. The rows are grouped by the output of the CASE expression, enabling summary calculations for each logical group.

CASE Statement in SQL with Aggregate Functions

The CASE statement becomes especially powerful when used with aggregate functions like SUM(), COUNT(), and AVG(). The CASE statement helps apply conditions inside aggregate functions like SUM, COUNT, or AVG, so calculations are done only when specific conditions are met.

Let’s create a table to perform SQL with aggregate functions:

CREATE TABLE SalesTransactions (
    TransactionID INT PRIMARY KEY,
    ProductID INT,
    TransactionDate DATE,
    ProductCategory VARCHAR(50), 
    SaleAmount DECIMAL(10, 2),
    PaymentMethod VARCHAR(50)   
);
INSERT INTO SalesTransactions (TransactionID, ProductID, TransactionDate, ProductCategory, SaleAmount, PaymentMethod) VALUES
(1, 101, '2024-06-01', 'Electronics', 500.00, 'Credit Card'),
(2, 102, '2024-06-01', 'Books', 35.50, 'Cash'),
(3, 103, '2024-06-02', 'Clothing', 120.00, 'Online Payment'),
(4, 101, '2024-06-02', 'Electronics', 75.00, 'Cash'),
(5, 104, '2024-06-03', 'Books', 60.25, 'Credit Card'),
(6, 102, '2024-06-03', 'Books', 20.00, 'Cash'),
(7, 105, '2024-06-04', 'Electronics', 1200.00, 'Online Payment'),
(8, 103, '2024-06-04', 'Clothing', 80.00, 'Credit Card'),
(9, 101, '2024-06-05', 'Electronics', 150.00, 'Credit Card'),
(10, 106, '2024-06-05', 'Clothing', 45.00, 'Cash');
SELECT * FROM SalesTransactions;

Output:

1. Using a CASE Statement in SQL with SUM

When we use CASE in conjunction with SUM(), we can create conditional sums. We can use this to sum a column based on several conditions, commonly returning a 0 if that condition is not met, as a value that allows SUM() to properly sum only the wanted values.

Example:

SELECT
    SUM(CASE WHEN ProductCategory = 'Electronics' THEN SaleAmount ELSE 0 END) AS TotalElectronicsSales,
    SUM(CASE WHEN ProductCategory = 'Books' THEN SaleAmount ELSE 0 END) AS TotalBooksSales,
    SUM(SaleAmount) AS OverallTotalSales
FROM
    SalesTransactions;

Output: 

Explanation: In this example, the CASE statement in SQL is used with SUM() to add up SaleAmount based on specific conditions. It calculates totals only for ‘Electronics’ and ‘Books’, treating other categories as zero. This works like a summary report that shows totals for selected categories.

2. Using the CASE Statement in SQL with COUNT

An alternative to using CASE with SUM() is using it with COUNT(). This allows you to count rows based on specific conditions. Typically, the CASE returns 1 when a condition is met (so the row is counted) and NULL when it’s not (so the row is ignored). This lets you perform conditional counting without affecting the total count.

Example:

SELECT
    COUNT(CASE WHEN PaymentMethod = 'Credit Card' THEN 1 END) AS CreditCardTransactions,
    COUNT(CASE WHEN PaymentMethod = 'Cash' THEN 1 END) AS CashTransactions,
    COUNT(*) AS TotalTransactions 
FROM
    SalesTransactions;

Output:

Explanation: In this example, the CASE statement in SQL is combined with COUNT() to count rows based on specific conditions. It counts transactions made with ‘Credit Card’ and ‘Cash’ separately. The CASE returns 1 for matching rows and NULL for others, allowing COUNT() to include only the relevant rows.

3. Using the CASE Statement in SQL with AVG

Using CASE with AVG() allows you to average the values based on conditions. Like SUM(), you’ll usually return the value to be averaged if the condition is met, and return NULL if not (as AVG() ignores NULLs to avoid biasing the average).

Example:

SELECT
    AVG(CASE WHEN ProductCategory = 'Clothing' THEN SaleAmount ELSE NULL END) AS AvgClothingSale,
    AVG(CASE WHEN ProductCategory = 'Electronics' THEN SaleAmount ELSE NULL END) AS AvgElectronicsSale
FROM
    SalesTransactions;

Output:

Explanation: In this example, the CASE statement is used with the AVG() function to calculate the average SaleAmount only for ‘Clothing’ and ‘Electronics’. Sales from other categories are ignored in each specific average. If a category like ‘Footwear’ or ‘Books’ isn’t present, the CASE returns NULL, which the AVG() function automatically skips while calculating the result.

4. Using the CASE Statement in SQL for Grouped Aggregations

When using CASE with GROUP BY and aggregation functions in SQL is the flexibility of defining aggregated grouping based on dynamic groups. You have many possibilities to define your groups for each aggregate or to create multiple conditional aggregates to work with existing groups.

Example:

SELECT
    ProductCategory,
    SUM(CASE WHEN PaymentMethod = 'Credit Card' THEN SaleAmount ELSE 0 END) AS TotalCreditCardSales,
    SUM(CASE WHEN PaymentMethod <> 'Credit Card' THEN SaleAmount ELSE 0 END) AS TotalOtherPaymentSales,
    COUNT(TransactionID) AS NumberOfTransactions
FROM
    SalesTransactions
GROUP BY
    ProductCategory;

Output:

Explanation: In this example, the CASE statement in SQL is used with the SUM() function inside a GROUP BY clause. It calculates total sales for each product category by conditionally summing ‘Credit Card’ and ‘Non-Credit Card’ payments, giving a clear breakdown of payment methods per category.

CASE Statement vs IF…ELSE in SQL 

Common Mistakes When Using the CASE Statement in SQL 

1. Missing the END keyword: The CASE expression must always end with the END keyword, or it will show a syntax error
2. Skipping the ELSE clause: If none of the WHEN conditions are true and there is no ELSE part, the result will be NULL
3. Mixing data types: Using different data types in the WHEN and ELSE parts can cause errors or give unexpected results
4. Wrong order of conditions: If a general condition is written before a more specific one, the output may not be correct, since SQL picks the first match
5. Writing too many conditions: Having too many or very complex conditions in one CASE can make it hard to understand and fix later

Best Practices for Using Case Statements in SQL

The CASE statement in SQL helps apply conditional logic directly within queries to simplify data categorization and aggregation. Let’s explore the best practices for using the CASE statement in SQL.

1. Prioritize readability: Keep your CASE conditions clear and simple so they are easy to understand
2. Consider condition order: Place the most common or most important condition first to improve both logic and performance
3. Limit nesting: Avoid using many CASE statements inside each other, as it makes the query harder to read and manage
4. Use lookup tables for mappings: Instead of writing long CASE blocks for fixed value changes, consider using a separate table and joining it
5. Move complex logic to views or functions: If the CASE logic becomes too detailed, break it out into a view or function to keep your main query clean

Practical Applications of CASE Statement in SQL 

The CASE statement in SQL is widely used in real-world scenarios to apply conditions, group data, handle missing values, and display custom outputs in a clean and readable format.

1. Multi-Faceted Reporting: CASE helps create dynamic reports by conditionally summarizing or calculating values, such as showing region-wise sales in separate columns or grouping performance data in one output.

2. Categorizing Data: It lets you divide data into clear categories based on set rules, like labeling sales figures as High, Medium, or Low depending on performance.

3. Data Translation: CASE can convert raw values like numeric codes into readable text, for example, turning ‘1’ into “Active” or ‘0’ into “Inactive” for better understanding.

Master the Basics of SQL – Without Spending a Dime!

Dive into structured lessons, practice queries, and foundational concepts for completely free.

Explore Program

Conclusion 

The CASE statement in SQL is a powerful tool for applying conditional logic directly within queries, making your data handling more efficient and dynamic. It helps simplify complex logic, categorize data, and customize outputs based on different conditions. By understanding how it works, comparing it with constructs like IF ELSE, avoiding common mistakes, and using it in real-world scenarios, you can greatly improve your SQL query-building skills and make your data analysis more effective and readable.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

CASE Statement in SQL – FAQs