The CASE statement in SQL is a conditional expression that allows you to execute different expressions based on certain conditions. It can be used in SELECT, UPDATE, DELETE, and various other SQL statements.
Here's the basic syntax for using a CASE statement:
Basic Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
[ELSE resultN]
END
Example Usage in a SELECT Statement
Let's say you have a table called employees with columns id, name, salary, and department. You can use a CASE statement to categorize employees based on their salary.
SELECT id,
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;
In this example, the CASE statement checks the salary of each employee and assigns a label (Low, Medium, High, or Not Specified) to the salary_category column based on the salary range.
Using CASE in Other Statements
You can also use the CASE statement in other contexts, like UPDATE:
UPDATE employees
SET department = CASE
WHEN salary < 30000 THEN 'Support'
WHEN salary BETWEEN 30000 AND 70000 THEN 'Operations'
WHEN salary > 70000 THEN 'Management'
ELSE department
END;
In this update statement, the department of employees is updated based on their salary.
Important Notes
1. The CASE statement should end with END.
2. Each WHEN clause is evaluated in order, and the first true condition will apply.
3. If none of the conditions are true and there's no ELSE clause, the result will return NULL.
Summary
The CASE statement is a powerful feature in SQL that lets you implement conditional logic directly in your queries, making your data handling more flexible and informative.