The CASE statement in SQL is used to create conditional logic within SQL queries. It allows you to perform IF-THEN-ELSE logic directly in your SQL statements, which can be very useful for transforming data based on specific conditions.
Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
Example Usage
Let's consider a table employees with the following structure:
employees
emp_id emp_name salary
1 John 50000
2 Jane 75000
3 Jake 45000
4 Jill 90000
We want to classify employees based on their salary into 'High', 'Medium', and 'Low' categories. Here's how you can do that using a CASE statement:
SELECT emp_name,
salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
This query will return:
emp_name salary salary_category
John 50000 Medium
Jane 75000 Medium
Jake 45000 Low
Jill 90000 High
CASEin ORDER BY Clause
You can also use the CASE statement in the ORDER BY clause to customize the sorting order based on conditions.
SELECT emp_name, salary
FROM employees
ORDER BY
CASE
WHEN salary > 80000 THEN 1
WHEN salary BETWEEN 50000 AND 80000 THEN 2
ELSE 3
END;
This will order the result set by the custom salary categories, with 'High' salaries first, 'Medium' salaries next, and 'Low' salaries last.
CASEin UPDATEStatement
The CASE statement can also be used in an UPDATE statement to conditionally modify data.
UPDATE employees
SET salary_category =
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END;
This query updates the salary_category column in the employees table based on the conditions defined.
Nested CASEStatements
You can nest CASE statements within each other to handle more complex conditional logic.
SELECT emp_name,
salary,
CASE
WHEN salary > 80000 THEN
CASE
WHEN salary > 90000 THEN 'Very High'
ELSE 'High'
END
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
This query further classifies 'High' salaries into 'High' and 'Very High'.
Using the CASE statement in SQL allows you to implement complex conditional logic directly within your SQL queries, making your data manipulation and retrieval much more powerful and flexible.
