SQL practice › SQL reference › Self join
SQL self join: a table joined to itself, with queries you can run
A self join is an ordinary JOIN in which both sides are the same table. There is no SELF JOIN keyword: you write the table twice and give each copy its own alias, as in FROM employees e JOIN employees m ON m.id = e.manager_id.
Use it when a row points at another row of the same table (an employee's manager is also an employee), or when you want to compare the rows of one table with each other.
Syntax
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_idFROM employees e- The table in its first role: the employee.
eis an alias, a name for this copy inside the query. JOIN employees m- The same table again, in its second role: the manager. It needs a different alias, or nothing could tell the two copies apart.
ON m.id = e.manager_id- The match rule. An employee's row stores the id of their manager, so the manager is the row whose
idequals the employee'smanager_id.
The table
Ten employees. manager_id holds the id of another employee: Omar's is 1, and employee 1 is Maya. Maya, Raj and Nora head their departments and have no manager, so theirs is NULL.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
| name | manager |
|---|---|
| Omar | Maya |
| Lena | Maya |
| Zoe | Raj |
| Ivan | Raj |
| Theo | Nora |
| Kim | Raj |
| Pia | Maya |
manager_id is NULL and matches nothing.Why two aliases are required
Think of it as two copies of the table: one read as "the employees", one read as "the managers". Without aliases every column exists twice under the same name, and name could be the employee's or the manager's. The database doesn't guess.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
SQLite says: ambiguous column name: name
employees e and employees m, and say which copy every column comes from: SELECT e.name and ON m.id = e.manager_id.LEFT JOIN: keep the people with no manager
The inner join dropped the three department heads. As with any join, LEFT JOIN keeps every row of the first copy and puts NULL where the second copy has nothing to offer.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
| name | manager |
|---|---|
| Maya | NULL |
| Omar | Maya |
| Lena | Maya |
| Raj | NULL |
| Zoe | Raj |
| Ivan | Raj |
| Nora | NULL |
| Theo | Nora |
| Kim | Raj |
| Pia | Maya |
The mistake with no error: the ON written backwards
Both copies have an id and a manager_id, so there are two ways to write the match, and both run. m.id = e.manager_id finds each employee's manager. m.manager_id = e.id finds the opposite: the people who report to e.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
| name | manager |
|---|---|
| Maya | Omar |
| Maya | Lena |
| Raj | Zoe |
| Raj | Ivan |
| Nora | Theo |
| Raj | Kim |
| Maya | Pia |
id, the copy you call the employee on its manager_id. Change the ON to m.id = e.manager_id.Employees who earn more than their manager
Once the two rows sit side by side, comparing them is a plain WHERE: a column of one copy against the same column of the other.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
| name | salary | manager | manager_salary |
|---|---|---|---|
| Pia | 75000 | Maya | 72000 |
> to < and you get the six who earn less than their manager.Pairs of rows, each pair once
A self join doesn't need a pointer column. Join the table to itself on any value two rows can share, here the department, and you get pairs of colleagues. The catch is that the plain version returns too much: every pair twice, and every person paired with themselves.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
| name | colleague | department |
|---|---|---|
| Maya | Lena | Sales |
| Maya | Omar | Sales |
| Maya | Pia | Sales |
| Omar | Lena | Sales |
| Omar | Pia | Sales |
| Lena | Pia | Sales |
| Raj | Ivan | Engineering |
| Raj | Kim | Engineering |
| Raj | Zoe | Engineering |
| Zoe | Ivan | Engineering |
| Zoe | Kim | Engineering |
| Ivan | Kim | Engineering |
| Nora | Theo | Support |
a.id <> b.id you get 26: every pair twice, Maya with Omar and Omar with Maya. With no id condition at all you get 36, because each person is also paired with themselves. a.id < b.id keeps exactly one copy of each pair.Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
For every employee who has a manager, return the employee's name, the employee's salary and their manager's salary.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT e.name, e.salary, m.salary
FROM employees e
JOIN employees m ON m.id = e.manager_id| name | salary | salary |
|---|---|---|
| Omar | 58000 | 72000 |
| Lena | 58000 | 72000 |
| Zoe | 88000 | 95000 |
| Ivan | 81000 | 95000 |
| Theo | 43000 | 46000 |
| Kim | 88000 | 95000 |
| Pia | 75000 | 72000 |
The two salaries live in two different rows of the same table, so the table is needed twice: e for the employee and m for the manager, matched with m.id = e.manager_id. The three department heads have no manager and don't appear.
Return every employee's name and their manager's name. For an employee with no manager, show the text no manager instead of NULL. (COALESCE(x, 'text') returns the text when x is NULL.)
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT e.name, COALESCE(m.name, 'no manager')
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id| name | COALESCE(m.name, 'no manager') |
|---|---|
| Maya | no manager |
| Omar | Maya |
| Lena | Maya |
| Raj | no manager |
| Zoe | Raj |
| Ivan | Raj |
| Nora | no manager |
| Theo | Nora |
| Kim | Raj |
| Pia | Maya |
LEFT JOIN keeps Maya, Raj and Nora, with NULL in the manager's columns. COALESCE(m.name, 'no manager') then replaces that NULL with the text.
Find the pairs of employees who earn exactly the same salary. Return the two names and the salary, each pair once, with the employee who has the lower id first.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT a.name, b.name, a.salary
FROM employees a
JOIN employees b ON b.salary = a.salary AND a.id < b.id| name | name | salary |
|---|---|---|
| Omar | Lena | 58000 |
| Zoe | Kim | 88000 |
Joining the table to itself on salary pairs up the rows with equal pay. a.id < b.id removes the self-pairs (Omar with Omar) and the mirror pairs (Lena with Omar), and puts the lower id first.
For every employee, return their name and how many people report directly to them. Someone who manages nobody must be in the result, with 0.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Pia | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT m.name, COUNT(r.id)
FROM employees m
LEFT JOIN employees r ON r.manager_id = m.id
GROUP BY m.id, m.name| name | COUNT(r.id) |
|---|---|
| Maya | 3 |
| Omar | 0 |
| Lena | 0 |
| Raj | 3 |
| Zoe | 0 |
| Ivan | 0 |
| Nora | 1 |
| Theo | 0 |
| Kim | 0 |
| Pia | 0 |
Here the first copy is the manager and the second the people who point at them. LEFT JOIN keeps the seven employees nobody reports to, and COUNT(r.id) gives them 0 because it skips the NULLs.
Questions people ask
What is a self join in SQL?
A join where the same table is on both sides. It isn't a separate kind of join: it's written with JOIN or LEFT JOIN, and the only special part is that the table appears twice under two aliases.
Why does a self join need aliases?
Because both copies have the same table name and the same column names, so employees.name could mean either one. An alias gives each copy its own name for the length of the query. In PostgreSQL, once a table has an alias the original name can't be used in that query at all.
What is a self join used for?
Two things. Following a pointer inside one table: an employee's manager, a category's parent category, a reply's original message. And comparing rows of one table with each other: employees who earn more than their manager, pairs with the same salary or the same department, rows that look like duplicates.
How do I avoid getting each pair twice?
Add a.id < b.id to the join condition. a.id <> b.id only removes the rows paired with themselves; each pair still comes back in both orders. The last live query above shows the three counts.
Which self join questions should I practice for an interview?
Three questions are built directly on a self join: each employee with their manager's name, employees who earn more than their manager, and pairs of rows that share a value. All three are on this page. If the answer must keep the people with no manager, it needs LEFT JOIN.