Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 25 of 49

Self joins: a table joined to itself

Join a table to itself with two aliases to read manager and employee pairs, compare rows in the same table, and keep top-level rows with a LEFT JOIN.

15 minCombining tablesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite a self join with two aliases to show each employee next to their manager and to compare rows of one table with each other.

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.

sql
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;
result
+----------+---------+
| 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:

sql
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;
result
+----+----------+----------------------+---------+
| 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:

sql
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;
result
+---------+----------------------+----------------+
| 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:

sql
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;
result
+----------+----------+-------------------+---------------+
| 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:

sql
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);
result
+----------+--------+---------+----------------+-------+
| 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:

sql
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;
result
+----------+---------+---------------+
| 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 JOIN for hierarchies.
  • Joining on the wrong columns. e.id = m.id pairs each row with itself. The pair you want is e.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
sql
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;
result
+----------+-------------------+---------+-------------------+
| 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.

Check yourself

1. Why do you need two different aliases in a self join?
2. Meera has manager_id NULL. What happens to her in employees e INNER JOIN employees m ON e.manager_id = m.id?
3. What does a.id < b.id do in a query that pairs employees with the same job title?