Self Joins

A self join is when you join a table to itself. This sounds unusual, but it is extremely useful for representing hierarchical or comparative relationships within a single table.

When Do You Need a Self Join?

Self joins are commonly used for:

• Hierarchical data: employees and their managers (stored in the same table)
• Comparisons: finding pairs of rows that meet some criteria (e.g., employees in the same department)
• Sequential data: comparing a row to the previous or next row

The employees Table and manager_id

Our employees table has a manager_id column. This column contains the id of another employee who is that person's manager. This is a classic self-referencing foreign key:

employees table
+----+----------+-------------+--------+------------+
| id | name     | department  | salary | manager_id |
+----+----------+-------------+--------+------------+
| 1  | Alice    | Engineering | 95000  | NULL       |  <-- top-level (no manager)
| 2  | Bob      | Marketing   | 65000  | 1          |  <-- managed by Alice
| 3  | Charlie  | Engineering | 80000  | 1          |  <-- managed by Alice
| 4  | Diana    | Sales       | 72000  | 1          |  <-- managed by Alice
| 5  | Eve      | Engineering | 90000  | 3          |  <-- managed by Charlie
| 6  | Frank    | Marketing   | 55000  | 2          |  <-- managed by Bob
| 7  | Grace    | Engineering | 88000  | 3          |  <-- managed by Charlie
| 8  | Hank     | Sales       | 60000  | 4          |  <-- managed by Diana
+----+----------+-------------+--------+------------+

Basic Self Join: Finding Each Employee's Manager

To find who manages each employee, join the employees table to itself:

SELECT
  e.name AS employee_name,
  e.department,
  m.name AS manager_name
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id;

Here:

• e represents the employee (the person we are looking up)
• m represents the manager (the person the employee reports to)
• LEFT JOIN is used so that top-level managers (with manager_id = NULL) still appear in the results

Walking Through the Logic

For employee Alice (manager_id = NULL):
  Look for a row where id = NULL --> no match
  Result: Alice | NULL (she has no manager)

For employee Bob (manager_id = 1):
  Look for a row where id = 1 --> Alice
  Result: Bob | Alice

For employee Eve (manager_id = 3):
  Look for a row where id = 3 --> Charlie
  Result: Eve | Charlie

Finding Direct Reports

Flip the perspective -- find which employees report to each manager:

SELECT
  m.name AS manager_name,
  m.department AS manager_department,
  e.name AS direct_report
FROM employees m
INNER JOIN employees e
  ON m.id = e.manager_id
ORDER BY m.name, e.name;

This time we use INNER JOIN because we only want managers who actually have direct reports.

Counting Direct Reports

SELECT
  m.name AS manager_name,
  COUNT(e.id) AS number_of_reports
FROM employees m
LEFT JOIN employees e
  ON m.id = e.manager_id
GROUP BY m.name
ORDER BY number_of_reports DESC;

This shows every employee alongside how many people report to them. Using LEFT JOIN ensures employees with zero reports are included.

Comparing Employees in the Same Department

Self joins are also useful for comparing rows within the same table. Find pairs of employees in the same department:

SELECT
  e1.name AS employee_1,
  e2.name AS employee_2,
  e1.department
FROM employees e1
INNER JOIN employees e2
  ON e1.department = e2.department
  AND e1.id < e2.id
ORDER BY e1.department, e1.name;

The condition e1.id < e2.id prevents:

• Pairing an employee with themselves (e1.id = e2.id)
• Duplicate pairs in reverse order (Alice-Bob and Bob-Alice)

Finding Employees Who Earn More Than Their Manager

SELECT
  e.name AS employee_name,
  e.salary AS employee_salary,
  m.name AS manager_name,
  m.salary AS manager_salary,
  e.salary - m.salary AS salary_difference
FROM employees e
INNER JOIN employees m
  ON e.manager_id = m.id
WHERE e.salary > m.salary;

This finds employees who earn more than their direct manager -- a common HR analysis query.

Multi-Level Hierarchy

You can chain self joins to look up multiple levels of the hierarchy:

SELECT
  e.name AS employee,
  m1.name AS manager,
  m2.name AS managers_manager
FROM employees e
LEFT JOIN employees m1
  ON e.manager_id = m1.id
LEFT JOIN employees m2
  ON m1.manager_id = m2.id;

This shows three levels: the employee, their direct manager, and their manager's manager. Each level requires an additional self join.

Try It Yourself

1. Write a self join to display each employee alongside their manager's name and department. Include employees who have no manager.

2. Find all employees who earn more than their manager. Display both names and both salaries.

3. Using a self join, find all pairs of employees in the "Engineering" department. Make sure each pair appears only once (not both Alice-Charlie and Charlie-Alice).

4. Count how many direct reports each manager has. Include employees who manage nobody (show 0).