Introduction:
In SQL Server, CASE is a conditional statement that allows you to perform different actions based on different conditions. It provides a way to control the flow of your SQL statements by evaluating one or more expressions and returning a specific result or action based on the result of the evaluation.
The CASE statement has two forms: simple and searched. The simple form is used when you want to compare a single expression to multiple values. The searched form is used when you want to evaluate multiple conditions and return a specific result for each condition.
CASE Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE default_result
END
- The “CASE” keyword indicates the start of the statement.
- Each “WHEN” clause tests a condition and returns a result if the condition is true.
- The “ELSE” clause provides a default result if none of the conditions are true.
- The “END” keyword indicates the end of the statement.
The CASE statement can be used in both the SELECT and WHERE clauses of a SQL query to transform or filter data based on specific conditions. It is a very versatile statement that can be used to create complex expressions with multiple conditions and nested statements.
Let’s Create a Table and insert some sample data in it:
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(255),
LastName varchar (255),
Gender varchar (10),
Salary int
);
INSERT INTO Employees VALUES
(1, ‘John’, ‘Doe’, ‘Male’, 50000),
(2, ‘Jane’, ‘Smith’, ‘Female’, 60000),
(3, ‘Bob’, ‘Johnson’, ‘Male’, 70000),
(4, ‘Alice’, ‘Williams’, ‘Female’, 80000),
(5, ‘Mike’, ‘Brown’, ‘Male’, 90000);
Example 1: If the employee is male, the salary is reduced by 20%; if they are female, it’s reduced by 10%; otherwise, the salary remains the same.
SELECT * FROM Employees;
SELECT FirstName, LastName, Gender, Salary,
CASE Gender
WHEN ‘Male’ THEN Salary * 0.8
WHEN ‘Female’ THEN Salary * 0.9
ELSE Salary
END AS ‘Adjusted Salary’
FROM Employees;
Result:
Nested CASE Statement:
Example 2: If the salary is less than 50,000, the group is “Low”. If the salary is between 50,000 and 70,000, the group is “Average”, and the nested CASE statement is used to further categorize the group based on gender. If the salary is greater than 70,000, the group is “High”
SELECT FirstName, LastName, Gender, Salary,
CASE
WHEN Salary < 50000 THEN ‘Low’
WHEN Salary BETWEEN 50000 AND 70000 THEN
CASE
WHEN Gender = ‘Male’ THEN ‘Average (Male)’
ELSE ‘Average (Female)’
END
ELSE ‘High’
END AS ‘Salary Group’
FROM Employees;
Result:
CASE in JOIN clause
Example 3: If the manager’s salary is not available, the status is “No Manager”. If the employee’s salary is higher than their manager’s, the status is “Higher Salary than Manager”; otherwise, it’s “Lower Salary than Manager”.
SELECT E.FirstName, E.LastName, E.Salary,
CASE
WHEN M.Salary IS NULL THEN ‘No Manager’
WHEN E.Salary > M.Salary THEN ‘Higher Salary than Manager’
ELSE ‘Lower Salary than Manager’
END AS ‘Manager Comparison’
FROM Employees E
LEFT JOIN Employees M ON E.EmployeeID = M.EmployeeID + 1;
Result:
Advantages and limitations of the CASE statement in SQL Server:
Advantages:
- Provides a flexible and concise way to perform conditional logic.
- Can be used in the SELECT, WHERE, and JOIN clauses to transform or filter data based on specific conditions.
- Can be used with aggregate functions to group and summarize data based on different criteria.
- Allows you to create customized labels and categories for data.
- Supports both simple and searched forms for comparing expressions to values or evaluating multiple conditions.
- Can be used with other SQL functions and statements, such as GROUP BY, HAVING, and subqueries.
Limitations:
- Can make queries more complex and difficult to read.
- Nested CASE statements can be difficult to manage.
- May be less efficient than other conditional statements, such as IF and WHILE.
- May require additional coding to handle null values.
- May not be supported by some database platforms or versions.
Overall, the CASE statement is a powerful tool for performing conditional logic in SQL Server. It offers a lot of flexibility and can be used in a variety of ways to transform and filter data based on specific conditions. However, it’s important to be aware of its limitations and consider alternative approaches when appropriate.
Conclusion:
In conclusion, the CASE statement in SQL Server is a powerful and versatile tool that allows for conditional logic in queries. It can be used to transform data, perform calculations, and filter results based on specific conditions. It is flexible and can be used in a variety of ways, from simple queries to complex calculations. However, it does have some limitations, such as its limited support for subqueries and the potential for creating convoluted and difficult-to-read queries. Overall, the CASE statement is a valuable tool for any SQL developer to have in their toolkit.