The CASE statement in SQL is used to create conditional logic directly within a SELECT query. It allows you to categorize data based on specific conditions.
Here’s the general syntax for using a CASE statement:
SELECT
column1,
column2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
ELSE default_result
END AS alias_name
FROM
table_name;
Explanation:
1. CASE: Begins the case statement.
2. WHEN: Specifies the condition to evaluate.
3. THEN: Indicates the result to return if the condition is true.
4. ELSE: This is optional. If none of the conditions are met, a default result can be specified.
5. END: Ends the case statement.
6. AS alias_name: This gives a name to the result column produced by the CASE statement.
Example
Suppose you have a table named employees with a column salary. You want to categorize each employee's salary into 'Low', 'Medium', and 'High'.
SELECT
employee_id,
employee_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 salary_category
FROM
employees;
Breakdown of the Example:
- Columns Selected: The query selects employee_id, employee_name, and salary from the employees table.
- CASE Logic:
- If the salary is less than 30,000, the salary_category will be 'Low'.
- If the salary is between 30,000 and 70,000, it will be categorized as 'Medium'.
- If the salary is greater than 70,000, it will be considered 'High'.
- If none of the conditions are met, it defaults to 'Not Specified'.
Notes:
- The CASE statement can be used in various components of a SQL query, including SELECT, WHERE, ORDER BY, and JOIN clauses.
- You can have as many WHEN conditions as necessary.
- Ensure that your conditions are mutually exclusive to avoid ambiguity.
By using the CASE statement effectively, you can create meaningful categories and facilitate better data analysis in your SQL queries.