CASE expression in SQL

The CASE expression in SQL is used to implement conditional logic in queries. It allows you to perform different actions based on specific conditions. The CASE expression is similar to the IF-THEN-ELSE logic in programming languages and is useful for creating derived columns, categorizing data, and applying conditional formatting.

​

Key Points

1. Conditional Logic: The CASE expression allows you to implement conditional logic in SQL queries.
2. Multiple Conditions: You can specify multiple conditions using WHEN clauses.
3. ELSE Clause: An optional ELSE clause can be used to specify a default value if none of the conditions are met.
4. Use Cases: Commonly used in SELECT, UPDATE, and ORDER BY clauses to create derived columns, categorize data, or apply conditional sorting.

​

Syntax of CASE

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

• condition1, condition2, ...: The conditions to evaluate.
• result1, result2, ...: The result to return if the corresponding condition is true.
• default_result: The result to return if none of the conditions are true (optional).

​

Examples

​

CASE in SELECT Clause

Suppose you have a table named Employees that stores employee details.

Table: Employees

To categorize employees based on their salary:

SELECT Name, Salary,
    CASE
        WHEN Salary > 60000 THEN 'High'
        WHEN Salary BETWEEN 50000 AND 60000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

​

CASE in ORDER BY Clause

You can use the CASE expression in the ORDER BY clause to apply conditional sorting.

To sort employees by salary category (High, Medium, Low):

SELECT Name, Salary
FROM Employees
ORDER BY
    CASE
        WHEN Salary > 60000 THEN 1
        WHEN Salary BETWEEN 50000 AND 60000 THEN 2
        ELSE 3
    END;

​

CASE in UPDATE Statement

You can use the CASE expression in the UPDATE statement to apply conditional updates.

To update the salary of employees based on their current salary:

UPDATE Employees
SET Salary =
    CASE
        WHEN Salary > 60000 THEN Salary + 5000
        WHEN Salary BETWEEN 50000 AND 60000 THEN Salary + 3000
        ELSE Salary + 2000
    END;

The salaries are updated as follows:

• Employees with a salary greater than ₹60,000 receive a ₹5,000 raise.
• Employees with a salary between ₹50,000 and ₹60,000 receive a ₹3,000 raise.
• All other employees receive a ₹2,000 raise.

​

Practical Use Case

Suppose you have a table named Students that stores student details.

Students

To categorize students based on their percentage:

SELECT Name, Percentage,
    CASE
        WHEN Percentage >= 90 THEN 'A'
        WHEN Percentage BETWEEN 80 AND 89 THEN 'B'
        ELSE 'C'
    END AS Grade
FROM Students;

​

Key Takeaways

1. The CASE expression allows you to implement conditional logic in SQL queries.
2. It can be used in SELECT, UPDATE, and ORDER BY clauses to create derived columns, categorize data, or apply conditional sorting.
3. Multiple conditions can be specified using WHEN clauses, and an optional ELSE clause can provide a default value.
4. The CASE expression is useful for creating dynamic and flexible queries.