The SQL CASE expression is a conditional expression that returns a value based on a set of conditions. The CASE expression is used to evaluate a set of conditions and return a result corresponding to the first true condition. The CASE expression is widely used in SQL for creating conditional statements in SELECT, UPDATE, and DELETE statements.
Syntax
The syntax of the SQL CASE expression is as follows:
CASE
WHEN condition THEN result
[WHEN condition THEN result ...]
[ELSE result]
END
In the above syntax, “condition” is the condition to be evaluated and “result” is the value that will be returned if the condition is true. The “ELSE” clause is optional and is used to specify a default value to be returned if none of the conditions are true.
Example
Consider the following table:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | 5500 |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | 6500 |
Table “employees”
Suppose we want to classify the employees into three categories based on their salaries: “Low”, “Medium”, and “High”. The following SQL statement demonstrates the use of the CASE expression for this purpose:
SELECT
Name,
Salary,
CASE
WHEN Salary BETWEEN 0 AND 5000 THEN 'Low'
WHEN Salary BETWEEN 5001 AND 8000 THEN 'Medium'
ELSE 'High'
END AS 'Salary Classification'
FROM employees;
The above SQL statement will return the following result:
| Name | Salary | Salary Classification |
|---|---|---|
| John Doe | 5000 | Low |
| Jane Doe | 5500 | Medium |
| Bob Smith | 6000 | High |
| Alice Johnson | 6500 | High |
In this example, the CASE expression evaluates the salary of each employee and returns the corresponding salary classification based on the salary range. The result is displayed in the “Salary Classification” column of the result set.
Nested CASE Expressions
SQL allows the use of nested CASE expressions, where a CASE expression can be used as the result in another CASE expression. This can be useful in situations where multiple conditions need to be evaluated and the result depends on the outcome of these conditions.
The following example demonstrates the use of nested CASE expressions:
SELECT
Name,
Salary,
CASE
WHEN Salary BETWEEN 0 AND 5000 THEN 'Low'
WHEN Salary BETWEEN 5001 AND 8000 THEN
CASE
WHEN Name LIKE 'J%' THEN 'Medium-J'
ELSE 'Medium-Other'
END
ELSE 'High'
END AS 'Salary Classification'
FROM employees;
The above SQL statement will return the following result:
| Name | Salary | Salary Classification |
|---|---|---|
| John Doe | 5000 | Low |
| Jane Doe | 5500 | Medium-J |
| Bob Smith | 6000 | High |
| Alice Johnson | 6500 | High |
In this example, the outer CASE expression classifies the employees into two categories: “Low” and “High”. The inner CASE expression further classifies the employees with a salary between 5001 and 8000 and a name starting with “J” as “Medium-J” and the rest as “Medium-Other”. The result is displayed in the “Salary Classification” column of the result set.
Conclusion
The SQL CASE expression is a powerful tool for creating conditional statements in SQL. It allows for the evaluation of multiple conditions and the return of a result based on the first true condition. The use of nested CASE expressions can further extend the functionality of the CASE expression and make it easier to implement complex conditional statements in SQL.
Overall, the CASE expression is an essential part of SQL and is widely used in various applications and industries to handle data manipulation and analysis tasks.
