A table can be joined multiple times in the same query by assigning different aliases to each instance.
This technique is useful when one table contains several relationships to itself or when a record references multiple records in the same table.
An employee may have both a manager and a mentor stored in the Employees table.
SELECT
e.EmployeeName,
m.EmployeeName AS ManagerName,
r.EmployeeName AS MentorName
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmployeeID
LEFT JOIN Employees r
ON e.MentorID = r.EmployeeID;This pattern is useful for employee hierarchies, referral relationships, organizational structures, and parent-child data.
- The same table can be referenced multiple times.
- Each instance needs a different alias.
- Different aliases represent different relationships.
- This is an extension of SELF JOIN.
- It is useful for hierarchical and relationship-based data.
Can the same table be joined twice?
Yes. The table can be referenced multiple times using different aliases.
Why are different aliases required?
Each alias identifies a different instance or relationship of the same table.