SQL Self Join: Employee and Manager Examples
· 7 min read
A SQL self join is a normal join where both sides are the same table. You give the table two different aliases, so SQL treats it as two copies, and then join a column in one copy to a column in the other.
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
JOIN employees AS m
ON e.manager_id = m.emp_id
ORDER BY e.emp_id;Read it as: "for each employee e, find the row m whose emp_id is that employee's manager_id". There is no special SELF JOIN keyword. It is an ordinary JOIN (or LEFT JOIN) that happens to name the same table twice.
Self joins come up whenever one row points at another row in the same table (employees and managers, parent categories, referrals) or when you compare two rows of the same kind side by side. This guide uses the employee and manager case.
The example table
| emp_id | name | title | manager_id | salary |
|---|---|---|---|---|
| 1 | Maya Chen | Head of Operations | NULL | 9800 |
| 2 | Leo Park | Support Lead | 1 | 6400 |
| 3 | Ines Duarte | Logistics Lead | 1 | 7100 |
| 4 | Sam Okafor | Support Agent | 2 | 6600 |
| 5 | Priya Nair | Support Agent | 2 | 4300 |
| 6 | Tom Reyes | Driver | 3 | 4100 |
manager_id holds the emp_id of the person's manager. Maya runs the team, so her manager_id is NULL.
The query at the top returns:
| employee | manager |
|---|---|
| Leo Park | Maya Chen |
| Ines Duarte | Maya Chen |
| Sam Okafor | Leo Park |
| Priya Nair | Leo Park |
| Tom Reyes | Ines Duarte |
Five rows, not six. Maya has no manager, so the inner join drops her.
Why the aliases matter
Without aliases, SQL cannot tell which copy of the table you mean. Try it:
SELECT name, manager_id
FROM employees
JOIN employees ON manager_id = emp_id;DuckDB stops with Ambiguous reference to column name "manager_id". Postgres refuses even earlier with table name "employees" specified more than once. Either way, the fix is the same: give each copy a name.
Pick aliases that say what each copy is for: e for the employee and m for the manager reads better than a and b, and makes the ON clause easy to check. Prefix every column, and rename the ones you select from both copies (AS employee, AS manager), or the result has two columns called name.
Keep the people with no manager: LEFT JOIN
An inner self join drops rows that have no match. If you want the whole team, including the person at the top, use a LEFT JOIN:
SELECT e.name AS employee,
COALESCE(m.name, '(no manager)') AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.emp_id
ORDER BY e.emp_id;| employee | manager |
|---|---|
| Maya Chen | (no manager) |
| Leo Park | Maya Chen |
| Ines Duarte | Maya Chen |
| Sam Okafor | Leo Park |
| Priya Nair | Leo Park |
| Tom Reyes | Ines Duarte |
All six people are back. COALESCE replaces the NULL manager with a label, which is covered in SQL COALESCE and NULL. The rule is the one from any left join: if the question says "every employee", the employee side must be the left table of a left join.
Compare a row with its manager's row
Once both copies are joined, you can compare their columns like any two tables. Who earns more than their manager?
SELECT e.name AS employee, e.salary,
m.name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m
ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;| employee | salary | manager | manager_salary |
|---|---|---|---|
| Sam Okafor | 6600 | Leo Park | 6400 |
A classic interview question. The self join puts both salaries on one row, so a plain WHERE can compare them. An inner join is right here: someone with no manager has nothing to compare with.
Two levels up: chain another self join
Each extra level of the hierarchy is one more copy of the table. To show each employee's manager and their manager's manager:
SELECT e.name AS employee,
m.name AS manager,
mm.name AS managers_manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.emp_id
LEFT JOIN employees AS mm ON m.manager_id = mm.emp_id
ORDER BY e.emp_id;| employee | manager | managers_manager |
|---|---|---|
| Leo Park | Maya Chen | NULL |
| Ines Duarte | Maya Chen | NULL |
| Sam Okafor | Leo Park | Maya Chen |
| Priya Nair | Leo Park | Maya Chen |
| Tom Reyes | Ines Duarte | Maya Chen |
The second join is a LEFT JOIN on purpose. Leo and Ines report to Maya, and Maya has no manager, so an inner join to mm would silently drop them. If you also want Maya herself in the list, make the first join a left join too.
Pairs of rows from the same table
A self join does not have to follow a pointer like manager_id. You can join on any shared value to build pairs. Which employees share a manager?
SELECT a.name AS colleague_1, b.name AS colleague_2
FROM employees AS a
JOIN employees AS b
ON a.manager_id = b.manager_id
AND a.emp_id < b.emp_id
ORDER BY a.emp_id, b.emp_id;| colleague_1 | colleague_2 |
|---|---|
| Leo Park | Ines Duarte |
| Sam Okafor | Priya Nair |
The a.emp_id < b.emp_id condition does two jobs. It stops a row from pairing with itself, and it keeps only one of "Leo and Ines" and "Ines and Leo". Leave it out and the query returns nine rows: everyone paired with themselves (Tom Reyes, who has no teammate, is "his own colleague"), and every real pair twice. Using <> instead of < removes the self-pairs but keeps both orders. Maya is missing either way, because NULL = NULL is not true.
Common mistakes
Joining in the wrong direction (the AI mistake to watch for)
The most common self join bug is a reversed ON clause. It runs, it returns the right number of rows, and the names look plausible. AI assistants write it often, because e.emp_id = m.manager_id and e.manager_id = m.emp_id look almost the same:
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
JOIN employees AS m
ON e.emp_id = m.manager_id
ORDER BY e.emp_id, m.emp_id;| employee | manager |
|---|---|
| Maya Chen | Leo Park |
| Maya Chen | Ines Duarte |
| Leo Park | Sam Okafor |
| Leo Park | Priya Nair |
| Ines Duarte | Tom Reyes |
Every pair is real, but the roles are flipped: the column labelled manager now holds the direct report. Any count or salary comparison built on top of it answers the opposite question.
How to spot it: say the ON clause out loud. "The employee's manager_id equals the manager's emp_id" is correct. Then check one row you know: the top person should never appear in the employee column with a manager next to them.
Forgetting that the top of the tree has NULL
Inner self joins drop the root of the hierarchy, and chained inner joins drop everyone near it. If the row count is lower than the number of employees, look for a NULL in manager_id.
Walking the whole hierarchy
Chained self joins work when you know the depth. When you do not, use a recursive CTE, which repeats a self join until it runs out of rows:
WITH RECURSIVE chain AS (
SELECT emp_id, name, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, c.level + 1
FROM employees AS e
JOIN chain AS c ON e.manager_id = c.emp_id
)
SELECT name, level
FROM chain
ORDER BY level, emp_id;| name | level |
|---|---|
| Maya Chen | 0 |
| Leo Park | 1 |
| Ines Duarte | 1 |
| Sam Okafor | 2 |
| Priya Nair | 2 |
| Tom Reyes | 2 |
WITH RECURSIVE works in DuckDB, Postgres, MySQL 8 and SQLite. SQL Server and Oracle accept the same idea but leave out the word RECURSIVE. If CTEs are new to you, start with the SQL CTE guide.
FAQ
Is a self join an inner join or an outer join?
Either. "Self join" describes the tables, not the join type. Use JOIN when every row must have a match and LEFT JOIN when rows without one (like the top manager) should stay.
Do I need aliases in a self join?
Yes. With the same table named twice, SQL needs a way to tell the copies apart. Postgres rejects the query without them and DuckDB reports ambiguous columns.
When should I use a self join?
When one row refers to another row of the same table, or when you compare two rows of the same table. For "previous row" comparisons, the SQL LAG function is usually simpler.
Self joins are a common SQL interview question. To practise one on a realistic company's sales team, try the drills on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Who reports to whom” →