0Pricing
SQL Interview Prep · Lesson

Joining Three or More Tables

Chaining joins and reasoning about intermediate result sets.

Joining Three or More Tables is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Joins Chain, Not Branch

Real interview problems rarely stop at two tables. You will be asked to join three, four, or more. The key insight that calms the chaos: a multi-table join is just a sequence of two-table joins.

The database joins the first two into an intermediate result, then joins that result to the third, and so on. If you can reason about one join, you can reason about a chain of them.

The Three-Table Schema

We extend our example with a third table. Now we have customers, their orders, and the products each order references.

  • customers(id, name)
  • orders(id, customer_id, product_id, amount)
  • products(id, title)

Two relationships connect them: orders.customer_id = customers.id and orders.product_id = products.id.

customers(id, name)
orders(id, customer_id, product_id, amount)
products(id, title)

The Basic Three-Table Query

Stack the joins one after another. Each JOIN ... ON adds a table and states how it connects to what came before.

Read it top to bottom: start from orders, attach the matching customer, then attach the matching product. Every order row gains a name and a product title.

SELECT c.name, p.title, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products  p ON p.id = o.product_id;

Think in Intermediate Result Sets

The mental model that makes multi-table joins easy: after each JOIN, picture the intermediate result so far.

  • After join 1: orders + customer columns.
  • After join 2: that result + product columns.

The next ON clause can reference any column already present in the intermediate set, which is why join order affects what you can write in each ON.

Each ON Sees Earlier Tables

An important consequence: a later join's ON clause may reference any table joined before it, not only the table being added.

Here the third join links to o (orders), which was already in scope. You could also link a fourth table to c or p because both are already part of the running result.

SELECT c.name, p.title, w.region
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products  p ON p.id = o.product_id
JOIN warehouses w ON w.id = o.warehouse_id; -- refers to o

Start From the Central Table

A practical tip for live interviews: begin the FROM with the fact / central table (usually the one in the middle of the relationships), then radiate outward to the lookup tables.

Orders sits in the middle, holding foreign keys to both customers and products, so starting there keeps every ON clause simple and forward-referencing.

-- orders is central: it links to both sides
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products  p ON p.id = o.product_id

Fan-Out Compounds Across Chains

Everything you learned about fan-out still applies, and it stacks. If each order has many items and each item has many tax rows, the row count multiplies at every step.

When an aggregate looks too large in a multi-join query, suspect a fan-out introduced by one of the middle joins, and check each join's grain individually.

-- rows = orders * items_per_order * tax_rows_per_item
SELECT o.id
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN item_taxes  t ON t.item_id  = i.id;

Mixing Join Types in a Chain

You can combine INNER and LEFT joins in one query, but order matters. Once a LEFT JOIN introduces NULLs, a later INNER JOIN that references those NULL columns can drop the preserved rows.

Here, if a customer has no order, the LEFT JOIN keeps them with NULL product_id, and the following INNER JOIN to products removes them again, an easy mistake to make under pressure.

SELECT c.name, p.title
FROM customers c
LEFT JOIN orders  o ON o.customer_id = c.id
JOIN products p ON p.id = o.product_id;
-- the INNER products join can cancel the LEFT JOIN

Keeping Outer Joins Outer

To preserve customers with no orders all the way through, make the downstream join LEFT as well. NULLs then flow safely to the end.

Rule of thumb: once you go outer, stay outer for every table that hangs off the optional branch.

SELECT c.name, p.title
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id
LEFT JOIN products p ON p.id = o.product_id;
-- customers with no orders are now kept (NULL title)

Aliases Become Essential at Scale

With four or five tables, several may share column names like id, name, or created_at. Short, consistent aliases stop ambiguity and keep the query readable.

Pick aliases that hint at the table (c, o, p) and qualify every column. Interviewers read your aliasing as a sign of how you write production SQL.

SELECT c.id   AS customer_id,
       o.id   AS order_id,
       p.id   AS product_id,
       p.title
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products  p ON p.id = o.product_id;

Bridge Tables for Many-to-Many

When two entities have a many-to-many relationship, a third junction (bridge) table sits between them, and you join through it. Students and courses connect via enrollments.

Recognizing that you must route through a bridge table is itself a common interview signal.

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses     c ON c.id = e.course_id;

Quick Check

Reason about a mixed-join chain.

Recap: Joining Three or More Tables

Carry these into the interview:

  • A multi-table join is a chain of two-table joins; reason about the intermediate result after each step.
  • A later ON clause can reference any earlier table in the chain.
  • Start from the central table and radiate to lookups.
  • Fan-out compounds across joins; check grain at each step.
  • An INNER JOIN after a LEFT JOIN can cancel it; stay outer to keep optional rows.
  • Many-to-many relationships are joined through a bridge table.

Frequently asked questions

Is the “Joining Three or More Tables” lesson free?

Yes — the full text of “Joining Three or More Tables” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Joining Three or More Tables”?

Chaining joins and reasoning about intermediate result sets. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Joining Three or More Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How INNER JOIN Matches Rows
  2. ON vs WHERE in Joins
  3. Join Fan-Out and Row Multiplication
  4. Joining Three or More Tables
← Back to SQL Interview Prep