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.