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.

Skip to the 4 exercises ↓

Syntax

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
FROM employees e
The table in its first role: the employee. e is 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 id equals the employee's manager_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.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Live queryEach employee with their manager's name
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 7 rows
namemanager
OmarMaya
LenaMaya
ZoeRaj
IvanRaj
TheoNora
KimRaj
PiaMaya

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.

Live queryNo aliases: the database can't tell the copies apart
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run

SQLite says: ambiguous column name: name

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.

Live queryEvery employee, with or without a manager
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 10 rows
namemanager
MayaNULL
OmarMaya
LenaMaya
RajNULL
ZoeRaj
IvanRaj
NoraNULL
TheoNora
KimRaj
PiaMaya

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.

Live queryBackwards: Omar becomes Maya's manager
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 7 rows
namemanager
MayaOmar
MayaLena
RajZoe
RajIvan
NoraTheo
RajKim
MayaPia

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.

Live queryWho earns more than their manager?
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 1 row
namesalarymanagermanager_salary
Pia75000Maya72000

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.

Live queryColleagues in the same department
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 13 rows
namecolleaguedepartment
MayaLenaSales
MayaOmarSales
MayaPiaSales
OmarLenaSales
OmarPiaSales
LenaPiaSales
RajIvanEngineering
RajKimEngineering
RajZoeEngineering
ZoeIvanEngineering
ZoeKimEngineering
IvanKimEngineering
NoraTheoSupport

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.

Exercise 1Salary next to the manager's salary

For every employee who has a manager, return the employee's name, the employee's salary and their manager's salary.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT e.name, e.salary, m.salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
Result · 7 rows
namesalarysalary
Omar5800072000
Lena5800072000
Zoe8800095000
Ivan8100095000
Theo4300046000
Kim8800095000
Pia7500072000

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.

Exercise 2Everyone, with 'no manager' for the heads

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT e.name, COALESCE(m.name, 'no manager')
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
Result · 10 rows
nameCOALESCE(m.name, 'no manager')
Mayano manager
OmarMaya
LenaMaya
Rajno manager
ZoeRaj
IvanRaj
Norano manager
TheoNora
KimRaj
PiaMaya

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.

Exercise 3Pairs with the same salary

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
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
Result · 2 rows
namenamesalary
OmarLena58000
ZoeKim88000

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.

Exercise 4Direct reports per person, including zero

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10PiaSales7500012024-01-08
Ctrl/⌘ + Enter to run
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
Result · 10 rows
nameCOUNT(r.id)
Maya3
Omar0
Lena0
Raj3
Zoe0
Ivan0
Nora1
Theo0
Kim0
Pia0

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.

Keep going

Lesson 3 is seven JOIN drills on a plant shop's customers and orders: writing the ON, the wrong-key trap, LEFT JOIN with counts, finding what's missing, a condition in ON versus WHERE, and counting after a join. It's free, like lessons 1, 2 and 4 and the level test.

Related

SQL JOIN: INNER and LEFT, with live queriesSQL NULL: IS NULL, COALESCE and the NOT IN trapSQL interview questions, with answersSQL JOIN practice exercisesSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Table Expressions (table and column aliases). Last checked November 2026.