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.
Schema
| id | INT |
| name | TEXT |
| manager_id | INT |
| id | INT |
| name | TEXT |
| plan_id | INT |
| id | INT |
| plan_name | TEXT |
| monthly_fee | NUMERIC |
| id | INT |
| subscriber_id | INT |
| amount | NUMERIC |
Example data
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.
Before you run it — Aisyah has no manager (manager_id NULL). Does she appear as an employee row in this INNER join?
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 see what changes.
Before you run it — same query but LEFT JOIN. Does Aisyah appear this time, and what's in her manager_name?
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. Gina is on the Basic plan but has never paid; the LEFT JOIN to payments keeps her instead of dropping her.
Before you run it — Emma paid 15.00 twice, Gina never paid. What total_paid do you expect for each of the 3 subscribers?
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? Sime Darby'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.