SQL CASE Statement

Overview

The CASE statement is SQL's way of implementing conditional logic. It allows you to evaluate a list of conditions and return a specific value when the first condition is met. It's often used in SELECT statements to create dynamic results based on specified criteria.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Example 1: Basic Usage

In this example, we use the CASE statement to categorize employees based on their salary:

SELECT EmployeeName,
    Salary,
    CASE
        WHEN Salary < 30000 THEN 'Low'
        WHEN Salary BETWEEN 30000 AND 60000 THEN 'Medium'
        ELSE 'High'
    END AS SalaryCategory
FROM Employees;

This query categorizes each employee's salary as 'Low', 'Medium', or 'High' based on the specified salary ranges.

Example 2: Using CASE in ORDER BY

You can also use the CASE statement in an ORDER BY clause to sort results dynamically. Here’s how to sort employees by their salary categories:

SELECT EmployeeName, Salary
FROM Employees
ORDER BY
    CASE
        WHEN Salary < 30000 THEN 1
        WHEN Salary BETWEEN 30000 AND 60000 THEN 2
        ELSE 3
    END;

This query orders employees by their salary categories, ensuring that 'Low' salaries appear first, followed by 'Medium' and 'High'.

Example 3: CASE with Aggregate Functions

You can use the CASE statement with aggregate functions to summarize data based on conditions. For example, counting employees in each salary category:

SELECT
    COUNT(CASE WHEN Salary < 30000 THEN 1 END) AS LowSalaryCount,
    COUNT(CASE WHEN Salary BETWEEN 30000 AND 60000 THEN 1 END) AS MediumSalaryCount,
    COUNT(CASE WHEN Salary > 60000 THEN 1 END) AS HighSalaryCount
FROM Employees;

This query counts the number of employees in each salary category, returning the results in three separate columns.

Important Notes

• Multiple WHEN clauses can be used to handle different conditions.
• The ELSE clause is optional; if omitted and no conditions are met, NULL is returned.
• The CASE statement can be used in various clauses, including SELECT, WHERE, and ORDER BY.

Conclusion

The CASE statement is a powerful tool in SQL that enables conditional logic in queries. By using CASE, you can create dynamic and meaningful results based on specified criteria, enhancing the analytical capabilities of your SQL queries.