MySQL Self Join

A self join is when a table is joined to itself. This sounds unusual, but it is a powerful technique for working with hierarchical or self-referencing data — where rows in a table have a relationship with other rows in the same table.

The most classic example is an employees table where each employee row stores the id of their manager — who is also a row in the same table.

---

The Sample Table

id	name	role	manager_id
1	Ahmed Kabir	CEO	NULL
2	Sara Begum	Manager	1
3	Karim Ali	Manager	1
4	Rafi Hossain	Developer	2
5	Nila Akter	Designer	2
6	Tanvir Islam	Analyst	3

The hierarchy this data represents:

---

Why Aliases Are Required

Because you are referencing the same table twice in the same query, you must give it two different aliases — one for the "employee" side and one for the "manager" side. Without aliases, MySQL would not know which reference belongs to which role:

FROM employees AS e        -- the employee
JOIN employees AS m ON ... -- the manager (same table, different alias)

---

Syntax

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

---

INNER Self Join — Employees with Their Managers

This query shows each employee alongside their manager's name:

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

employee	role	manager
Karim Ali	Manager	Ahmed Kabir
Sara Begum	Manager	Ahmed Kabir
Nila Akter	Designer	Sara Begum
Rafi Hossain	Developer	Sara Begum
Tanvir Islam	Analyst	Karim Ali

Ahmed Kabir (the CEO) does not appear as an employee in this result because his manager_id is NULL — and INNER JOIN excludes unmatched rows.

---

LEFT Self Join — Include the Top-Level Row

Use a LEFT JOIN version of the self join to include the CEO (who has no manager):

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

employee	role	manager
Ahmed Kabir	CEO	NULL
Karim Ali	Manager	Ahmed Kabir
Sara Begum	Manager	Ahmed Kabir
Nila Akter	Designer	Sara Begum
Rafi Hossain	Developer	Sara Begum
Tanvir Islam	Analyst	Karim Ali

---

Other Uses for Self Join

Self joins are not limited to employee hierarchies. Other common uses include:

• Finding pairs of rows that share a value — for example, customers from the same city.
• Comparing a row against other rows in the same table — for example, products with the same category but different prices.
• Category trees where a parent_id references another row in the same categories table.

---

Key Points to Remember

• A self join joins a table to itself — useful for hierarchical or self-referencing data.
• Table aliases are required — the same table needs two different names to distinguish the two roles.
• Use INNER JOIN to exclude rows with no self-match (e.g., top-level records with NULL parent).
• Use LEFT JOIN to include all rows, even those at the top of the hierarchy.
• Any join type (INNER, LEFT, RIGHT) can be used as a self join — the "self" part just means both sides reference the same table.