SELF JOIN — Join a Table with Itself

A SELF JOIN is a regular join where a table is joined with itself.
It is used when rows in the same table are related to each other, such as hierarchical or paired data.

To distinguish between the two instances of the same table, you must use table aliases.

Syntax

SELECT A.column, B.column
FROM table_name AS A
JOIN table_name AS B
ON A.common_column = B.related_column;

Example Scenario

Let’s say we have a table of employees where each employee might have a manager, and managers are also listed in the same table.

employees

+--------+------------+------------+
| emp_id | name       | manager_id |
+--------+------------+------------+
| 1      | Alice      | NULL       |
| 2      | Bob        | 1          |
| 3      | Charlie    | 1          |
| 4      | David      | 2          |
| 5      | Emma       | 2          |
+--------+------------+------------+

Here:

  • Alice is the top-level manager (no one manages her).
  • Bob and Charlie report to Alice.
  • David and Emma report to Bob.

Example Query

List all employees with their manager's name.

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

Output

+----------+----------+
| employee | manager  |
+----------+----------+
| Alice    | NULL     |
| Bob      | Alice    |
| Charlie  | Alice    |
| David    | Bob      |
| Emma     | Bob      |
+----------+----------+
  • The table employees is used twice: once as e (employee) and once as m (manager).
  • For Alice, manager is NULL since she has no manager.

When to Use SELF JOIN?

  • When a table has hierarchical or recursive relationships

  • Common use cases include:

    • Employees and managers
    • Friends/followers in social networks
    • Products with parent categories