Joining Multiple Tables

Objectives

By the end of this lesson, you should be able to:

• Chain more than one JOIN in a single query
• Trace how each ON condition connects one table to the next
• Combine multi-table joins with WHERE, GROUP BY, and aggregate functions

💡 Why this matters: Real questions often span more than two tables, “which customer ordered which product” needs customers, orders, and products all connected together, plus a link table in between when the relationship is many-to-many (Module 9).

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

Building on this module’s customers, orders, and products tables, with one more table connecting orders to the products within them:

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL
);

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (1, 1, 2),
  (1, 2, 1),
  (2, 3, 3),
  (3, 4, 1);

order_items is a junction table (Module 9), each row connects one order to one product with a quantity, exactly how “which products were in which order” gets modeled when a single order can contain several products.

Chaining Joins

SELECT c.name AS customer, p.name AS product, oi.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
ORDER BY c.name, p.name;

    customer     |     product     | quantity
------------------+------------------+-----------
 Casey Brooks    | Mystery Item    |        1
 Riley Nguyen    | Desk Lamp       |        1
 Riley Nguyen    | Notebook        |        3
 Riley Nguyen    | Wireless Mouse  |        2

Four tables, three JOIN clauses, each one connecting to the table already built up by the ones before it: customers to orders (by customer_id), orders to order_items (by order_id), order_items to products (by product_id). The result reads as one flat table, “customer, product, quantity,” even though that information started out spread across four separate tables.

Combining with WHERE, GROUP BY, and Aggregates

SELECT c.name AS customer, SUM(oi.quantity) AS total_items
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.name
ORDER BY c.name;

    customer     | total_items
------------------+-------------
 Casey Brooks    |           1
 Riley Nguyen    |           6

Everything from earlier modules still applies on top of a multi-table join, WHERE filters rows before grouping, GROUP BY and aggregate functions (Module 8) summarize the joined result exactly as if it were one table. A multi-table JOIN doesn’t replace those tools, it just gives them more columns to work with.

Try It

1. Write the four-table join above and confirm Casey Brooks appears with “Mystery Item.”
2. Add a WHERE clause keeping only rows where oi.quantity is greater than 1.
3. Write a query joining all four tables, grouped by customer, showing total quantity ordered per customer.
4. Explain, in your own words, the order the three JOIN clauses were chained in this lesson, and whether a different order would still work.

Recap

• More than two tables can be joined in a single query by chaining additional JOIN ... ON clauses.
• Each join typically connects to a table already brought in by an earlier join, forming a chain from one end of the relationship to the other.
• WHERE, GROUP BY, and aggregate functions work on a multi-table join’s result exactly as they would on a single table.

Next lesson: this module’s exercises, joining customers, products, categories, and a staff hierarchy for yourself.