Joining More Than Two Tables

When two tables are not enough

A single join follows one foreign key and answers most questions. Some answers refuse to live in only two tables. Ask "which projects can each engineer be staffed onto, and which department owns those projects?" and the names sit in employees, the department names in departments, and the project names in projects — three tables, one question.

The good news is you already know the move. A second join is exactly the same JOIN ... ON you wrote for two tables, applied one more time. You chain joins the way you chain links in a necklace: each one reaches one table further along the foreign keys until the answer is assembled in front of you.

flowchart LR
  E["employees"] -->|"department_id"| D["departments"]
  D -->|"department_id"| P["projects"]
  style E fill:#0e7490,color:#fff
  style D fill:#0e7490,color:#fff
  style P fill:#0e7490,color:#fff

Each JOIN follows one more foreign key, reaching one table further.

A join for each extra table

The matched rows produced by the first join become the left side of the second. Each JOIN brings in one new table and pairs its rows with whatever has been assembled so far, using an ON condition that almost always references the new table's foreign key.

SELECT e.name, d.name, p.name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.department_id = d.id;

Read it left to right: pair employees with their department, then pair each surviving row with every project in that same department. The second ON links projects back to departments — the table already in the query — through p.department_id, because that is the pointer projects actually carry.

Three tables, two joins. Run it, then remove the second JOIN to watch the result collapse back to two columns.

SELECT e.name AS employee, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.department_id = d.id;

Join order is a readability choice

For inner joins, the order you list the tables changes how the query reads, not the rows it returns — the result set is identical no matter which table you start from. So write the chain in the order a person would tell the story: begin with the thing you care about and follow the links outward from there.

What does matter is that every ON names the new table and ties it to a table already present. A common slip is writing a second ON whose columns come from two tables, neither of which is the one being joined, which SQLite rejects with a confusing error. Anchor each new join to something already on the table and the engine stays happy.

erDiagram
  DEPARTMENTS ||--o{ EMPLOYEES : "employs"
  DEPARTMENTS ||--o{ PROJECTS : "owns"
  DEPARTMENTS {
    int id
    text name
    text city
  }
  EMPLOYEES {
    int id
    text name
    int department_id
  }
  PROJECTS {
    int id
    text name
    int department_id
  }

departments is the hub both employees and projects point at — every three-table path runs through it.

Add WHERE and ORDER BY after the joins — they apply to the already-combined rows.

SELECT e.name AS employee, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.department_id = d.id
WHERE p.status = 'active'
ORDER BY d.name, e.name;

Joins first, then filters and sorts

A join lives in the FROM clause, so it runs before WHERE, ORDER BY, and SELECT get involved. The engine stitches the tables into one combined set of rows, then WHERE trims that set, then ORDER BY arranges the survivors. You never choose between joining and filtering — a real query usually does both, in that fixed order.

This is why a condition on the third table reads so naturally after the joins: by the time WHERE runs, projects is already part of every row, so filtering on p.status is no different from filtering on a column that was there from the start.

Three tables, three name columns

Every table here has a name column. Write SELECT name across a three-table join and the database cannot decide which one you mean, so it refuses with an "ambiguous column name" error. The fix never changes: qualify with the alias. e.name, d.name, and p.name are unambiguous, and they also tell a reader exactly which name they are looking at.

With three tables in play, qualifying stops being a nicety. A query that runs is one whose every shared column carries its alias, so build the habit now before the fourth table arrives.

Filter on any of the three tables — here, keep only rows whose department sits in Oslo.

SELECT e.name AS employee, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.department_id = d.id
WHERE d.city = 'Oslo'
ORDER BY p.name, e.name;

Exercise

List every employee's name, their department's name, and the name of every project in that department. Chain two joins through departments.

SELECT e.name, d.name, p.name
FROM employees e
-- join to departments, then to projects, here
;

SELECT e.name, d.name, p.name FROM employees e JOIN departments d ON e.department_id = d.id JOIN projects p ON p.department_id = d.id;

Exercise

Same three-table shape, but keep only rows where the project is 'active'. Return each employee's name, department name, and project name.

SELECT e.name, d.name, p.name
FROM employees e
JOIN departments d ON e.department_id = d.id
-- add the projects join and the filter here
;

SELECT e.name, d.name, p.name FROM employees e JOIN departments d ON e.department_id = d.id JOIN projects p ON p.department_id = d.id WHERE p.status = 'active';

Exercise

This three-table query joins projects on the wrong key — p.id = d.id matches a project's own id against a department's id, producing meaningless pairs. Fix the second ON so projects link to departments the way foreign keys intend.

SELECT e.name, p.name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON p.id = d.id
;

SELECT e.name, p.name FROM employees e JOIN departments d ON e.department_id = d.id JOIN projects p ON p.department_id = d.id;

Exercise

List the name of each Engineering employee alongside the name of each completed project in the Engineering department. You must join all three tables and filter on both the department name and the project status.

SELECT e.name, p.name
FROM employees e
-- join departments and projects, then filter, here
;

SELECT e.name, p.name FROM employees e JOIN departments d ON e.department_id = d.id JOIN projects p ON p.department_id = d.id WHERE d.name = 'Engineering' AND p.status = 'completed';

Exercise

For every project owned by a department in Oslo, return the project name, the department name, and the name of each employee in that department. Sort by project name, then employee name.

SELECT p.name, d.name, e.name
FROM projects p
-- join departments and employees, filter to Oslo, then sort here
;

SELECT p.name, d.name, e.name FROM projects p JOIN departments d ON p.department_id = d.id JOIN employees e ON e.department_id = d.id WHERE d.city = 'Oslo' ORDER BY p.name, e.name;

Recap

Chain one JOIN ... ON per extra table; each new ON ties the new table to one already present.
The matched rows from the first join feed the second, read left to right.
Inner-join order is cosmetic — write the chain to read like a story.
Two tables hanging off the same hub fan out, multiplying rows for every combination.
Qualify every shared column (e.name, d.name, p.name) — three tables guarantee ambiguity otherwise.

Next you will turn a table on itself, joining employees to employees to compare rows within the same table.

Joining More Than Two Tables

When two tables are not enough

A join for each extra table

Join order is a readability choice

Joins first, then filters and sorts

Three tables, three name columns

Recap

Checkpoint quiz

Go deeper — technical resources