←Back to the bookChapter 154 · Notebook 3 · Joins

Chapter 154 · Tools & Workflow · Notebook 3 of 5

Joins: Combining Related Tables

Relational databases split data across tables to avoid repeating it: customers in one, orders in another, line items in a third. A JOIN stitches them back together on a shared key, and it is the single most important idea in SQL.

import pandas as pd, sqlite3
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "sql-and-databases--shop.xlsx"
try:
    xls = pd.ExcelFile("../../data/" + fn)
except FileNotFoundError:
    xls = pd.ExcelFile(BASE + fn)
# Load the four sheets into an in-memory SQLite database, then query it with real SQL.
con = sqlite3.connect(":memory:")
for table in ["customers", "products", "orders", "order_items"]:
    pd.read_excel(xls, sheet_name=table).to_sql(table, con, index=False, if_exists="replace")

def q(sql):
    """Run a SQL query against the shop database and return the result as a DataFrame."""
    return pd.read_sql_query(sql, con)

print("tables:", [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")])
q("SELECT * FROM products LIMIT 3")

tables: ['customers', 'products', 'orders', 'order_items']

	product_id	product	category	unit_price
0	1	Cold Brew Kit	Equipment	39
1	2	Pour-Over Set	Equipment	28
2	3	Espresso Beans 1kg	Beans	22

DEMO 1 · INNER JOIN: match rows on a key

orders holds a customer_id but not the customer's details. Join to the customers table on that key to bring the region and segment alongside each order. INNER JOIN keeps only rows that match on both sides.

q("""SELECT o.order_id, o.order_date, c.name, c.region, o.channel
       FROM orders o
       JOIN customers c ON o.customer_id = c.customer_id
       ORDER BY o.order_id
       LIMIT 5""")

	order_id	order_date	name	region	channel
0	1001	2024-08-18	Customer 07	West	web
1	1002	2024-09-13	Customer 12	West	web
2	1003	2024-12-21	Customer 05	North	mobile
3	1004	2024-07-09	Customer 17	West	web
4	1005	2024-05-10	Customer 21	West	web

DEMO 2 · Join, then aggregate: revenue by region

The payoff. Chain three tables, line items to their order to that order's customer, then GROUP BY the customer's region. Revenue by region is invisible in any single table; the join makes it computable.

q("""SELECT c.region, ROUND(SUM(oi.revenue), 2) AS revenue
       FROM order_items oi
       JOIN orders o     ON oi.order_id    = o.order_id
       JOIN customers c  ON o.customer_id  = c.customer_id
       GROUP BY c.region
       ORDER BY revenue DESC""")

	region	revenue
0	North	43069.15
1	West	26514.30
2	Central	19658.15
3	East	15197.55
4	South	10153.25

DEMO 3 · Revenue by category

The same move through a different table: join line items to products to group revenue by category. Equipment leads.

q("""SELECT p.category,
              COUNT(*)               AS items,
              ROUND(SUM(oi.revenue),2) AS revenue
       FROM order_items oi
       JOIN products p ON oi.product_id = p.product_id
       GROUP BY p.category
       ORDER BY revenue DESC""")

	category	items	revenue
0	Equipment	492	57006.05
1	Beans	361	33243.80
2	Accessories	574	19073.80
3	Other	67	5268.75

DEMO 4 · LEFT JOIN: keep rows that do not match

An INNER JOIN drops rows with no match. A LEFT JOIN keeps every row from the left table and fills NULL where the right has nothing, which is exactly how you find customers who have never ordered.

q("""SELECT c.customer_id, c.name, c.region
       FROM customers c
       LEFT JOIN orders o ON c.customer_id = o.customer_id
       WHERE o.order_id IS NULL
       ORDER BY c.customer_id""")

	customer_id	name	region
0	37	Customer 37	North
1	38	Customer 38	East
2	39	Customer 39	Central
3	40	Customer 40	North

DEMO 5 · A full four-table join: top customers

Bring all four tables together to rank customers by total spend, the kind of question a real business asks every day.

q("""SELECT c.name, c.region, c.segment,
              ROUND(SUM(oi.revenue), 2) AS total_spend
       FROM order_items oi
       JOIN orders o    ON oi.order_id   = o.order_id
       JOIN customers c ON o.customer_id = c.customer_id
       GROUP BY c.customer_id
       ORDER BY total_spend DESC
       LIMIT 5""")

	name	region	segment	total_spend
0	Customer 27	Central	Consumer	5495.55
1	Customer 07	West	Consumer	5247.60
2	Customer 21	West	Business	4742.40
3	Customer 34	North	Consumer	4030.05
4	Customer 03	North	Business	3990.55

Wrap-up

A JOIN matches rows across tables on a shared key: INNER keeps only matches, LEFT keeps every left-hand row and marks the misses with NULL. Joins plus GROUP BY answer almost every cross-table business question, and they are the reason data is worth splitting into tidy tables in the first place. Next, a way to summarize without losing the individual rows.

←Back to the bookChapter 154 · Notebook 3 · Joins