Introduction
Real data is spread across several tables: customers in one, orders in another, products in a third. Each table describes one kind of thing, and they are connected by keys: every order records the id of its customer. Answering "how much did each city spend?" needs columns from both tables, so you join them. Joins are where many analyses quietly go wrong, by losing rows or counting some twice, so it pays to understand exactly what each kind does.
How it works
A key identifies a row, such as a customer id. A column in another table holding such ids, like an order's customer, is a foreign key. To join in plain Python, first build an index: a dictionary from key to row, by_id = {c["id"]: c for c in customers}. Then go through the other table and look up each row's foreign key. Lookups in a dictionary are fast, so this works for large tables, unlike a nested loop that compares every pair.
What happens to rows without a match depends on the kind of join:
- An inner join keeps only rows that match on both sides. An order with an unknown customer disappears.
- A left join keeps every row of the left table, filling missing values (None, 0 or "no orders") where there is no match. Use it when every customer must appear, even those with no orders.
- A right join is a left join with the tables swapped, and a full outer join keeps unmatched rows from both sides.
Relationships have a direction. Each order has one customer, but a customer can have many orders: many-to-one. Joining orders to customers keeps one row per order. Going the other way, from customers to orders, produces one row per order too, which is why a customer's details then appear several times; to get one row per customer, aggregate the orders first, then join the totals.
Rows whose foreign key matches nothing are orphans: an order for customer 9 who does not exist. They point at data problems (a deleted customer, a typing error) and should be counted and reported, not silently dropped.
In pandas, joins are pd.merge(orders, customers, left_on="customer", right_on="id", how="inner"), with how="left", "right" or "outer".
Example
# Joining: combine two tables through a shared key.
customers = [
{"id": 1, "name": "Asha", "city": "Dhaka"},
{"id": 2, "name": "Ben", "city": "Sylhet"},
{"id": 3, "name": "Chen", "city": "Khulna"},
]
orders = [
{"order": 101, "customer": 1, "amount": 250},
{"order": 102, "customer": 3, "amount": 80},
{"order": 103, "customer": 1, "amount": 40},
{"order": 104, "customer": 9, "amount": 99},
]
by_id = {c["id"]: c for c in customers} # an index on the key makes lookups fast
print("inner join: orders with their customer")
for o in orders:
c = by_id.get(o["customer"])
if c is not None:
print(o["order"], c["name"], c["city"], o["amount"])
print("left join: every customer with their total")
totals = {}
for o in orders:
totals[o["customer"]] = totals.get(o["customer"], 0) + o["amount"]
for c in customers:
print(c["name"], totals.get(c["id"], 0))
print("orders with an unknown customer:", [o["order"] for o in orders if o["customer"] not in by_id])Line by line
by_idindexes the customers by id.- The inner join prints each order with its customer's name and city; order 104 has no customer and is left out.
- The left join first aggregates orders into totals per customer, then lists every customer, using 0 for Ben, who ordered nothing.
- The last line reports the orphan order.
Expected result
inner join: orders with their customer
101 Asha Dhaka 250
102 Chen Khulna 80
103 Asha Dhaka 40
left join: every customer with their total
Asha 290
Ben 0
Chen 80
orders with an unknown customer: [104]
In the real world
Relational databases store data in many joined tables, and SQL's JOIN is one of its most used keywords. Analysts join sales to products and stores, patients to visits, students to courses. Data-quality checks routinely count orphan rows, because a growing number of them is an early sign that one system is losing records.
Common mistakes
- Using an inner join when every row of one table must appear, silently losing customers with no orders.
- Joining before aggregating and then summing values that were repeated by the join.
- Comparing keys of different types, such as the text "1" and the number 1, which never match.
- Ignoring orphans instead of counting and reporting them.
Best practices
- Decide which table must keep all its rows, and choose the join to match.
- Make sure keys have the same type and format in both tables before joining.
- Check row counts before and after every join, and report unmatched rows.
Summary
Tables are connected by keys and foreign keys. Index one table by its key in a dictionary and look up the other's foreign keys to join them. Inner joins keep only matches, left joins keep every row of one side, and orphan rows, which match nothing, should always be counted and reported.
Quick check
Key takeaways
- Index one table by its key in a dictionary, then look up each row of the other.
- An inner join keeps matches only; a left join keeps every row of one side.
- Always count the rows that did not match, because they usually reveal data problems.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.