Sure, here are some examples of using `CASE` expressions in SQL, particularly with `JOIN` operations in MS SQL Server:
### Example 1: Using `CASE` in SELECT with `JOIN`
Suppose we have two tables: `Employees` and `Salaries`. We want to join these tables and display a calculated column that categorizes employees based on their salary range.
```sql
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
s.Salary,
CASE
WHEN s.Salary < 50000 THEN 'Low'
WHEN s.Salary >= 50000 AND s.Salary < 100000 THEN 'Medium'
ELSE 'High'
END AS SalaryCategory
FROM
Employees e
JOIN
Salaries s ON e.EmployeeID = s.EmployeeID;
```
In this example:
- We join `Employees` and `Salaries` tables on `EmployeeID`.
- The `CASE` expression categorizes employees into 'Low', 'Medium', or 'High' salary categories based on their salary from the `Salaries` table.
### Example 2: Using `CASE` in JOIN Condition
Let's say we have a table `Orders` and another table `Customers`. We want to join these tables and filter based on a condition using `CASE`.
```sql
SELECT
o.OrderID,
o.OrderDate,
c.CustomerName
FROM
Orders o
JOIN
Customers c ON o.CustomerID = c.CustomerID
AND CASE
WHEN o.OrderDate >= '2023-01-01' THEN c.CustomerType = 'Premium'
ELSE c.CustomerType = 'Regular'
END;
```
In this example:
- We join `Orders` and `Customers` tables on `CustomerID`.
- The `CASE` expression within the `JOIN` condition checks the `OrderDate` to determine if the customer type should be 'Premium' or 'Regular'.
### Example 3: Using `CASE` in WHERE Clause
Suppose we have a table `Employees` and we want to filter based on a condition using `CASE` in the `WHERE` clause.
```sql
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
d.DepartmentName
FROM
Employees e
JOIN
Departments d ON e.DepartmentID = d.DepartmentID
WHERE
CASE
WHEN d.DepartmentName = 'IT' THEN e.Salary >= 80000
WHEN d.DepartmentName = 'HR' THEN e.Salary >= 70000
ELSE e.Salary >= 60000
END;
```
In this example:
- We join `Employees` and `Departments` tables on `DepartmentID`.
- The `CASE` expression in the `WHERE` clause filters employees based on their salary, depending on their department.
These examples demonstrate how `CASE` expressions can be used effectively with `JOIN` operations in MS SQL Server to conditionally categorize, filter, or perform calculations based on specific criteria. Adjust the conditions and expressions as per your specific requirements and data structure.
0 Comments