Self-Joins

## A join operates on row sets, not on files The mental model that makes self-joins obvious is this: a join takes two *row sources* and produces the pairs of rows that satisfy its `ON` predicate. Nothing in that definition says the two sources must be different tables. If both sources are the rows of `employees`, the join still works exactly the same way — it pairs an `employees` row with another `employees` row. That is all a self-join is. There is no `SELF JOIN` keyword, no special syntax, and no copy of the table made anywhere. You write `JOIN` and name the same table twice. ## Why the alias is mandatory Every table reference in a `FROM` clause is exposed under a name, and those names must be distinct within the clause. Write this and it fails: ```sql -- rejected: two references both called employees SELECT name, manager_id FROM employees JOIN employees ON employees.manager_id = employees.employee_id; ``` `employees.manager_id` cannot be resolved: two things in scope answer to `employees`. Engines reject this outright rather than guessing. Supplying a *correlation name* (the standard's term for a table alias) fixes it: ```sql SELECT e.name AS employee, m.name AS manager FROM employees AS e JOIN employees AS m ON e.manager_id = m.employee_id; ``` Now `e` and `m` are two separate handles onto the same set of rows. `e.name` and `m.name` are different columns of the *result*, even though they are the same column of the *table*. A portability note: the `AS` keyword before a table alias is optional in the standard and rejected by some engines, so `FROM employees e` is the spelling that works everywhere. ## Reading a self-join out loud A good habit is to name the aliases for the *role* each row plays, never `t1`/`t2`. In the query above, `e` is "the employee" and `m` is "the manager". The predicate `e.manager_id = m.employee_id` then reads as prose: this employee's manager id equals that person's own id. Roles make three-alias queries survivable too: ```sql SELECT e.name AS employee, m.name AS manager, g.name AS grand_manager FROM employees AS e LEFT JOIN employees AS m ON e.manager_id = m.employee_id LEFT JOIN employees AS g ON m.manager_id = g.employee_id; ``` Each extra level of hierarchy is one more alias, which is why this shape only works when the depth is known and fixed. ## What the INNER version quietly drops The classic follow-up trap: the top of the hierarchy has no manager, so its `manager_id` is NULL. NULL never equals anything, so the `ON` predicate is never true for that row and an INNER self-join returns everyone *except* the CEO. If the org chart is supposed to be complete, the query is wrong and nothing complains. `LEFT JOIN` preserves the left side and NULL-extends the manager columns: ```sql SELECT e.name AS employee, m.name AS manager -- manager is NULL for the CEO FROM employees AS e LEFT JOIN employees AS m ON e.manager_id = m.employee_id; ``` A row count sanity check catches this instantly: the result of the LEFT form should have exactly as many rows as `employees` when `manager_id` points at a unique key. ## The direction of the join is a choice Swapping the roles asks a different question. `FROM employees AS m JOIN employees AS e ON e.manager_id = m.employee_id` with `SELECT m.name, COUNT(*)` and a `GROUP BY` lists managers with their headcount; the same physical pairing, read from the other end. Deciding which alias is the "driving" side is the whole design step of a self-join. ## Where self-joins stop A self-join reaches a *fixed* number of hops. One join is one level up; three joins are three levels up. If you need "every ancestor, however deep", the number of joins is data-dependent and the shape no longer works — that is what recursive queries exist for. Similarly, if the relationship is many-to-many (an employee with several mentors), the self-join is still correct but each employee produces one output row per match, which is normal join behaviour rather than a bug. ## Things interviewers listen for That you say "two row sources" rather than "two copies of the table"; that you alias by role; that you notice the NULL parent disappears from the inner form; and that you can state what a single row of the result means without hesitating.