Postgres by Example: Self-Join

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:

• 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

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. 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.

To run:

$ 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:

• 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: 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.

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.

Source: self-join.sql

Next: UNION, INTERSECT, EXCEPT

Home: Postgres by Example