Joining Multiple Tables

Real schemas are rarely just two tables. An e-commerce database, for example, typically splits data across customers, orders, order_items, and products — each order can contain many line items, and each line item points to one product. Answering a realistic question like "which products has each customer bought, and how much did they spend?" means chaining several JOIN clauses together in a single query.

Extending the reference schema

order_items and products, added to customers/orders

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(50),
  price      DECIMAL(10, 2)
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id      INT,
  product_id    INT,
  quantity      INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO products (product_id, name, price) VALUES
  (1, 'Keyboard', 45.00),
  (2, 'Monitor',  199.99),
  (3, 'Mouse',    25.00);

INSERT INTO order_items (order_item_id, order_id, product_id, quantity) VALUES
  (1, 101, 1, 1),
  (2, 101, 3, 2),
  (3, 102, 2, 1),
  (4, 103, 3, 1);

Now there is a clear chain: customers to orders (one customer has many orders), orders to order_items (one order has many line items), and order_items to products (each line item points to exactly one product). Getting from a customer's name to the products they bought means walking that entire chain.

Worked example: a four-table join

Every product each customer has ordered

SQL

SELECT
  c.name        AS customer_name,
  o.order_id,
  p.name        AS product_name,
  oi.quantity,
  p.price,
  (oi.quantity * p.price) AS line_total
FROM customers c
JOIN orders o       ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
ORDER BY o.order_id;

customer_name | order_id | product_name | quantity | price  | line_total
--------------+----------+--------------+----------+--------+-----------
Alice         | 101      | Keyboard     | 1        | 45.00  | 45.00
Alice         | 101      | Mouse        | 2        | 25.00  | 50.00
Alice         | 102      | Monitor      | 1        | 199.99 | 199.99
Bob           | 103      | Mouse        | 1        | 25.00  | 25.00

Each additional JOIN clause pulls in one more table's worth of detail, and the query still reads top to bottom as a single, linear chain: start at customers, join to orders, join to order_items, join to products. Every join here defaults to an INNER JOIN, which is why Dave (who has no orders) and Carol's order 104 (which has no order_items in this sample data) do not appear — there is no matching row to walk the chain through.

Join order and readability

Join order rarely changes correctness, but it changes readability

For inner joins in particular, the order in which you write JOIN clauses usually does not affect the final result — the query optimizer analyzes the whole query and decides the actual, most efficient execution order on its own, which can be completely different from the order you typed. Write joins in whatever order tells the clearest story (usually following the natural relationship chain, like customers to orders to order_items to products), and let the database engine handle performance. Mixing in an OUTER JOIN changes this: the order and placement of a LEFT/RIGHT JOIN relative to other joins can change which rows survive, so extra care is needed once outer joins enter the chain.

A few habits keep multi-join queries maintainable as they grow: give every table a short, consistent alias (c, o, oi, p rather than switching styles mid-query); always qualify column names with their alias once more than one table is involved; and put each JOIN...ON pair on its own line so the relationship chain is easy to scan top to bottom.

Chaining JOIN clauses lets a single query pull related data from three or more tables.
A four-table chain like customers to orders to order_items to products is a very common real-world shape.
The optimizer decides actual execution order for inner joins — the order you write them mainly affects readability.
Consistent short aliases and one JOIN per line keep multi-table queries easy to follow.