The CASE statement in SQL Server is used to execute conditional logic within a SQL query. It evaluates a list of conditions and returns one of multiple possible result expressions. The CASE statement comes in two forms: the simple CASE expression and the searched CASE expression.
Simple CASE Expression
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE Expression
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
END
Take the Leap from Reader to Developer - Join Our SQL Server Course at TrainingHub.io!
Examples
- Example 1: Simple CASE Expression
In this example, we have a table Employees with a column JobTitle. We want to categorize employees based on their job title.
SELECT
EmployeeID,
FirstName,
LastName,
JobTitle,
CASE JobTitle
WHEN 'Manager' THEN 'Management'
WHEN 'Developer' THEN 'Technical'
WHEN 'Analyst' THEN 'Business'
ELSE 'Other'
END AS JobCategory
FROM Employees;
- Example 2: Searched CASE Expression
In this example, we have a table Sales with columns SaleAmount and SaleDate. We want to classify sales into different categories based on the amount.
SELECT
SaleID,
SaleAmount,
SaleDate,
CASE
WHEN SaleAmount < 100 THEN 'Low'
WHEN SaleAmount BETWEEN 100 AND 500 THEN 'Medium'
WHEN SaleAmount > 500 THEN 'High'
ELSE 'Undefined'
END AS SaleCategory
FROM Sales;
Explanation of Components
1. Simple CASE Expression:
- input_expression: The expression to be compared.
- when_expression: The expression against which input_expression is compared.
- result_expression: The value returned if input_expression matches when_expression.
- else_result_expression: The value returned if no when_expression matches. This part is optional.
2. Searched CASE Expression:
- Boolean_expression: A condition that returns a Boolean value (TRUE or FALSE).
- result_expression: The value returned if Boolean_expression is TRUE.
- else_result_expression: The value returned if none of the Boolean_expressions are TRUE. This part is optional.
Notes
- The CASE statement can be used in SELECT, INSERT, UPDATE, and DELETE statements.
- It is useful for transforming data, making it easier to interpret results, and for implementing complex logic within queries.
- Proper use of CASE statements can significantly simplify queries and improve readability.
- Scenario 1: Data Transformation
Transforming data values in a column based on specific conditions.
Example: Categorizing Products by Price Range
SELECT
ProductID,
ProductName,
Price,
CASE
WHEN Price < 50 THEN 'Low'
WHEN Price BETWEEN 50 AND 150 THEN 'Medium'
WHEN Price > 150 THEN 'High'
ELSE 'Unknown'
END AS PriceCategory
FROM Products;
- Scenario 2: Conditional Aggregation
Using CASE statements within aggregate functions to perform conditional aggregation.
Example: Summarizing Sales by Region
SELECT
Region,
SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS Sales2023,
SUM(CASE WHEN Year = 2022 THEN Sales ELSE 0 END) AS Sales2022
FROM SalesData
GROUP BY Region;
- Scenario 3: Conditional Updates
Updating table data conditionally.
Example: Updating Employee Salaries Based on Performance Ratings
UPDATE Employees
SET Salary = CASE
WHEN PerformanceRating = 'Excellent' THEN Salary * 1.10
WHEN PerformanceRating = 'Good' THEN Salary * 1.05
WHEN PerformanceRating = 'Average' THEN Salary * 1.02
ELSE Salary
END;
Boost your data career by enrolling in TrainingHub.io's Data Analyst courses in Toronto, featuring industry-relevant curriculum and expert instructors.
Scenario 4: Filtering Data with CASE in WHERE Clause
Using CASE statements within the WHERE clause for complex filtering.
Example: Filtering Orders Based on Variable Conditions
DECLARE @filterType INT = 1; -- 1 for High Value Orders, 2 for Recent Orders
SELECT
OrderID,
OrderDate,
TotalAmount
FROM Orders
WHERE
CASE
WHEN @filterType = 1 THEN
CASE
WHEN TotalAmount > 1000 THEN 1
ELSE 0
END
WHEN @filterType = 2 THEN
CASE
WHEN OrderDate > '2023-01-01' THEN 1
ELSE 0
END
ELSE 1
END = 1;
- Scenario 5: Conditional Joins
Using CASE statements to dynamically change the join condition.
- Scenario 6: Dynamic Column Selection
Using CASE to select different columns based on conditions.
Example: Selecting Columns Dynamically Based on User Role
DECLARE @UserRole NVARCHAR(50) = 'Admin';
SELECT
EmployeeID,
FirstName,
LastName,
CASE
WHEN @UserRole = 'Admin' THEN Salary
ELSE NULL
END AS Salary,
CASE
WHEN @UserRole = 'Admin' THEN Bonus
ELSE NULL
END AS Bonus
FROM Employees;
- Scenario 7: Handling NULL Values
Using CASE statements to handle NULL values and provide default values.
Example: Replacing NULL Values with Default Text ,Typically we do ISNULL function, Colaesce function
SELECT
CustomerID,
CustomerName,
CASE
WHEN Address IS NULL THEN 'Address Not Provided'
ELSE Address
END AS Address
FROM Customers;
- Scenario 8: Conditional Sorting
Using CASE statements in ORDER BY clause to sort data conditionally.
Example: Sorting Employees by Role Priority
SELECT
EmployeeID,
FirstName,
LastName,
Role
FROM Employees
ORDER BY
CASE
WHEN Role = 'Manager' THEN 1
WHEN Role = 'Developer' THEN 2
WHEN Role = 'Analyst' THEN 3
ELSE 4
END;
Join thousands of learners in Canada who have advanced their careers with TrainingHub.io’s IT online courses.
Suggested blogs
Recent blogs
22 November 2024
|