MySQL Self Join

Last updated: October 19, 2026Oct 19, 2026

A self join joins a table to itself. It isn't a new keyword: you write an ordinary INNER or LEFT JOIN, but list the same table twice with two different aliases. It is how you answer questions about relationships inside one table: who is each employee's manager, which customers live in the same city, which products cost the same.

1. The Idea

Table: employees (6 rows)

SELECT a.col, b.col
FROM table_name a
JOIN table_name b ON a.some_col = b.other_col;

2. Employee and Manager

SELECT e.name AS employee, e.title, m.name AS manager
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
ORDER BY m.name, e.name;

Result: 5 rows

Ishita has no manager, so the INNER JOIN drops her. A LEFT JOIN keeps her:

SELECT e.name AS employee, COALESCE(m.name, '— (top)') AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

Result: 6 rows

3. Comparing Rows in the Same Table

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary * 0.55
ORDER BY e.salary DESC;

Result: 3 rows

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

4. Finding Pairs

Colleagues who work in the same city. The condition a.emp_id < b.emp_id avoids pairing someone with themselves and listing each pair twice:

SELECT a.name AS colleague_1, b.name AS colleague_2, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.emp_id < b.emp_id
ORDER BY a.city, a.name, b.name;

Result: 6 rows

Without that condition each pair appears twice (A–B and B–A), plus everyone paired with themselves.

5. Counting Direct Reports

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

Result: 6 rows

6. Deeper Levels: Recursive CTE

Each self join climbs one level. For a hierarchy of any depth, use a recursive CTE (MySQL 8+):

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 0 AS level, CAST(name AS CHAR(100)) AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, c.level + 1, CONCAT(c.path, ' > ', e.name)
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT level, name, path FROM chain ORDER BY path;

Result: 6 rows

7. Tips

• Give aliases meaningful names (e for employee, m for manager).
• Index the self-referencing column (manager_id); a foreign key does that automatically.
• Use LEFT JOIN when the top or unmatched rows must stay.
• Use a.id < b.id to get each pair once.

8. Summary

• A self join joins a table to itself using two aliases.
• Typical uses: employee–manager, comparing rows, finding pairs or duplicates.
• INNER self joins drop unmatched rows; LEFT self joins keep them.
• Recursive CTEs handle hierarchies of any depth.