How to Join Three Tables in SQL
To list the products in every order you need three tables: orders, order items and products. Add one JOIN at a time, each with its own ON, and follow the keys from table to table.
Step by step
Three tables
- One answer, three tables. Which products were in each order? The answer is spread over 3 tables: orders, order_items and products. You join them one step at a time.
- The link table. The orders table doesn't hold products, because one order can have many. order_items is the link table. It has one row per product in an order, with an order_id and a product_id.
First join
- Orders to items. Start with 2 tables. Select from orders AS o, then JOIN order_items AS i ON i.order_id = o.order_id. Show o.order_id, i.product_id and i.quantity. Run it. It returns 307 rows, 1 for each product in each order.
SELECT o.order_id, i.product_id, i.quantity FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id; - Just a number. But product_id is only a number. Nobody knows what product 10 is. The product names live in the products table.
Second join
- Items to products. To add the second join, delete the semicolon after o.order_id. Then type JOIN products AS p on a new line. Every JOIN needs its own ON. Add ON p.product_id = i.product_id; to match products to order_items. product_id is the key the 2 tables share.
o.order_id JOIN products AS pON p.product_id = i.product_id; - Show the name. At the top of the query, replace i.product_id with p.product_name. The results now show the product's name from products, not its number. Run it again. Every order now lists the names of the products in it.
p.product_name, - Names from products. These names come from the third table, products, which the query reaches through the product_id in order_items.
- Same row count. There are still 307 rows. Each item matches exactly 1 product, so the extra join added a column, not rows.
Recap
- Join three tables. To join 3 tables, add one JOIN at a time, each with its own ON. Follow the keys: orders to order_items, then order_items to products.
Common mistakes
- Leaving out the ON for the second JOIN. Every JOIN needs its own ON. Without one, some databases give an error and others pair every item with every product, so the row count jumps far past 307.
- Joining products straight to orders. orders has no product_id, so there's no key to match on. Join products to order_items on product_id, because that's the key the 2 tables share.
- Leaving the old semicolon in place. A semicolon ends the query, so a JOIN typed after it isn't part of the query and won't run. Remove it before you add the next JOIN, and keep a single semicolon at the very end.
- Column names without the table alias. order_id is in both orders and order_items, so a plain order_id gives an 'ambiguous column' error. Write o.order_id or i.order_id to say which table you mean.
When a business analyst uses this
When you check the data for a sales report requirement, join orders, order_items and products to list each order with its product names. Then compare the row count with the order lines in the source system before sign-off. If the count jumps after a join, one of the keys is wrong.
Questions people ask
Can you join three tables in SQL?
Yes. Add one JOIN for each extra table, each with its own ON that matches the shared key. The same pattern works for 4 or more tables.
How do you join three tables with a WHERE condition?
Put the WHERE after the last ON and before the semicolon. It filters the joined rows, and it can use columns from any of the 3 tables, such as p.product_name.
How do you LEFT JOIN three tables in SQL?
Write LEFT JOIN instead of JOIN for both joins. Orders with no items still show up, with NULL in the item and product columns. If the second join is a plain JOIN, it removes those orders again.
