Self Join

A self join joins a table to itself. It sounds odd, but it's the natural way to query relationships within one table — an employee and their manager, products in the same category, or customers from the same city. The trick is giving the table two different aliases.

Employees and their managers

SQL

SELECT e.first_name AS employee,
       m.first_name AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY manager, employee;

Result 11 rows

employee	manager
Hannah	Daniel
Priya	Ethan
Amara	Mei
Jonas	Mei
Tomás	Mei
Daniel	Olivia
Ethan	Olivia
Mei	Olivia
Rafael	Olivia
Lucas	Rafael
Sofia	Rafael

Think of e and m as two copies of the table: one playing the employee, one playing the manager. The join matches each employee's manager_id to the manager's id.

Including people without a manager

SQL

SELECT e.first_name AS employee,
       COALESCE(m.first_name || ' ' || m.last_name, '— (top of company)') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
WHERE e.id <= 4;

Result 4 rows

employee	manager
Olivia	— (top of company)
Rafael	Olivia Brandt
Mei	Olivia Brandt
Jonas	Mei Tanaka

The inner join dropped Olivia, who has no manager. A LEFT JOIN keeps her.

Counting direct reports

SQL

SELECT m.first_name AS manager, COUNT(e.id) AS direct_reports
FROM employees AS m
JOIN employees AS e ON e.manager_id = m.id
GROUP BY m.id, m.first_name
ORDER BY direct_reports DESC;

Result 5 rows

manager	direct_reports
Olivia	4
Mei	3
Rafael	2
Daniel	1
Ethan	1

Comparing rows with each other

SQL

SELECT e.first_name, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary * 0.8;

Result 1 row

first_name	salary	manager	manager_salary
Jonas	85000	Mei	98000

Employees earning more than 80% of their manager's salary.

Pairs within a table

SQL

SELECT a.name AS customer_1, b.name AS customer_2, a.city
FROM customers AS a
JOIN customers AS b ON b.city = a.city AND a.id < b.id;

Result 1 row

customer_1	customer_2	city
Ana Pereira	Julia Santos	Lisbon

Customers who live in the same city. The condition a.id < b.id matters: without it, each customer would pair with themself, and every pair would appear twice (Ana–Julia and Julia–Ana).

Same category, cheaper alternatives

SQL

SELECT p.name AS product, alt.name AS cheaper_alternative, alt.price
FROM products AS p
JOIN products AS alt ON alt.category = p.category AND alt.price < p.price
WHERE p.name = 'Mechanical Keyboard'
ORDER BY alt.price;

Result 3 rows

product	cheaper_alternative	price
Mechanical Keyboard	Smart Plug	14.99
Mechanical Keyboard	USB-C Charger	19.99
Mechanical Keyboard	Wireless Earbuds	59.99

Deeper hierarchies

Each self join climbs one level. To find a manager's manager, join a third copy; for a hierarchy of unknown depth (a whole org chart, nested categories), use a recursive CTE (lesson 32).

SQL

SELECT e.first_name AS employee, m.first_name AS manager, mm.first_name AS managers_manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
JOIN employees AS mm ON mm.id = m.manager_id;

Result 7 rows

employee	manager	managers_manager
Jonas	Mei	Olivia
Amara	Mei	Olivia
Lucas	Rafael	Olivia
Sofia	Rafael	Olivia
Hannah	Daniel	Olivia
Priya	Ethan	Olivia
Tomás	Mei	Olivia

Try it yourself

Find employees who were hired before their own manager.

PreviousFULL & CROSS JOIN NextJoining Multiple Tables