SELF JOIN

Definition

A self join joins a table to ITSELF, treating it as if it were two separate tables — necessary whenever a row needs to be related to another row in the SAME table, like an employee and their manager (who is also an employee).

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT e.id, e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Here, employees is joined to itself via two ALIASES, e (the employee) and m (their manager) — Rename (7.3, 12's aliasing) is genuinely REQUIRED here, not just stylistic, since SQL needs some way to distinguish "the employee row" from "the manager row" even though both come from the same table. A LEFT JOIN (not INNER) is used here specifically so Asha — who has no manager (manager_id IS NULL) — still appears, with manager = NULL, rather than being dropped.

Edge Cases and Pitfalls

• Without aliasing, SELECT name FROM employees JOIN employees ON manager_id = id is ambiguous nonsense — every column reference would be unclear about which "copy" of the table it means.
• Self joins commonly appear for hierarchical/recursive relationships (this exact "who reports to whom" example is the "recursive relationship" concept from ER modeling, 6.15) — though a self join alone can only show ONE level of the hierarchy at a time (employee-to-direct-manager); walking an ARBITRARY number of levels up a management chain needs a recursive CTE (Chapter 32).
• A self join can also find pairs within a table for comparison purposes — e.g. "employees in the same department" (e1.department_id = e2.department_id AND e1.id <> e2.id, using <> to avoid pairing a row with itself).

Key Takeaways

• A self join relates a table's rows to other rows in the SAME table, using two aliases to distinguish the two "copies."
• Aliasing is functionally required here, not just stylistic — SQL cannot otherwise distinguish which occurrence a column reference means.
• Self joins handle one hierarchy level at a time; arbitrary-depth traversal needs a recursive CTE (Chapter 32).