After this lesson you can join a table to itself. That lets you show each employee beside their manager, find pairs of rows that share something, and read data that refers to other rows in the same table.
Why a table would join itself
In the first lesson of this section you saw that employees.manager_id holds the id of another employee. The manager is not in a separate table. They are a row of employees too. To print "Arjun reports to Meera" you need the employee's row and the manager's row side by side, and both come from the same table.
You do that by writing the table twice in FROM, with two different aliases. The database treats them as two separate tables that happen to hold the same data.
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.id <= 6
ORDER BY e.id;+----------+---------+
| employee | manager |
+----------+---------+
| Arjun | Meera |
| Kavya | Meera |
| Rohan | Arjun |
| Isha | Arjun |
+----------+---------+Read e as "the employee row" and m as "the manager row". The condition e.manager_id = m.id follows the foreign key from the employee to the row it points at. This is an ordinary join, the only twist being that both sides come from one table.
Keeping the top of the hierarchy
Meera has no manager, so her manager_id is NULL. The inner join above drops her, and also every other manager-less person. To keep them, use a LEFT JOIN, which fills the manager columns with NULL:
SELECT e.id, e.first_name AS employee, e.job_title, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;+----+----------+----------------------+---------+
| id | employee | job_title | manager |
+----+----------+----------------------+---------+
| 1 | Meera | Engineering Director | NULL |
| 2 | Arjun | Senior Developer | Meera |
| 3 | Kavya | Senior Developer | Meera |
| 4 | Rohan | Developer | Arjun |
| 5 | Isha | Developer | Arjun |
| 6 | Vikram | Sales Director | NULL |
| 7 | Neha | Account Executive | Vikram |
| 8 | Sameer | Account Executive | Vikram |
| 9 | Tara | Sales Associate | Neha |
| 10 | Dev | Marketing Manager | NULL |
| 11 | Anika | Content Strategist | Dev |
| 12 | Yash | Designer | Dev |
| 13 | Lata | Finance Manager | NULL |
| 14 | Omar | Accountant | Lata |
| 15 | Pooja | Support Lead | NULL |
| 16 | Karan | Support Engineer | Pooja |
| 17 | Sana | Support Engineer | Pooja |
| 18 | Ravi | Consultant | NULL |
| 19 | Diya | Junior Developer | Kavya |
| 20 | Aman | Sales Associate | Neha |
+----+----------+----------------------+---------+Employees 1, 6, 10, 13, 15 and 18 have NULL managers. The first five are heads of their departments. Ravi (18) is the consultant who also has no department.
You can use a COALESCE to print something friendlier than NULL, for example COALESCE(m.first_name, 'nobody'). That function gets its own lesson later in the course.
Counting reports
A self join plus grouping tells you how many people report to each manager. Join the other way round, with the manager as the main row and their reports on the right:
SELECT m.first_name AS manager, m.job_title, COUNT(e.id) AS direct_reports
FROM employees m
INNER JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.first_name, m.job_title
ORDER BY direct_reports DESC, m.first_name;+---------+----------------------+----------------+
| manager | job_title | direct_reports |
+---------+----------------------+----------------+
| Arjun | Senior Developer | 2 |
| Dev | Marketing Manager | 2 |
| Meera | Engineering Director | 2 |
| Neha | Account Executive | 2 |
| Pooja | Support Lead | 2 |
| Vikram | Sales Director | 2 |
| Kavya | Senior Developer | 1 |
| Lata | Finance Manager | 1 |
+---------+----------------------+----------------+Only people who manage someone appear, because the inner join drops everyone with no reports. Use a LEFT JOIN and COUNT(e.id) if you want to list everyone, with zero for those who lead nobody.
Comparing rows in the same table
A self join does not need a foreign key. Any condition will do, so you can compare rows. Here is a query for pairs of employees in the same department who also have the same job title:
SELECT a.first_name AS person_a, b.first_name AS person_b, a.job_title, a.department_id
FROM employees a
INNER JOIN employees b
ON a.department_id = b.department_id
AND a.job_title = b.job_title
AND a.id < b.id
ORDER BY a.department_id, a.id;+----------+----------+-------------------+---------------+
| person_a | person_b | job_title | department_id |
+----------+----------+-------------------+---------------+
| Arjun | Kavya | Senior Developer | 1 |
| Rohan | Isha | Developer | 1 |
| Neha | Sameer | Account Executive | 2 |
| Tara | Aman | Sales Associate | 2 |
| Karan | Sana | Support Engineer | 5 |
+----------+----------+-------------------+---------------+The part a.id < b.id is the important one. Without it each person would match themselves, and each pair would appear twice, once as (Arjun, Kavya) and once as (Kavya, Arjun). Requiring the left id to be smaller keeps a single copy of each pair.
Another comparison: how far below their manager is each person's salary? Join each person to their manager and subtract:
SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salary,
m.salary - e.salary AS gap
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.id IN (2, 3, 7, 8);+----------+--------+---------+----------------+-------+
| employee | salary | manager | manager_salary | gap |
+----------+--------+---------+----------------+-------+
| Arjun | 125000 | Meera | 180000 | 55000 |
| Kavya | 125000 | Meera | 180000 | 55000 |
| Neha | 90000 | Vikram | 150000 | 60000 |
| Sameer | 90000 | Vikram | 150000 | 60000 |
+----------+--------+---------+----------------+-------+If you filter with WHERE e.salary > m.salary instead, you get no rows at all, because nobody in the sample data earns more than their manager. An empty result is still an answer, so read it as one before assuming the query is broken.
Walking up more than one level
One self join looks one step up the chain. To show an employee, their manager and the manager's manager, join twice:
SELECT e.first_name AS employee, m.first_name AS manager, g.first_name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees g ON m.manager_id = g.id
WHERE e.id IN (4, 19)
ORDER BY e.id;+----------+---------+---------------+
| employee | manager | grand_manager |
+----------+---------+---------------+
| Rohan | Arjun | Meera |
| Diya | Kavya | Meera |
+----------+---------+---------------+This works when you know how many levels you need. For a chain of unknown depth, such as "everyone under Meera at any level", you need a recursive query, which is covered at the end of this course.
Differences between databases
Self joins are plain joins and behave the same in MySQL, PostgreSQL, SQLite and SQL Server. Remember that table aliases are required here: the database would not otherwise know which copy a column belongs to. For MySQL examples, see Self joins and cross joins in MySQL.
Common mistakes
- Forgetting aliases or reusing the same one. Both copies need different names, and every column needs a prefix.
- Using INNER JOIN and losing the top level. People without a manager disappear. Use
LEFT JOINfor hierarchies. - Joining on the wrong columns.
e.id = m.idpairs each row with itself. The pair you want ise.manager_id = m.id. - Getting mirrored or self pairs. For comparisons between rows, add
a.id < b.id. - Assuming the hierarchy is only one level deep. One self join gives one level. Deeper trees need more joins or a recursive query.
Exercise
Show each Sales department employee (department 2) with the name and job title of their manager, including the director who has no manager. Then find pairs of employees hired in the same year, listing each pair once.
Show one possible answer
SELECT e.first_name AS employee, e.job_title, m.first_name AS manager, m.job_title AS manager_title
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.department_id = 2
ORDER BY e.id;
SELECT a.first_name AS person_a, b.first_name AS person_b, a.hire_date, b.hire_date AS hire_date_b
FROM employees a
INNER JOIN employees b
ON SUBSTR(a.hire_date, 1, 4) = SUBSTR(b.hire_date, 1, 4)
AND a.id < b.id
ORDER BY a.id, b.id;+----------+-------------------+---------+-------------------+
| employee | job_title | manager | manager_title |
+----------+-------------------+---------+-------------------+
| Vikram | Sales Director | NULL | NULL |
| Neha | Account Executive | Vikram | Sales Director |
| Sameer | Account Executive | Vikram | Sales Director |
| Tara | Sales Associate | Neha | Account Executive |
| Aman | Sales Associate | Neha | Account Executive |
+----------+-------------------+---------+-------------------+
+----------+----------+------------+-------------+
| person_a | person_b | hire_date | hire_date_b |
+----------+----------+------------+-------------+
| Meera | Dev | 2018-03-12 | 2018-10-15 |
| Arjun | Neha | 2019-07-01 | 2019-02-18 |
| Arjun | Pooja | 2019-07-01 | 2019-09-23 |
| Kavya | Sameer | 2020-01-20 | 2020-08-24 |
| Kavya | Omar | 2020-01-20 | 2020-11-09 |
| Rohan | Anika | 2021-06-14 | 2021-01-11 |
| Isha | Yash | 2022-09-05 | 2022-05-30 |
| Isha | Karan | 2022-09-05 | 2022-02-14 |
| Neha | Pooja | 2019-02-18 | 2019-09-23 |
| Sameer | Omar | 2020-08-24 | 2020-11-09 |
| Tara | Sana | 2023-04-03 | 2023-07-17 |
| Yash | Karan | 2022-05-30 | 2022-02-14 |
| Ravi | Diya | 2024-01-08 | 2024-06-10 |
| Ravi | Aman | 2024-01-08 | 2024-03-18 |
| Diya | Aman | 2024-06-10 | 2024-03-18 |
+----------+----------+------------+-------------+The second query takes the year as the first four characters of the date text. That works in SQLite, where dates are stored as text, and SUBSTR also exists in MySQL and PostgreSQL. In SQL Server use SUBSTRING or YEAR(hire_date) instead. In other databases, a proper year function such as EXTRACT(YEAR FROM hire_date) is cleaner, and the date functions lesson covers it.
Key points
- A self join uses the same table twice under two different aliases, such as e for the employee and m for the manager.
- The ON condition links a foreign key to the primary key of the same table: e.manager_id = m.id.
- Use LEFT JOIN when top-level rows, like the Engineering Director with no manager, must stay in the result.
- Self joins also compare rows of one table with each other, for example pairs of people with the same job title. Use a.id < b.id to avoid duplicate and mirrored pairs.