SQL Tutorial › Joins & Set Operators › SELF JOIN

SQL SELF JOIN

Q: What is a self join in SQL?

A self join is a normal join (inner or outer) in which a table is joined to itself, using two different aliases.

Q: When do you use a self join?

For hierarchies such as employees and managers, for comparing rows within the same table, and for finding pairs or duplicates.

Q: Is SELF JOIN a keyword?

No. You write JOIN or LEFT JOIN with the same table twice.

IntermediateLesson 36 of 699 min read6 queries run on MySQL 8.4

A self join joins a table to itself. It is how you answer questions about rows that point to other rows of the same table: an employee and their manager, two customers in the same city, a product and a cheaper one in its category. The only new idea is that both copies of the table need different aliases.

In this lesson

Join a table to itself with two aliases
List employees with their manager
Keep the top of the hierarchy with LEFT JOIN
Compare and pair rows of the same table

Sample data

manager_id stores the emp_id of each employee’s manager, another row in the same table:

employees · 12 rows (some columns)

emp_id	first_name	job_title	manager_id
100	Meera	CEO	NULL
101	Asha	Engineering Manager	100
102	Rahul	Senior Developer	101
103	Priya	QA Engineer	101
104	Karan	Developer	102
105	Vikram	Sales Manager	100
106	Neha	Sales Executive	105
107	Arjun	Sales Executive	105
108	Kavya	Marketing Lead	100
109	Rohan	Content Writer	108
110	Sneha	HR Manager	100
111	Amit	Intern	101

Employees and their managers

SQL

SELECT e.emp_id, e.first_name AS employee, m.first_name AS managerFROM employees eJOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

emp_id	employee	manager
101	Asha	Meera
102	Rahul	Asha
103	Priya	Asha
104	Karan	Rahul
105	Vikram	Meera
106	Neha	Vikram
107	Arjun	Vikram
108	Kavya	Meera
109	Rohan	Kavya
110	Sneha	Meera
111	Amit	Asha

11 rows in set

How this query works, step by step

employees e is the copy that plays the employee; employees m is the copy that plays the manager.
ON m.emp_id = e.manager_id finds, for each employee, the row whose id equals their manager_id.
The CEO’s manager_id is NULL, so she has no match and the inner join drops her: 11 rows.

Keep the CEO with LEFT JOIN

SQL

SELECT e.first_name AS employee,       COALESCE(m.first_name, '(no manager)') AS managerFROM employees eLEFT JOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

employee	manager
Meera	(no manager)
Asha	Meera
Rahul	Asha
Priya	Asha
Karan	Rahul
Vikram	Meera
Neha	Vikram
Arjun	Vikram
Kavya	Meera
Rohan	Kavya
Sneha	Meera
Amit	Asha

12 rows in set

Count direct reports

Group by the manager copy to see how many people report to each manager:

SQL

SELECT m.emp_id, m.first_name AS manager, COUNT(*) AS direct_reportsFROM employees eJOIN employees m ON m.emp_id = e.manager_idGROUP BY m.emp_id, m.first_nameORDER BY direct_reports DESC, m.emp_id;

ResultMySQL 8.4

emp_id	manager	direct_reports
100	Meera	4
101	Asha	3
105	Vikram	2
102	Rahul	1
108	Kavya	1

5 rows in set

How this query works, step by step

Each row of the join is one employee-manager pair.
Grouping by the manager counts their pairs.
Meera has 4 direct reports and Asha 3. People who manage nobody do not appear.

Employees who earn more than their manager

A classic interview question:

SQL

SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salaryFROM employees eJOIN employees m ON m.emp_id = e.manager_idWHERE e.salary > m.salary;

ResultMySQL 8.4

Empty set

In the sample data nobody does, so the result is an Empty set. Change the condition to e.salary > m.salary * 0.75 to find people paid close to their manager.

Pairs of rows

Employees who work in the same city, each pair listed once:

SQL

SELECT a.first_name AS person_1, b.first_name AS person_2, a.cityFROM employees aJOIN employees b ON b.city = a.city AND a.emp_id < b.emp_idWHERE a.city = 'Pune'ORDER BY a.emp_id, b.emp_id;

ResultMySQL 8.4

person_1	person_2	city
Priya	Kavya	Pune
Priya	Rohan	Pune
Kavya	Rohan	Pune

3 rows in set

How this query works, step by step

b.city = a.city pairs people from the same city.
a.emp_id < b.emp_id removes pairs of a person with themselves and keeps only one order of each pair (Priya-Kavya, not also Kavya-Priya).
Pune has three employees, so there are 3 pairs.

Two levels up

Chain the self join to reach the manager’s manager:

SQL

SELECT e.first_name AS employee, m.first_name AS manager, g.first_name AS managers_managerFROM employees eJOIN employees m ON m.emp_id = e.manager_idJOIN employees g ON g.emp_id = m.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

employee	manager	managers_manager
Rahul	Asha	Meera
Priya	Asha	Meera
Karan	Rahul	Asha
Neha	Vikram	Meera
Arjun	Vikram	Meera
Rohan	Kavya	Meera
Amit	Asha	Meera

7 rows in set

For any depth, use a recursive CTE.

Exercise: Same-price products

List pairs of different products in the same category where the first is cheaper than the second, for the Stationery and Electronics categories.

Expected resultMySQL 8.4

category	cheaper	dearer
Electronics	Wireless Mouse	USB-C Hub
Electronics	Wireless Mouse	Mechanical Keyboard
Electronics	USB-C Hub	Mechanical Keyboard
Stationery	Gel Pens	Notebook Pack

4 rows in set

Hint 1

Join products a to products b on category, with a.price < b.price.

SQL

SELECT a.category, a.product_name AS cheaper, b.product_name AS dearerFROM products aJOIN products b ON b.category = a.category AND a.price < b.priceWHERE a.category IN ('Stationery', 'Electronics')ORDER BY a.category, a.price, b.price;

ResultMySQL 8.4

category	cheaper	dearer
Electronics	Wireless Mouse	USB-C Hub
Electronics	Wireless Mouse	Mechanical Keyboard
Electronics	USB-C Hub	Mechanical Keyboard
Stationery	Gel Pens	Notebook Pack

4 rows in set

Quick quiz

1. Why does a self join need two aliases?

Without aliases, employees.emp_id would be ambiguous.

2. Why is the CEO missing from employees e JOIN employees m ON m.emp_id = e.manager_id?

3. What does a.emp_id < b.emp_id do in a pairs query?

SELF JOIN FAQ

What is a self join in SQL?

A self join is a normal join (inner or outer) in which a table is joined to itself, using two different aliases.

When do you use a self join?

For hierarchies such as employees and managers, for comparing rows within the same table, and for finding pairs or duplicates.

Is SELF JOIN a keyword?

No. You write JOIN or LEFT JOIN with the same table twice.