Self-Joins for Hierarchical Data
What you'll learn: How to use self-joins to query parent-child relationships within a single table, like employees reporting to managers.
The Core Idea
Hierarchical data stores parent-child relationships in the same table—for example, an employees table where each employee has a manager_id that references another employee's id. To see both employee and manager information together, you join the table to itself using different aliases.
Think of it like a family tree stored in one notebook: to see who someone's parent is, you look up that person's entry, find their parent's ID, then flip back to find that person's full details.
Basic Pattern
SELECT
e.id,
e.name AS employee_name,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
Here, e represents employees and m represents managers—but they're the same table. The ON clause connects each employee's manager_id to their manager's id.
We use LEFT JOIN (not INNER JOIN) because top-level employees (like CEOs) have no manager—their manager_id is NULL. LEFT JOIN keeps them in results with manager_name as NULL.
Why This Matters
Self-joins unlock hierarchical queries:
- Organizational charts (who reports to whom)
- Category trees (parent categories, subcategories)
- Task dependencies (which tasks must complete before others)
The key insight: treat one copy of the table as "children" and the other as "parents," then connect them through the relationship column.
Key Takeaway: Self-joins let you query parent-child relationships by joining a table to itself with different aliases, connecting rows through a reference column like manager_id.