DEV Community

Kaziu
Kaziu

Posted on

SQL Self-Join Explained with the Simplest Example

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;
Enter fullscreen mode Exit fullscreen mode

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)