AI Hints
Homeโ€บSQL Tutorialโ€บJoining Many Tables
SQL ยท Lesson 48 of 126

Joining Three Tables

Order lines with book details.

โฑ 2 min readโ–ถ Video at 1:07:40๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Each order can contain several books. Those details live in the order items table. One row per book, per order, with a quantity. But order items only stores the book's ID. To see the title and the price, we need to join to books as well.

Joining Three Tables
From the video: Joining Three Tables
  • An order holds several books
  • order_items: one row per book
  • Titles and prices live in books

How the tables connect

Here's the map of Helen's shop. A customer places orders. Each order has order items. And each order item points to one book. Every arrow is a pair of matching ID columns. To travel from a customer to a book title, you follow the arrows, one join at a time.

  1. customerscustomer_id
  2. ordersorder_id
  3. order_itemsbook_id
  4. bookstitle, price
How the tables connect
From the video: How the tables connect

Order lines with titles

Let's start with two tables. Order items, joined to books on the book ID. Now each line shows the title, the quantity, and the price. Run it. Order one thousand and one had two different books. Order one thousand and three had two copies of Wild Coast.

query.sqlsql
SELECT oi.order_id,
       b.title,
       oi.quantity,
       b.price
FROM order_items AS oi
JOIN books AS b ON b.book_id = oi.book_id
ORDER BY oi.order_id
LIMIT 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚      title       โ”‚ quantity โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1001     โ”‚ Stars Over Lagos โ”‚ 1        โ”‚ 9.99  โ”‚
โ”‚ 1001     โ”‚ Startup Stories  โ”‚ 1        โ”‚ 19.99 โ”‚
โ”‚ 1002     โ”‚ Spice Roads      โ”‚ 1        โ”‚ 27.99 โ”‚
โ”‚ 1003     โ”‚ Wild Coast       โ”‚ 2        โ”‚ 11.49 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Four tables in one query

Now let's go all the way, from a customer to the titles they bought. Just add one join after another. Each join has its own on, linking the new table to one already in the query. Run it. Tara Brown bought three books, across her two orders.

query.sqlsql
SELECT c.first_name,
       c.last_name,
       b.title
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN books       AS b  ON b.book_id     = oi.book_id
WHERE c.first_name = 'Tara' AND c.last_name = 'Brown';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚      title       โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Tara       โ”‚ Brown     โ”‚ Stars Over Lagos โ”‚
โ”‚ Tara       โ”‚ Brown     โ”‚ Startup Stories  โ”‚
โ”‚ Tara       โ”‚ Brown     โ”‚ Spice Roads      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Tips for many joins

A few tips when joining several tables. Follow the arrows, one table at a time. Give every table a short alias. Line up your joins, so they're easy to read. And if a result looks wrong, remove the last join, and check again.

Follow the arrowsone join at a time
Short aliasesc, o, oi, b
Line them upeasy to read
Debugremove the last join

Summary

You can chain as many joins as you need. Each one adds a table, with its own on. Next, using joins to answer money questions.

Key takeaways
  • One JOIN per table
  • Each has its own ON
  • Follow the arrows
Next up: Revenue with Joins โ€” JOIN plus GROUP BY