SELF JOIN

A self join is an ordinary join whose left and right inputs happen to be the same table read twice — the engine opens two independent cursors over the rows and pairs them by a condition, so each row can be matched against any other row of that table (including itself). The only new thing is that you must give each instance its own alias, because without aliases employee_id would be ambiguous: the planner can no longer tell which copy you mean.

The canonical use is a hierarchy stored as an adjacency list: one table where each row carries a foreign key pointing back to another row in the same table. Employees and their managers are the textbook case — manager_id on a row points at the employee_id of that person's boss.

The data we will trace

Four employees. Carol is the CEO, so her manager_id is NULL — there is no one above her. This NULL is the whole point of the lesson; keep your eye on row 1.

The naive query — and why it is wrong

The obvious query joins the table to itself on "my manager_id equals their employee_id":

-- WRONG for this requirement: silently drops Carol
SELECT e1.employee_name AS employee,
       e2.employee_name AS manager
FROM   employees e1
JOIN   employees e2
       ON e1.manager_id = e2.employee_id;

Trace it row by row. For each e1 we scan e2 looking for a match on the ON condition:

Result: 3 rows. Carol vanished. Plain JOIN is an inner join, so a row of e1 survives only if it finds at least one partner in e2. Carol's manager_id is NULL, and the comparison NULL = e2.employee_id evaluates to UNKNOWN (never TRUE) for every candidate row — so no partner is ever found and Carol is filtered out. This is the real-world correctness trap: "list every employee and their manager" silently becomes "list every employee who has a manager," and the top of the org chart disappears. Nobody notices until the CEO files a bug that they're missing from the org report.

The fix: LEFT JOIN keeps the row with no partner

Switch the inner join for a LEFT JOIN. A left join keeps every e1 row regardless of whether a match is found; when no e2 matches, the e2 columns come back as NULL.

SELECT e1.employee_name AS employee,
       e2.employee_name AS manager
FROM   employees e1
LEFT JOIN employees e2
       ON e1.manager_id = e2.employee_id
ORDER BY e1.employee_id;

4 rows now — the full org. If you want a friendlier label than NULL, wrap the manager column: COALESCE(e2.employee_name, '— (top level)').

When one self join is not enough: deeper hierarchies

A self join climbs exactly one level. To show employee → manager → manager's manager you'd add another aliased copy and chain the conditions:

-- two levels up, fixed depth
SELECT e.employee_name        AS employee,
       m.employee_name        AS manager,
       gm.employee_name       AS skip_level
FROM   employees e
LEFT JOIN employees m  ON e.manager_id = m.employee_id
LEFT JOIN employees gm ON m.manager_id = gm.employee_id;

This works only because you know the depth at write time. When the chain is arbitrary or unbounded ("give me Aki and everyone above her, however many levels"), self joins fall apart — you cannot write N joins for an unknown N. That is what a recursive CTE is for: it walks the adjacency list level by level until no more parents are found.

WITH RECURSIVE chain AS (
    SELECT employee_id, employee_name, manager_id, 1 AS lvl
    FROM   employees
    WHERE  employee_name = 'Aki'         -- anchor: start here
  UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, c.lvl + 1
    FROM   employees e
    JOIN   chain c ON e.employee_id = c.manager_id  -- step up one parent
)
SELECT lvl, employee_name FROM chain ORDER BY lvl;

Output: (1, Aki) → (2, Dev) → (3, Carol), then the recursion stops because Carol's manager_id is NULL and the join finds no parent. Rule of thumb: fixed, shallow depth → self join; arbitrary depth → recursive CTE. (Truly deep, hot trees argue for a different storage model entirely — closure tables or materialized paths — but that is a data-modeling decision, not a query one.)

Pitfalls

• The disappearing CEO (inner-join NULL trap). The headline bug above. A plain JOIN on a self-referencing FK drops every row whose pointer is NULL — usually the root. If the requirement says "all employees," you need LEFT JOIN. Quick test: does SELECT COUNT(*) of your join equal COUNT(*) of the base table? If it's smaller, you lost rows.
• Forgetting an alias. FROM employees JOIN employees ON ... errors with "table name specified more than once" / "ambiguous column." Each instance needs its own alias, and every column reference must be qualified (e1./e2.).
• Pair-comparison double counting. The other big self-join use is comparing rows to each other ("find employees in the same department"). Joining on a.dept = b.dept alone gives each pair twice (A–B and B–A) and matches every row with itself. Fix with a tie-breaker in the ON or WHERE: AND a.employee_id < b.employee_id. That single inequality both removes self-matches and keeps each unordered pair once.
• It is still a join — cost is real. A self join on a large unindexed table is an O(n²)-ish nested loop. Index the join key (here employee_id is usually the PK, but make sure manager_id is indexed too for the manager-side lookup), and check the plan rather than assuming.

Takeaways

• A self join is just a join where both inputs are the same table; aliases are mandatory because they disambiguate which copy each column comes from.
• Use it for fixed-depth adjacency-list work: one self join climbs exactly one level of a hierarchy.
• Default to LEFT JOIN for "employee and their manager" so the root row (NULL parent) is preserved; plain JOIN silently drops it.
• When the depth is unbounded, reach for a recursive CTE instead of stacking N self joins; for pair comparisons, add an inequality (<) to kill self-matches and duplicate pairs.

Sources: PostgreSQL documentation, "Joins Between Tables" and "WITH Queries (Common Table Expressions) — Recursive"; MySQL Reference Manual 8.0, "JOIN" and recursive-CTE sections; SQL standard three-valued logic (NULL comparisons yield UNKNOWN); Joe Celko, SQL for Smarties (adjacency-list vs. nested-set/closure-table hierarchy models). Re-authored and deepened for this guide: the original page used an inner JOIN that silently dropped the top-level employee (NULL manager_id); this version fixes that with LEFT JOIN, adds a row-by-row trace, the NULL-comparison explanation, and a recursive-CTE contrast for arbitrary depth.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — SELF JOIN

Why this concept exists (judgment chain)

A self join is the same table opened twice with aliases — adjacency-list hierarchies (employee→manager). INNER silently drops NULL parent roots; LEFT preserves them. Unbounded depth needs recursive CTEs, not N stacked joins.

Worked example with numbers or traced steps

employees: Carol mgr NULL; Dev→Carol; Mei→Carol; Aki→Dev
INNER self-join ON e1.manager_id = e2.employee_id → 3 rows (Carol missing).
LEFT JOIN → 4 rows, Carol manager NULL.
Recursive CTE from Aki: (1,Aki)→(2,Dev)→(3,Carol).
Pair compare: a.dept=b.dept AND a.id < b.id avoids self+double pairs.

When NOT to use / named alternative

Do not stack five self joins for unknown depth — use WITH RECURSIVE. Do not use INNER for “all employees with manager name.” Prefer closure table / materialized path only when recursive CTE cost dominates hot deep trees. Skip self join for simple PK lookups to another table.

Failure / ops fingerprint

Fingerprint: CEO missing from org chart; ambiguous column errors without aliases; O(n²) self join on unindexed manager_id. Ops: index manager_id; COUNT(*) join == base table; cycle guards in recursive CTEs (path array / max depth).

Hostile-panel drills (defend the decision)

Q1. Why must you alias both sides?
Model answer: Same table twice — unqualified columns are ambiguous; engines reject or misbind.

Q2. INNER vs LEFT for org chart?
Model answer: INNER drops NULL manager_id roots; LEFT keeps them with NULL manager columns.

Q3. Self join vs recursive CTE?
Model answer: Fixed shallow depth → self join; arbitrary depth → recursive CTE (or alternate hierarchy model).