Self join: employees and managers
35 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)An employee table has a manager_id column that points to another row in the same table. The question "who is each employee's manager?" needs two rows of one table side by side.
Joining a table to itself
A self join is an ordinary join in which a table is joined to itself. The table appears twice under two different aliases, such as e for the employee and m for the manager.
Each employee row stores the id of another employee: the manager. To show the manager's name, read the same table twice.
Example 1 · Two copies of one staff table
DROP TABLE IF EXISTS college_staff;CREATE TABLE college_staff ( staff_id INT PRIMARY KEY, name VARCHAR(40), manager_id INT);INSERT INTO college_staff VALUES (1, 'Principal Rao', NULL), (2, 'HOD Iyer', 1), (3, 'Prof. Khan', 2), (4, 'Prof. Das', 2), (5, 'HOD Bose', 1);
-- Before: one table, manager_id points to another row of itSELECT * FROM college_staff ORDER BY staff_id;
-- After: copy e (the staff member) joined to copy m (their manager)SELECT e.name AS staff, m.name AS reports_toFROM college_staff eJOIN college_staff m ON e.manager_id = m.staff_idORDER BY e.staff_id;+----------+---------------+------------+ | staff_id | name | manager_id | +----------+---------------+------------+ | 1 | Principal Rao | NULL | | 2 | HOD Iyer | 1 | | 3 | Prof. Khan | 2 | | 4 | Prof. Das | 2 | | 5 | HOD Bose | 1 | +----------+---------------+------------+ +------------+---------------+ | staff | reports_to | +------------+---------------+ | HOD Iyer | Principal Rao | | Prof. Khan | HOD Iyer | | Prof. Das | HOD Iyer | | HOD Bose | Principal Rao | +------------+---------------+
What this code does
- Prof. Khan's row stores
manager_id = 2; copymfinds row 2, HOD Iyer, and the two are glued into one result row. - Principal Rao has
manager_idNULL, so the inner join finds no partner and leaves the principal out: 4 rows from 5 staff.
A self join is needed when a column points to another row of the same table: employees.manager_id stores the emp_id of another employee, and categories.parent_id stores the id of another category.
Without a self join you could only read the manager's id, not the manager's name. Imagine two printed copies of the same staff list: in copy one you find the employee and read the manager's id, in copy two you look that id up. The self join does this for every row. Nothing new is needed in SQL: join the two copies with ON e.manager_id = m.emp_id, the employee copy's foreign key equal to the manager copy's primary key.
Aliases make it readable
In a self join aliases are required, because the two copies have the same name. Without them MySQL cannot tell the copies apart and stops with ERROR 1066 (42000): Not unique table/alias: 'employees'.
Example 2 · Error: the same table twice without aliases
SELECT employees.name, employees.nameFROM employeesJOIN employees ON employees.manager_id = employees.emp_id;ERROR 1066 (42000): Not unique table/alias: 'employees'
What this code does
- Both copies are called
employees, soemployees.namecould mean either one. MySQL refuses the query with error 1066. - The fix is two aliases with clear roles:
FROM employees e JOIN employees m ON e.manager_id = m.emp_id.
Pick aliases that say each copy's role, not just a and b. e for the employee and m for the manager make the condition read like a sentence: "the employee's manager id is the manager's employee id". Then e.name is the employee's name and m.name is the manager's name.
Rename the output columns too (e.name AS employee, m.name AS manager): both come from the same column, so the result would otherwise show two columns headed name.
ON e.emp_id = m.manager_id makes e the manager and m the report. Read the condition aloud before you run it.Employees with their managers
A hierarchy is data in which each row points to a parent row of the same table, such as an employee to a manager or a category to its parent category.
e JOIN m ON e.manager_id = m.emp_id pairs each employee with their manager. Being an inner join, it drops the person at the top: the managing director has manager_id NULL, which matches no one.
Example 3 · Every employee with a manager or 'No manager'
SELECT e.emp_id, e.name AS employee, COALESCE(m.name, 'No manager') AS managerFROM employees eLEFT JOIN employees m ON e.manager_id = m.emp_idORDER BY e.emp_id;+--------+----------------+---------------+ | emp_id | employee | manager | +--------+----------------+---------------+ | 1 | Vikram Rao | No manager | | 2 | Anita Desai | Vikram Rao | | 3 | Karthik Raj | Anita Desai | | 4 | Pooja Gupta | Anita Desai | | 5 | Suresh Babu | Anita Desai | | 6 | Neha Joshi | Vikram Rao | | 7 | Amit Shah | Neha Joshi | | 8 | Farah Ali | Neha Joshi | | 9 | Lakshmi Menon | Vikram Rao | | 10 | Rohan Das | Lakshmi Menon | | 11 | Gita Reddy | Vikram Rao | | 12 | Manoj Pillai | Gita Reddy | | 13 | Tanvi Kulkarni | Vikram Rao | +--------+----------------+---------------+
What this code does
- The
LEFT JOINkeeps Vikram Rao, who has no manager, so all 13 employees appear. COALESCEreplaces hisNULLmanager name with 'No manager'. Everyone else shows the name found in copym.
Example 4 · Direct reports per manager
SELECT m.name AS manager, COUNT(*) AS direct_reportsFROM employees eJOIN employees m ON e.manager_id = m.emp_idGROUP BY m.emp_id, m.nameORDER BY direct_reports DESC, manager;+---------------+----------------+ | manager | direct_reports | +---------------+----------------+ | Vikram Rao | 5 | | Anita Desai | 3 | | Neha Joshi | 2 | | Gita Reddy | 1 | | Lakshmi Menon | 1 | +---------------+----------------+
What this code does
- Each joined row is one employee–manager pair, so grouping by the manager and counting rows gives the number of people who report to them directly.
- Only people who manage someone appear. Vikram Rao has 5 direct reports; Anita Desai's 3 reports are not counted again for Vikram, because a self join looks one level up only.
Example 5 · Who earns more than their manager?
DROP TABLE IF EXISTS branch_staff;CREATE TABLE branch_staff ( staff_id INT PRIMARY KEY, name VARCHAR(40), manager_id INT, salary INT);INSERT INTO branch_staff VALUES (1, 'Meera', NULL, 90000), (2, 'Sunil', 1, 60000), (3, 'Ritu', 1, 95000), (4, 'Ajay', 2, 65000), (5, 'Kavya', 2, 55000);
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salaryFROM branch_staff eJOIN branch_staff m ON e.manager_id = m.staff_idWHERE e.salary > m.salaryORDER BY e.name;+----------+--------+---------+----------------+ | employee | salary | manager | manager_salary | +----------+--------+---------+----------------+ | Ajay | 65000 | Sunil | 60000 | | Ritu | 95000 | Meera | 90000 | +----------+--------+---------+----------------+
What this code does
- After the join, each row holds an employee's salary and their manager's salary side by side, so a plain
WHERE e.salary > m.salarycompares them. - Ritu earns more than Meera and Ajay earns more than Sunil. Meera is not checked at all: with no manager, the inner join drops her, which is what the question wants.
Use LEFT JOIN to keep every employee, and COALESCE(m.name, 'No manager') to show a readable value for the top. Once the two copies are joined, all the usual tools work on them: GROUP BY m.name with COUNT(*) counts each manager's direct reports, and WHERE e.salary > m.salary compares a row with its manager's row.
"Find employees who earn more than their manager" is one of the most asked SQL interview questions, and it is exactly this self join plus one WHERE.
Predict the output
Predict the self join
There are 13 employees. Only Vikram Rao, the top person, has no manager.
Round 1 of 2
LEFT JOIN employees mm ON m.manager_id = mm.emp_id). Any number of levels needs a recursive query, covered with common table expressions in the next module.Pairs from the same table
A self join can also build pairs of rows from one table: two employees on the same salary in the same department, two customers from the same city, two players from the same team.
Example 6 · Same department, same salary: pairs without duplicates
-- <> removes self-pairs but lists each pair twiceSELECT a.name AS first, b.name AS second, a.salaryFROM employees aJOIN employees b ON a.dept_id = b.dept_id AND a.salary = b.salary AND a.emp_id <> b.emp_idORDER BY a.name;
-- < keeps exactly one row per pairSELECT a.name AS first, b.name AS second, a.salaryFROM employees aJOIN employees b ON a.dept_id = b.dept_id AND a.salary = b.salary AND a.emp_id < b.emp_idORDER BY a.name;+-------------+-------------+-----------+ | first | second | salary | +-------------+-------------+-----------+ | Amit Shah | Farah Ali | 85000.00 | | Farah Ali | Amit Shah | 85000.00 | | Karthik Raj | Pooja Gupta | 120000.00 | | Pooja Gupta | Karthik Raj | 120000.00 | +-------------+-------------+-----------+ +-------------+-------------+-----------+ | first | second | salary | +-------------+-------------+-----------+ | Amit Shah | Farah Ali | 85000.00 | | Karthik Raj | Pooja Gupta | 120000.00 | +-------------+-------------+-----------+
What this code does
- With
<>, Karthik–Pooja and Pooja–Karthik are both listed, so 2 real pairs become 4 rows. - With
a.emp_id < b.emp_id, only the order with the smaller id first survives: one row per pair. This is the standard pattern for pairs from one table.
Joining on the shared value alone (ON a.city = b.city) pairs every row with itself (Ravi with Ravi) and lists every real pair twice (Ravi–Meena and Meena–Ravi). Adding a.customer_id < b.customer_id fixes both at once: a row is never smaller than itself, and of the two mirrored pairs only one has the smaller id on the left.
a.id <> b.id removes the self-pairs but keeps both mirrored copies. Use <> only when order matters, such as "who could swap seats with whom" listed from each person's side.
Find the bug
Find the wrong line
Each self join has one wrong line. Click it.
Show each employee with the manager's name.
Round 1 of 2Next step
Check your understanding
12 quick questions