CodeMIRAI
0 XP

Self joins

2 min read 6 runnable examples Quiz included

Sometimes rows refer to other rows in the same table. Each employee has a manager_id, which is the employee_id of another employee:

SQL
SELECT employee_id, first_name, title, manager_id FROM employees;
Diagram
Grace (CTO)              Maya (Head of Sales)      Ravi (Support Lead)
└─ Alan (Lead Engineer)  ├─ Omar                   └─ Hana
   ├─ Ada                └─ Zoe
   └─ Linus

Joining a table to itself

To show each employee beside their manager's name, use the table twice under two aliases:

SQL
SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.title,
  m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
ORDER BY manager, employee;

Read it as: for each employee e, find the row m whose id is e's manager id. Only six rows come back. Grace, Maya and Ravi have no manager, so the inner join drops them.

Keeping everyone

A LEFT JOIN keeps the people at the top:

SQL
SELECT
  e.first_name AS employee,
  COALESCE(m.first_name, '(no manager)') AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;

Counting direct reports

Turn it around: for each manager m, count the employees e who report to them:

SQL
SELECT m.first_name, m.title, COUNT(e.employee_id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_id
ORDER BY direct_reports DESC, m.first_name;

How far below their manager's salary is each employee?

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
JOIN employees m ON m.employee_id = e.manager_id
ORDER BY gap DESC;

Finding pairs

Self joins also find pairs of rows. Who works in the same department as whom?

SQL
SELECT a.first_name AS person_1, b.first_name AS person_2, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.employee_id < b.employee_id
ORDER BY a.department, a.employee_id;

a.employee_id < b.employee_id does two jobs: it stops people pairing with themselves, and it stops every pair appearing twice (Ada with Linus, then Linus with Ada).

Finished this lesson?Mark it complete to earn +20 XP and move your progress on.