Self joins: a table joined to itself
A self join joins a table to itself, under two different aliases. That lets a row be compared with another row of the same table, such as an employee with their manager.
Why it matters
manager_id points into the same employees table. To show "Hana reports to Ben", you need Hana's row and Ben's row side by side. There is only one table, so you use it twice.
The idea
Give the table two aliases and treat them as two copies: e for the employee, m for the manager. Join on m.emp_id = e.manager_id, meaning "the manager copy's id equals the employee copy's manager_id". Now each result row has the employee's columns and their manager's columns.
Use a LEFT JOIN if you want to keep Asha, who has no manager. Self joins also compare rows with each other — pairs in the same department, or rows on consecutive days.
Two photocopies of the staff list on your desk. On the left copy you find Hana and read "manager: 2". On the right copy you find person 2 — Ben — and write his name next to hers.
The photocopies are real copies. SQL doesn't copy anything: the aliases are just two names for the same table, which is why you must prefix every column (e.name, m.name) — otherwise "name" is ambiguous.
Syntax
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;How it works
- e and m are both the employees table.
- For Hana (manager_id 2), find the m row with emp_id 2: Ben.
- The result row holds e.name = Hana and m.name = Ben.
- Asha's manager_id is NULL, so she has no match; LEFT JOIN keeps her with a NULL manager.
- To compare rows, add a condition:
a.emp_id < b.emp_idmakes each pair appear once and stops a row pairing with itself.
Key points
- A self join uses the same table twice with two aliases.
- Read the ON condition carefully:
m.emp_id = e.manager_idmeans "m is e's manager". - Use LEFT JOIN to keep rows whose pointer is NULL (the top of the tree).
a.id < b.idgives each pair once;a.id <> b.idgives each pair twice.- A self join reaches one level. For a whole chain of managers, use a recursive CTE (Unit 4).
Common mistakes
- Swapping the condition (
e.emp_id = m.manager_id), which lists each person's reports instead of their manager. - Forgetting the table prefixes in a self join, so the database can't tell which copy's
nameyou mean. - Using INNER JOIN and losing the CEO.
Not true: An INNER self join from employees to their managers also returns the person at the top.
In fact: The person at the top has no manager, so an INNER JOIN drops them. Use LEFT JOIN to keep every employee.
In the interview
A self join joins a table to itself with two aliases so I can compare rows within it — the classic case is employees and managers through a self-referencing manager_id. I join the manager alias's primary key to the employee alias's manager_id, and use a LEFT JOIN if the top of the hierarchy must appear. For pair comparisons I add id less-than id, to avoid self-pairs and mirrored duplicates.
Part of Unit 3: Joins: combining tables in the free SQL course.