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

Conditional Logic: The CASE expression allows you to implement conditional logic in SQL queries.
Multiple Conditions: You can specify multiple conditions using WHEN clauses.
ELSE Clause: An optional ELSE clause can be used to specify a default value if none of the conditions are met.
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

EmployeeID	Name	City	State	Salary
1	Anand	Chennai	Tamil Nadu	50000
2	Bala	Coimbatore	Tamil Nadu	60000
3	Kavitha	Karaikal	Puducherry	55000
4	Raj	Madurai	Tamil Nadu	70000
5	Kumar	Trichy	Tamil Nadu	45000

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;

Name	Salary	SalaryCategory
Anand	50000	Medium
Bala	60000	Medium
Kavitha	55000	Medium
Raj	70000	High
Kumar	45000	Low

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;

Name	Salary
Raj	70000
Anand	50000
Bala	60000
Kavitha	55000
Kumar	45000

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

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89

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;

Name	Percentage	Grade
Ram	92	A
Karthik	88	B
David	95	A
Kannan	91	A
Siva	89	B

Key Takeaways

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

Documentation Index

​Key Points

​Syntax of CASE

​Examples

​CASE in SELECT Clause

​CASE in ORDER BY Clause

​CASE in UPDATE Statement

​Practical Use Case

​Key Takeaways