Self-Joins for Hierarchical Data

In this lesson, you will learn how to navigate parent-child relationships within a single table by joining that table to itself.

The Problem of Hierarchies

Sometimes, data is organized in a hierarchy where records refer to other records in the same table. Imagine an Employees table where each row contains an employee_name and a manager_id. Since a manager is also an employee, the manager’s information lives in the same table as their subordinates.

If you want to see a list of employees alongside the names of their managers, a standard JOIN won't work because both the "child" (the employee) and the "parent" (the manager) are stored in the Employees table.

Using the Self-Join

To solve this, we perform a Self-Join. We treat the table as if it were two separate entities by giving it two different aliases (e.g., e for employee and m for manager).

By joining the table to itself, we can match an employee’s manager_id to their manager’s actual employee_id.

SELECT 
    e.name AS Employee, 
    m.name AS Manager
FROM Employees e
LEFT JOIN Employees m 
    ON e.manager_id = m.employee_id;

Breaking Down the Query

1. Aliasing: We define e (the team member) and m (the boss) to keep track of which column belongs to whom.
2. The Join Condition: We link the manager_id (the foreign key inside the employee row) to the employee_id (the primary key of the manager's row).
3. The Result: The engine looks up the manager's name based on the ID provided in the employee's row.

We use a LEFT JOIN here because it is a best practice for hierarchies: if the CEO has a NULL for their manager_id, a regular INNER JOIN would exclude the CEO from the results entirely. A LEFT JOIN ensures the CEO still appears, even without a manager to display.

Key Takeaway: A self-join allows you to relate a record to its own ancestor or parent by treating a single table as two distinct sources using unique aliases.