A self join in SQL is a technique where a table is joined with itself to compare rows within the same table. This can be useful for tasks like finding hierarchical relationships or comparing rows in some way.
Example Scenario
Suppose you have an Employees table with the following structure:
| EmployeeID | Name | ManagerID |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
Query Example
To find each employee along with their manager's name, you can use a self join:
SELECT e.EmployeeID, e.Name AS EmployeeName, m.Name AS ManagerName FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;
Explanation
- Aliases: We use
efor the employees andmfor managers to differentiate between the two instances of the same table. - Join Condition: The
ONclause specifies how to match the rows; in this case, we link an employee'sManagerIDto another employee'sEmployeeID. - LEFT JOIN: This ensures that employees without a manager (like Alice) still appear in the results, with
ManagerNameasNULL.
Result
The query would produce:
| EmployeeID | EmployeeName | ManagerName |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | Alice |
| 3 | Charlie | Alice |
| 4 | David | Bob |
Use Cases
Self joins can be used for:
- Finding hierarchical data (like managers and subordinates).
- Comparing rows within the same table (like finding duplicates).
- Any situation where relationships between rows in the same table need to be explored.