The CASE statement is a powerful SQL feature that allows you to implement conditional logic directly within your SQL queries. It can be used in SELECT, UPDATE, and other SQL statements. The CASE statement evaluates a list of conditions and returns a value when the first condition is met.
Here's a basic syntax of a CASE statement in a SELECT query:
SELECT
column1,
column2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END AS alias_name
FROM table_name;
Example
Assume you have a table named Employees with the following columns: EmployeeID, Name, and Salary. You want to categorize employees based on their salary: "Low", "Medium", and "High".
SELECT
EmployeeID,
Name,
Salary,
CASE
WHEN Salary < 30000 THEN 'Low'
WHEN Salary BETWEEN 30000 AND 70000 THEN 'Medium'
WHEN Salary > 70000 THEN 'High'
ELSE 'Not Specified'
END AS SalaryCategory
FROM Employees;
Explanation of the Example
- The SELECT statement fetches EmployeeID, Name, and Salary from the Employees table.
- The CASE statement checks the Salary for each employee:
- If the Salary is less than 30,000, it returns 'Low'.
- If the Salary is between 30,000 and 70,000 (inclusive), it returns 'Medium'.
- If the Salary is greater than 70,000, it returns 'High'.
- If none of the conditions are met (which should not happen given the data), it returns 'Not Specified'.
- AS SalaryCategory specifies an alias for the computed column in the output.
This logic allows you to create more informative result sets based on the values contained in your database.