🪞SELF JOIN

Same table, two roles, alias everything

A self-join is not a special operator: it is an ordinary JOIN where both operands are the same base table (or view), distinguished by different aliases-staff AS employee joined to staff AS manager. Any join shape applies: INNER, LEFT, or non-equi predicates for intervals or “strictly earlier” pairwise comparisons.

Cardinality traps are the same as multi-table joins: if managers are not unique per id, or if an employee appears twice in the table, row multiplication returns. Always validate uniqueness assumptions on the keys you traverse.

SQL

Employee → manager (one hop)

SELECT e.name AS employee,
       m.name AS manager
FROM staff e
JOIN staff m ON m.staff_id = e.manager_id;

✓Illustrative result set

employee	manager
Ada	Lin
Omar	Ada

🌳Depth beyond one hop

Stacking self-joins for “manager’s manager” does not scale to arbitrary org depth.

Two hops = second join to staff again; n hops means n joins, ugly and brittle.
For arbitrary trees, use a WITH RECURSIVE traversal (PostgreSQL), hierarchical CTE patterns (SQL Server), or move the graph to a proper adjacency/closure table and query with recursive SQL once.

SQL

Pairwise combinations without duplicates (same project)

SELECT a.sample_id AS a_id,
       b.sample_id AS b_id
FROM specimens a
JOIN specimens b
  ON a.project_id = b.project_id
 AND a.sample_id < b.sample_id
LIMIT 3;

✓Illustrative result set

a_id	b_id
S1	S2
S1	S3
S2	S3

Missing inequality guard on self-join

JOIN specimens b ON a.project_id = b.project_id without a.key < b.key (or similar) pairs every row with itself and every other row twice-n² growth.

Pair each unordered pair once

Add a strict ordering predicate on a stable primary key, or join through a bridge table that already encodes unordered pairs.

⏱️Interval overlap (non-equi self-join sketch)

Find instrument runs whose time ranges overlap, still two aliases of the same runs table.

SELECT r1.run_id AS run_a, r2.run_id AS run_b
FROM instrument_runs r1
JOIN instrument_runs r2
ON r1.instrument_id = r2.instrument_id
AND r1.run_id < r2.run_id
AND r1.started_at < r2.ended_at
AND r2.started_at < r1.ended_at

Half-open intervals [start, end) simplify boundary touches; adjust predicates to your convention.

⚡ Advanced / dialect note

Index the columns you self-join on, typically foreign keys like manager_id or project_id: the same way you would index a separate table, or nested-loop probes become sequential scans on large staff directories.

Column names

Always list SELECT columns with aliases (e.name, not bare name) so consumers and ORMs never see ambiguous names in the result set.

TL;DR

A self-join joins a table to itself using different aliases (e.g. employees to managers on manager_id). It is just a regular join with two copies of the same relation, cardinality rules still apply. Always qualify columns with aliases to avoid ambiguous references.