What is a self-join?

Question

Accepted Answer

A **self-join** is a join where a table is joined **to itself** — useful when rows in a table relate to *other rows in the same table*, such as hierarchies (employees and their managers) or comparing rows within a table.

## The classic example: employees and managers

```sql
-- join employees to itself to pair each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;   -- same table, two aliases!
-- → Bob | Ann, Carol | Ann, Ann | NULL
```

The key is **table aliases** (`e` and `m`) — they let you treat the single table as two "copies" so you can join the employee row to its manager row within the same table. (Using LEFT JOIN keeps employees with no manager.)

## Other uses of self-joins

```sql
-- find pairs of products with the same price
SELECT a.name, b.name, a.price
FROM products a
JOIN products b ON a.price = b.price AND a.id < b.id;   -- a.id < b.id avoids duplicates/self-pairs

-- find rows related to other rows in the same table (sequences, comparisons)
```

Self-joins are also used to compare rows within a table (finding duplicates, pairs, or related entries) — the `a.id < b.id` condition is a common trick to avoid pairing a row with itself and avoid duplicate pairs.

## How it works conceptually

```text
A self-join is just a regular join where both sides happen to be the SAME table.
The aliases create two logical "instances" of the table, so each row of one instance
can be matched against rows of the other instance.
→ Nothing special syntactically — it's a normal join, with the trick being aliases.
```

## Why it matters

Self-joins are a useful SQL technique for handling **relationships within a single table** — particularly hierarchical or self-referential data, which is a common real-world pattern, so understanding them is valuable for these scenarios.

The classic and most important use is **hierarchies** where rows reference other rows in the same table (employees referencing their managers, categories with parent categories, organizational structures, threaded comments) — self-joins let you query these relationships (pairing each employee with their manager) which simpler queries can't express.

Understanding that the key mechanism is **table aliases** (creating two logical "instances" of the table so rows can be matched against each other within the same table) demystifies the technique — it's just a normal join where both sides are the same table.

Self-joins also handle comparing rows within a table (finding pairs, duplicates, or related entries, often with a condition like `a.id < b.id` to avoid self-pairing and duplicates).

Since self-referential and hierarchical data is common (org charts, category trees, manager relationships, comparing records within a table), and since self-joins are the standard way to query such relationships within a single table, understanding self-joins — that a table can be joined to itself using aliases, and the common patterns (hierarchies, row comparisons) — is useful, practically-relevant SQL knowledge for handling a class of queries involving intra-table relationships that arise frequently in real data models, and a common interview topic demonstrating deeper understanding of joins.