A self-join is a type of join where a table is joined with itself.
It allows you to combine rows from the same table based on a related column, creating a new result set.
Self-joins are commonly used when you have a hierarchical relationship within a table, such as when you have a parent-child relationship, supervisor-employee relationship, or when you need to compare data within the same table.
Let’s explore a few examples of self-joins to understand how they work:
Example 1: Employee and Manager Relationship
Consider a table “Employee” with the following columns: EmpID, EmpName, JobTitle, and ManagerID. The ManagerID column contains the ID of the employee’s manager.
EmpID | EmpName | JobTitle | ManagerID |
---|---|---|---|
1 | John Smith | Manager | NULL |
2 | Jane Doe | Analyst | 1 |
3 | Mike Johnson | Analyst | 1 |
4 | Sarah Parker | Senior Analyst | 1 |
5 | Lisa Adams | Analyst | 3 |
6 | James Brown | Senior Analyst | 3 |
Now, let’s perform a self-join to get the names of employees and their respective managers:
SELECT E1.EmpName AS EmployeeName, E2.EmpName AS ManagerName
FROM Employee E1
LEFT JOIN Employee E2 ON E1.ManagerID = E2.EmpID;
Result of the join :
EmployeeName | ManagerName |
---|---|
John Smith | NULL |
Jane Doe | John Smith |
Mike Johnson | John Smith |
Sarah Parker | John Smith |
Lisa Adams | Mike Johnson |
James Brown | Mike Johnson |
Explanation: In this example, we use a LEFT JOIN to match each employee’s ManagerID with the EmpID of their respective managers. We get the EmployeeName and ManagerName from the self-join, and since the manager of the top-level manager (John Smith) is NULL, it indicates that John Smith is the highest-level manager.