SQL Self Join

A self join in SQL is a regular join, but the table is joined with itself. This is useful for comparing rows within the same table. A common use case is to find hierarchical relationships, such as an employee and their manager stored in the same table.

Here’s a basic example to illustrate the concept:

Example Table: Employees

Scenario

We want to find each employee along with their manager's name.

SQL Self Join Query

SELECT 
    e1.EmployeeID AS EmployeeID,
    e1.EmployeeName AS EmployeeName,
    e2.EmployeeName AS ManagerName
FROM 
    Employees e1
LEFT JOIN 
    Employees e2
ON 
    e1.ManagerID = e2.EmployeeID;

Explanation

• e1 and e2 are aliases for the same table Employees.
• e1.ManagerID = e2.EmployeeID is the join condition that matches employees with their managers.
• We use LEFT JOIN to include employees who do not have a manager (e.g., the CEO).

Output

Detailed Steps:

1. Employee with ID 1 (John):

   • ManagerID is NULL, so there is no matching EmployeeID in e2.
   • Result: EmployeeName = John, ManagerName = NULL.

2. Employee with ID 2 (Jane):

   • ManagerID is 1, which matches EmployeeID of John in e2.
   • Result: EmployeeName = Jane, ManagerName = John.

3. Employee with ID 3 (Jim):

   • ManagerID is 1, which matches EmployeeID of John in e2.
   • Result: EmployeeName = Jim, ManagerName = John.

4. Employee with ID 4 (Jack):

   • ManagerID is 2, which matches EmployeeID of Jane in e2.
   • Result: EmployeeName = Jack, ManagerName = Jane.

5. Employee with ID 5 (Jill):

   • ManagerID is 2, which matches EmployeeID of Jane in e2.
   • Result: EmployeeName = Jill, ManagerName = Jane.

This example shows how a self join can be used to link rows within the same table to find hierarchical or relational data.