JOINs
Back in lesson 1 you split a library into authors and books tables
instead of one giant sheet — great for avoiding duplicated data, but now you've got a new
problem: what if you want a list of book titles with their author's name printed right
next to them? That data lives in two separate tables. A JOIN is how you glue
matching rows from two tables back together for a single query.
💡 The Bottom Line
A JOIN combines rows from two tables based on a matching column — typically a foreign key
matching a primary key. INNER JOIN returns only rows that match in both tables;
LEFT JOIN returns every row from the left table, filling in NULL
for any right-side columns that have no match. Splitting data into separate tables only pays
off because JOIN lets you stitch it back together whenever you need to.
Why you need a join
Picture two tables: customers and orders, where each order
references the customer who placed it.
customers
+----+---------+
| id | name |
+----+---------+
| 1 | Priya |
| 2 | Marco |
| 3 | Aisha |
+----+---------+
orders
+----+-------------+--------+
| id | customer_id | total |
+----+-------------+--------+
| 1 | 1 | 42.00 |
| 2 | 1 | 18.50 |
| 3 | 2 | 99.00 |
+----+-------------+--------+Notice Aisha has no matching row in orders at all — she hasn't ordered yet.
That detail matters for the two join types below.
INNER JOIN: only the matches
INNER JOIN returns a row only when both tables have a match on the join
condition. Customers with zero orders simply won't appear:
SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;name | total
-------+-------
Priya | 42.00
Priya | 18.50
Marco | 99.00Priya appears twice, once per order — a join produces one output row per matching pair, not
one row per customer. Aisha doesn't appear at all, since she has no matching row in
orders.
🙋 There Are No Dumb Questions
Q: Why does Priya show up twice? Doesn't that duplicate her data?
A: It looks like duplication, but it isn't — her name isn't stored twice in the database,
only in this particular result set, because she has two matching orders. A join's job is to
produce one row per matching pair of (customer, order); if a customer has two orders, that's
two pairs.
LEFT JOIN: keep everything on the left, matched or not
Want Aisha to show up anyway, even with no orders? Use LEFT JOIN — it keeps
every row from the table on the left, and fills in NULL wherever there's no match
on the right:
SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;name | total
-------+-------
Priya | 42.00
Priya | 18.50
Marco | 99.00
Aisha | NULL⚠️ Watch Out
Forgetting the ON condition (or joining on the wrong columns) doesn't error
out — it silently pairs every row in one table with every row in the other, producing a
result far bigger and more nonsensical than you intended. Always double-check the columns in
your ON clause actually represent the same real-world thing (a foreign key
matching its primary key).
The mental model
Think of a join as laying the two tables side by side and drawing a line between rows that
match on the join column. INNER JOIN keeps only the rows with a line connecting
them; LEFT JOIN keeps every row from the left table, whether or not it has a line,
padding the missing side with NULL.
🔧 Try It Yourself
Using the customers and orders tables above, write a
LEFT JOIN query that returns each customer's name alongside the total number of
orders they've placed — for customers with no orders, that count should show as 0 rather
than being omitted. (Hint: you'll want this lesson's JOIN combined with next lesson's
COUNT and GROUP BY — sketch the join first, and revisit this after
the next lesson.)
✅ Quick Check
You run an INNER JOIN between customers and orders.
Aisha has never placed an order. What happens to her row?
Next up: once you can join related tables together, the natural next step is summarizing
them — counting orders, totaling revenue, averaging prices. That's aggregation and
GROUP BY.