Chapter 19 · intermediate · 8 min
Self-joins & joining 3+ tables
Two extensions of the JOIN you already know. A self-join joins a table to itself, useful when rows reference other rows in the same table, like an employee referencing their manager (who is also an employee). And real schemas rarely stop at two tables; you'll often chain three or more JOINs to answer one question.
We'll build an org chart, then a three-table subscription report.
The dataset
An `employees` table where `manager_id` points at another row's `id` in the **same** table, plus a `subscribers` / `plans` / `payments` trio for the three-table example.
A self-join is just a join, twice-aliased
There's nothing special in the syntax: you join employees to employees, but you need two different aliases so SQL (and you) can tell the two "copies" apart. Here e is the employee row, m is their manager's row:
| employee | manager_name |
|---|---|
| Bala | Aisyah |
| Chong | Aisyah |
| Devi | Bala |
Employee -> manager
LEFT self-join: keep the top of the chart
Aisyah has no manager (manager_id IS NULL), so a plain JOIN drops her entirely, the same INNER JOIN trap from the JOINs chapter. Switch to LEFT JOIN and she reappears, with a NULL manager name:
| employee | manager_name |
|---|---|
| Bala | Aisyah |
| Chong | Aisyah |
| Devi | Bala |
| Aisyah | NULL |
Aisyah returns, with NULL manager
Chaining a third table
Real questions often span three tables: subscribers, the plans they're on, and the payments they've made. Add a second JOIN clause; each one connects a new table using its own ON condition. Read it top to bottom: subscribers join to plans, then (separately) to payments:
| name | plan_name | total_paid |
|---|---|---|
| Faizal | Premium | 35.00 |
| Emma | Basic | 30.00 |
| Gina | Basic | 0 |
Gina is on the Basic plan but has never paid; the LEFT JOIN to payments keeps her with a total_paid of 0 instead of dropping her.
subscribers -> plans -> payments
If you've used Excel or Google Sheets
A self-join is the same idea as a VLOOKUP where a table looks itself up, e.g. looking up each employee's manager by matching manager_id back against the same sheet's ID column. Chaining three tables is just two lookups performed one after another; spreadsheets handle this the same way, nesting or chaining lookup formulas, they just don't have a single keyword for "join everything at once" the way SQL's stacked JOIN clauses do.
Ready to practice? Pos Malaysia's employee-manager pairs question below is this exact self-join pattern.
Two habits scale to any number of tables: name every table with a short alias, and add JOINs one relationship at a time rather than trying to write the whole thing at once. If a subscriber can have zero rows in a joined table, that JOIN almost always needs to be a LEFT JOIN.