What is CASE statement?

The SQL `CASE` statement is a powerful conditional expression that allows you to define different outputs based on specified conditions, similar to IF-THEN-ELSE logic found in other programming languages. It evaluates conditions and returns a value when the first condition is met, stopping further evaluation. If no condition is met, and an `ELSE` clause is provided, it returns the value in the `ELSE` clause; otherwise, it returns `NULL`.

What is the CASE Statement?

The CASE statement is a control flow statement that allows you to perform different actions or return different values based on various conditions. It evaluates a list of conditions and returns one of multiple possible result expressions. It's an essential tool for adding logic and flexibility to your SQL queries, enabling data transformation, conditional aggregation, and custom sorting.

There are two main forms of the CASE statement: the Simple CASE expression and the Searched CASE expression.

Simple CASE Statement

The Simple CASE expression compares an expression to a set of simple expressions to determine the result. It's often used when you need to check for equality against a single column or value.

SELECT
    ProductName,
    ListPrice,
    CASE ListPrice
        WHEN 0 THEN 'Not for sale'
        WHEN 10 THEN 'Budget'
        WHEN 20 THEN 'Standard'
        ELSE 'Premium'
    END AS PriceCategory
FROM
    Products;

Searched CASE Statement

The Searched CASE expression evaluates a set of Boolean expressions (conditions) to determine the result. This form is more flexible as it allows you to specify different conditions for each WHEN clause, similar to IF-THEN-ELSE IF structures found in procedural programming.

SELECT
    EmployeeName,
    Salary,
    CASE
        WHEN Salary < 30000 THEN 'Junior'
        WHEN Salary >= 30000 AND Salary < 60000 THEN 'Mid-Level'
        WHEN Salary >= 60000 AND Salary < 90000 THEN 'Senior'
        ELSE 'Executive'
    END AS ExperienceLevel
FROM
    Employees;

Key Uses and Benefits

• Conditional Logic: Apply different transformations or return different values based on specific conditions.
• Data Categorization: Group data into categories based on ranges or specific values (e.g., 'Low', 'Medium', 'High').
• Conditional Aggregation: Use CASE with aggregate functions (like SUM or COUNT) to perform conditional counts or sums (e.g., SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)).
• Custom Sorting: Define custom sort orders that are not strictly alphabetical or numerical.
• Data Cleaning and Transformation: Handle missing values or reformat data based on rules.
• Reporting: Create dynamic reports where columns or values change based on user-defined criteria.

In summary, the CASE statement is an indispensable tool in SQL for implementing conditional logic within queries, providing greater control over how data is retrieved, transformed, and presented.