# Self-Join

> Query relationships within one table and follow an employee hierarchy.

---

LLMS index: [llms.txt](/en/llms.txt)

---

A *self-join* is when a table joins to itself. You give it two aliases so PostgreSQL can tell which copy you mean, and use a condition that links rows together. Self-joins show up whenever the data models a relationship between rows of the same kind — employees and their managers, comments and their parent comments, predecessors and successors. For arbitrary-depth hierarchies, the right tool is a recursive CTE (covered in a later lesson); for one level, a self-join is simple and fast.

## What you'll learn {#learn}

- Joining a table to itself with two aliases
- Choosing `INNER` vs `LEFT` for self-joins
- Modeling a one-level hierarchy (employee → manager)
- A peek at recursive CTEs for arbitrary depth
- Naming columns for clarity

## Example {#example}

```sql {num="31.1" caption="Self-Join"}
DROP TABLE IF EXISTS employees_example;
CREATE TABLE employees_example (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees_example(id)
);

INSERT INTO employees_example VALUES
  (1, 'Alice',  NULL),    -- top of the hierarchy
  (2, 'Bob',    1),       -- reports to Alice
  (3, 'Carol',  1),       -- reports to Alice
  (4, 'Dave',   2),       -- reports to Bob
  (5, 'Eve',    2);       -- reports to Bob

-- One level: employee and their direct manager
SELECT e.name AS employee, m.name AS manager
FROM employees_example e
LEFT JOIN employees_example m ON e.manager_id = m.id
ORDER BY e.id;

-- Count direct reports per manager
SELECT m.name AS manager, count(e.id) AS direct_reports
FROM employees_example m
LEFT JOIN employees_example e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY m.id;

-- A taste of recursion: full chain to the top (covered fully in the CTE lesson)
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE id = 4                                       -- start at Dave
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees_example e
  JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;
```

In the join `FROM employees_example e LEFT JOIN employees_example m ON e.manager_id = m.id`, `e` is the *employee* row and `m` is the *manager* row. Both refer to the same physical table — PostgreSQL treats the aliases as independent copies. Using `LEFT JOIN` means employees without a manager (Alice) still appear, with NULL in the manager columns.

The `count(e.id)` (not `count(*)`) in the second query is the standard pattern: `count(*)` would count NULLs from unmatched rows, giving every leaf manager at least 1 direct report incorrectly. `count(e.id)` only counts non-NULL ids — actual reports.

The recursive CTE at the end is a preview of the [CTE lesson](/en/38-cte/). `WITH RECURSIVE` lets you walk an arbitrary depth — the anchor row (Dave) is the starting point; the recursive step joins back to `chain` to climb one level at a time.

## Run the example {#run}

To run:

```text
$ psql -f source/self-join.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 5
 employee | manager
----------+---------
 Alice    |
 Bob      | Alice
 Carol    | Alice
 Dave     | Bob
 Eve      | Bob
(5 rows)
 ...
```

## Common pitfalls {#pitfalls}

- Using `INNER JOIN` instead of `LEFT JOIN` and silently losing the top of the hierarchy. The top row has no manager — `INNER JOIN` discards it.
- Forgetting that both `e` and `m` are the same table — you can `WHERE e.id <> m.id` to exclude same-row matches if your condition might allow them.
- Counting `*` instead of the joined-table id when looking for "groups with no children" — `count(child.id)` is the correct idiom.
- For arbitrary depth, repeatedly self-joining (`emp → mgr → mgr's mgr → ...`) does not scale. Use `WITH RECURSIVE` instead.

> [!TIP] Tip
> Symmetric relationships (friend-of) usually use a separate junction table, not a self-join. Asymmetric (manager-of, parent-of) fit nicely as a self-referencing column.

> [!NOTE] Try it
> Add a sixth employee under Carol. Re-run the direct-reports query — Carol should now have one. Then modify the recursive CTE to start at Eve instead of Dave and see how the chain changes.

## Downloads {#downloads}

SQL file: [self-join.sql](/examples/source/self-join.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)