Self Joins

Explanation

A self join is just a regular join where both sides are the same table. You give the table two different aliases and join them on a column that points back into the table — most commonly a hierarchy or a "related to" link.

In the employees dataset, each employee has a manager_id that references another employee's id. A self join pairs every employee with their manager.

Syntax

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id;

Interactive Example

List each employee next to their manager's name. Then show, for each manager, how many people report directly to them.

Common Mistakes

• Forgetting aliases. Without them, id or name is ambiguous and the query fails.
• Using INNER JOIN and losing the top. An INNER self join drops rows with no match (like the CEO, who has no manager). Use LEFT JOIN to keep them.
• Joining on the wrong column. Make sure you link the "child" key to the "parent" key, not the same column on both sides.

Best Practices

• Use LEFT JOIN so top-level rows (no parent) still appear.
• Alias clearly: e for the row, m for the related row.
• Pair self joins with COALESCE to label missing parents as "—" or "None".

Practice Question

Write a self join that shows each employee's name alongside the hire_date of their manager, ordered by the employee's name.

Summary

A self join links a table to itself using aliases, comparing rows that reference each other (like employee/manager). Use LEFT JOIN to retain top-level rows and always qualify columns with aliases.

FAQ