SQL Self Join Explained: Joining a Table to Itself
A self join joins a table to itself. It is how you compare rows within the same table: employees and their managers, users who referred other users, or flights that connect. The trick is to treat the two copies as two separate tables with different aliases.
Examples run in MySQL 8 and PostgreSQL · Updated
In short
- Give each copy of the table its own alias, such as
eandm. - The join condition links a column in one copy to a column in the other.
- Use
LEFT JOINto keep rows that have no partner. - Self joins are common for hierarchies and row-to-row comparisons.
Two copies of one table
In employees, manager_id points at another employee's emp_id. To show each employee next to their manager, read the table twice: e for the employee and m for the manager, joined on e.manager_id = m.emp_id.
Employees without a manager (Aarav, Meera, Isha and Zoya) have no match. An inner join would drop them; a left join keeps them with a NULL manager.
The example tables
Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.
employees
| emp_id | name | dept_id | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Aarav | 10 | 95000 | NULL | 2021-03-01 |
| 2 | Diya | 10 | 88000 | 1 | 2022-06-15 |
| 3 | Kabir | 10 | 72000 | 1 | 2023-01-10 |
| 4 | Meera | 20 | 64000 | NULL | 2020-11-20 |
| 5 | Rohan | 20 | 58000 | 4 | 2024-02-05 |
| 6 | Isha | 30 | 61000 | NULL | 2022-09-12 |
| 7 | Arjun | 20 | 64000 | 4 | 2023-07-30 |
| 8 | Zoya | NULL | 52000 | NULL | 2024-05-01 |
Examples
Employee and manager names
A left self join keeps everyone, including people who have no manager.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;| employee | manager |
|---|---|
| Aarav | NULL |
| Diya | Aarav |
| Kabir | Aarav |
| Meera | NULL |
| Rohan | Meera |
| Isha | NULL |
| Arjun | Meera |
| Zoya | NULL |
Compare a row with its partner
Employees whose salary is within 10,000 of their manager's. An inner join is right here: people without managers cannot qualify.
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary - 10000
ORDER BY e.name;| name | salary | manager | manager_salary |
|---|---|---|---|
| Arjun | 64000 | Meera | 64000 |
| Diya | 88000 | Aarav | 95000 |
| Rohan | 58000 | Meera | 64000 |
Common mistakes
- Missing aliases. Without distinct aliases every column is ambiguous. Always alias both copies and prefix every column.
- Joining the wrong direction.
e.emp_id = m.manager_idfinds each employee's reports, not their manager. Read the condition aloud to check it. - Counting pairs twice. When comparing rows symmetrically (for example duplicate detection), add
a.id < b.idso each pair appears once.
Practise Self Join
Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.
Employees Out-Earning Their Managers
Find employees whose salary is strictly higher than their direct manager's.
Managers With Five Direct Reports
Find managers who have at least five people reporting directly to them.
Frequently Bought Together Pairs
Find product pairs that appear in the same order across multiple orders.
Double-Booked Listings
Detect overlapping reservations on the same listing and measure the overlap.
Org Chart Downstream Reports
List everyone who reports, directly or indirectly, to a given manager along with their depth.
Top Inviters by Referred Spend
Rank customers by how much the people they invited pay per book.
Want a full set? Work through the SQL Joins study plan.
Questions people ask
- Is a self join a special join type?
- No. It is an ordinary INNER or LEFT JOIN where both sides happen to be the same table.
- How do I go more than one level up a hierarchy?
- Join the table again for each level, or use a recursive CTE (WITH RECURSIVE) for any depth.