CTE with JOIN
Learn how to use Common Table Expressions with JOIN operations in SQL Server.
What is CTE with JOIN?
A CTE can be used together with SQL JOIN operations to combine data from multiple tables.
This approach is useful when we first want to prepare a temporary result set and then join that result with another table.
Example Tables
Employees Table
EmployeeId | EmployeeName | DepartmentId | Salary
-----------------------------------------------------
101 | Amit | 1 | 60000
102 | Rahul | 2 | 45000
103 | Priya | 1 | 70000
104 | Sneha | 3 | 55000
Departments Table
DepartmentId | DepartmentName
-----------------------------
1 | IT
2 | HR
3 | Finance
CTE with INNER JOIN
We can create a CTE containing employee information and then use an INNER JOIN with the Departments table.
WITH EmployeeData AS
(
SELECT EmployeeId,
EmployeeName,
DepartmentId,
Salary
FROM Employees
)
SELECT
EmployeeData.EmployeeId,
EmployeeData.EmployeeName,
Departments.DepartmentName,
EmployeeData.Salary
FROM EmployeeData
INNER JOIN Departments
ON EmployeeData.DepartmentId = Departments.DepartmentId;
Result
| EmployeeId | EmployeeName | DepartmentName | Salary |
|---|---|---|---|
| 101 | Amit | IT | 60000 |
| 102 | Rahul | HR | 45000 |
| 103 | Priya | IT | 70000 |
| 104 | Sneha | Finance | 55000 |
CTE with WHERE and JOIN
We can filter records inside the CTE and then join the filtered result with another table.
WITH HighSalaryEmployees AS
(
SELECT EmployeeId,
EmployeeName,
DepartmentId,
Salary
FROM Employees
WHERE Salary > 50000
)
SELECT
HighSalaryEmployees.EmployeeName,
Departments.DepartmentName,
HighSalaryEmployees.Salary
FROM HighSalaryEmployees
INNER JOIN Departments
ON HighSalaryEmployees.DepartmentId =
Departments.DepartmentId;
CTE with LEFT JOIN
A CTE can also be used with a LEFT JOIN.
WITH EmployeeData AS
(
SELECT EmployeeId,
EmployeeName,
DepartmentId
FROM Employees
)
SELECT
EmployeeData.EmployeeName,
Departments.DepartmentName
FROM EmployeeData
LEFT JOIN Departments
ON EmployeeData.DepartmentId =
Departments.DepartmentId;
CTE with Multiple Tables
A CTE can also be combined with multiple tables when more complex information is required.
WITH EmployeeData AS
(
SELECT EmployeeId,
EmployeeName,
DepartmentId,
Salary
FROM Employees
)
SELECT
EmployeeData.EmployeeName,
Departments.DepartmentName,
EmployeeData.Salary
FROM EmployeeData
INNER JOIN Departments
ON EmployeeData.DepartmentId =
Departments.DepartmentId
WHERE EmployeeData.Salary > 50000
ORDER BY EmployeeData.Salary DESC;
How CTE with JOIN Works
- First, the CTE creates a temporary result set.
- The CTE result is given a meaningful name.
- The main query joins the CTE with another table.
- The JOIN condition connects related records.
- The final result is returned by the main query.
Advantages
- Makes complex JOIN queries easier to understand.
- Separates data preparation from the main query.
- Improves query readability.
- Allows filtering before performing the JOIN.
- Makes complex query logic easier to maintain.
Important Points
- A CTE can be joined with one or more tables.
- INNER JOIN, LEFT JOIN and other JOIN types can be used.
- JOIN conditions should use related columns.
- Filtering can be performed inside the CTE or in the main query depending on the requirement.
Summary
A CTE can be combined with JOIN operations to create readable and organized SQL queries. The CTE can prepare the required data first, and the main query can then join that result with other tables.