← All Kits · SQL Kit

Joining a Table to Itself: Managers, Pairs and the Same Table Twice

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

Employees and their managers are both employees, so both live in the same table. To show a name next to a manager name you have to join that table to itself, which feels wrong the first few times and is completely ordinary.

What you do: write the table twice with two aliases, and treat them as two separate tables from then on. On the eight employees below, the inner version returns 6 rows and the LEFT version returns 8.

The short version. A self join is a normal join. The only new thing is that you must name the two copies.

The table

CREATE TABLE employees(emp_id INT, name TEXT, manager_id INT, dept TEXT, salary INT);
INSERT INTO employees VALUES
(1,'Alvarez',NULL,'Exec',120000),
(2,'Brennan',1,'Sales',72000),
(3,'Cho',1,'Sales',68000),
(4,'Diaz',2,'Sales',54000),
(5,'Ellis',2,'Sales',51000),
(6,'Fowler',3,'Support',49000),
(7,'Grant',NULL,'Support',47000),
(8,'Hale',3,'Support',45000);

manager_id points at an emp_id in the same table. Alvarez has no manager, and so does Grant, which turns out to matter.

The query

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

Read the join clause as the sentence it is: the manager copy's emp_id equals the employee copy's manager_id. Getting that the wrong way round is the single most common self join error, and it produces a result that looks fine until you notice the hierarchy is upside down.

Inner or LEFT: 6 rows against 8

SELECT COUNT(*) FROM employees e JOIN employees m ON m.emp_id = e.manager_id;
-- 6

SELECT COUNT(*) FROM employees e LEFT JOIN employees m ON m.emp_id = e.manager_id;
-- 8

The inner join silently loses Alvarez and Grant, because neither has a manager row to match. On a headcount report that is a two-person error out of eight, and it is invisible: the output is a perfectly normal-looking list.

The rule generalises. Any self join up a hierarchy loses the top of it, and any self join down a hierarchy loses the bottom. Decide which end you are prepared to drop, and if the answer is neither, use LEFT JOIN.

Finding pairs in one table

The other main use: rows that share something. Colleagues in the same department, orders from the same customer on the same day, duplicate records.

SELECT a.name, b.name, a.dept
FROM employees a
JOIN employees b ON b.dept = a.dept
WHERE a.emp_id < b.emp_id;

The WHERE line does two jobs and both are essential. Without it, every person is matched with themselves, and every genuine pair appears twice in both orders. With a.emp_id < b.emp_id, each pair is returned exactly once and no row pairs with itself.

Sales has Brennan, Cho, Diaz and Ellis, which gives 6 pairs. Support has Fowler, Grant and Hale, which gives 3. Exec has one person and gives none. Nine rows, and the arithmetic is the check: n people in a department produce n(n-1)/2 pairs.

Three more shapes worth knowing

On this data, Brennan earns 72,000 and their manager Alvarez earns 120,000, so nobody out-earns their manager. Change one number and the query finds it, which is what makes it a useful audit rather than a puzzle.

Three levels is the practical limit for repeated joins. Beyond that, or when the depth varies, you need recursion, and that is a different page.

The mistakes, in the order they happen

1. No aliases. The database cannot resolve emp_id and raises an ambiguous column error. This one is loud, which makes it the best of the five.
2. The join condition reversed. No error. The hierarchy comes out inverted.
3. Inner join up a hierarchy. No error. The top of the tree disappears.
4. No inequality on a pair query. Every pair twice, plus self-matches, so counts double.
5. Single-letter aliases everywhere. Legal, and unreadable in six months. Use emp and mgr rather than a and b when the two copies mean different things.

How to apply this to your own work

1. Find a table in your database that points at itself: a manager id, a parent category, a replaces-this-record id. It is more common than people expect.
2. Write the LEFT JOIN version first, count the NULLs, and ask whether each one is a real top-of-tree or a missing value.
3. Compare the inner and LEFT counts every time. The difference is the number of rows the inner version would have hidden.
4. Name your aliases after the role, not after the alphabet.
5. On any pair query, check the count against n(n-1)/2 before believing it.

The one habit to keep

Say the join clause out loud as a sentence about roles: the manager row's id equals the employee row's manager id. A self join has no natural table names to lean on, so the sentence is the only thing keeping the direction straight.

Which table in your database has a column pointing back at its own key?