A self-join is when you join a table to itself.
The first time you see it, you might think "wait, JOIN the same table…?",
but ⭐️ it's the go-to technique when parent-child or boss-subordinate relationships live in a single table.
Example: an employees table (bosses are employees too)
Bosses are also employees, so they're in the same employees table.
| id | name | manager_id (boss's id) |
|---|---|---|
| 1 | Yamada (Department Head) | (none) |
| 2 | Sato | 1 |
| 3 | Suzuki | 1 |
When you want to "list each employee alongside their boss's name",
you join employees again under a different alias.
SELECT
emp.name AS employee,
boss.name AS boss
FROM employees AS emp
LEFT JOIN employees AS boss -- ← join the same table under another alias
ON emp.manager_id = boss.id;
Result:
| employee | boss |
|---|---|
| Yamada (Department Head) | (none) |
| Sato | Yamada (Department Head) |
| Suzuki | Yamada (Department Head) |
The key is the alias (AS)
Since it's the same table, the trick is to give it aliases like AS emp / AS boss and treat them as separate tables.
-
emp.name… the employee's own name -
boss.name… the boss's name
Without aliases, there's no way to tell "which name?"
Why LEFT JOIN?
We want to keep people with no boss (like the department head) in the result, so we use LEFT JOIN.
With INNER JOIN, the rows without a boss would disappear.
Summary
When parent-child or boss-subordinate relationships are in the same table, joining the table to itself under an alias lets you put "the row + its related row" on one line. That's a self-join.
It shines in designs that keep "relationships between things of the same kind" in one table:
employee × boss, category × parent category, company × its parent company, and so on.
Top comments (0)