Joining Three or More Tables

Real questions often need more than two tables. Keep adding JOINs, one per table, each with its own ON.

In the E-commerce database, an order has items, and each item points to a product:

• orders: one row per order
• order_items: one row per product in an order
• products: names and prices

Which products were in each order?

SELECT o.order_id, p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;

Add customers to see who bought what:

SELECT c.name, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;

Each JOIN connects the new table to one already in the query. Follow the keys like a path: customers → orders → order_items → products.