SQL joins that actually make sense
You have two tables. Joins tell the DB which rows go together.
Setup
users: Alice (1), Bob (2), Carol (3)orders: two for Alice, one for Bob, none for Carol
The four joins
INNER JOIN
Only rows where BOTH sides match. Carol disappears — she has no orders.
LEFT JOIN
All rows from LEFT table + matches from RIGHT (or NULLs). Carol appears with NULL amount.
RIGHT JOIN
Mirror of LEFT. Rare — usually you just flip the tables and use LEFT.
FULL OUTER JOIN
All rows from both sides, matched where possible. SQLite doesn't support directly (use UNION of two LEFT joins).
Mental model
Think of each table as a circle. INNER = intersection. LEFT = left circle whole. RIGHT = right circle whole. FULL = both circles.
Aggregation trick: LEFT JOIN + COALESCE
The last query is the killer real-world pattern:
- LEFT JOIN keeps Carol.
COALESCE(SUM(...), 0)turns her NULL into 0.- Now every user shows up in the total-spend leaderboard.
Try it
- Add a third table
productsand 3-way join to show what each user bought. - Find users whose total spend > 200.
3 graded exercises
Write the code, click Check. We’ll tell you exactly what to fix.
- Exercise 1
Write an INNER JOIN between
usersandordersonorders.user_id = users.id. Print how many rows the join returns. (Alice has 2 orders, Bob has 1, Carol has none, so the answer is3.) - Exercise 2
Write a LEFT JOIN from
userstoordersso Carol appears with a NULL amount. Find Carol's row and print her amount. In Python, SQL NULL becomesNone, so the printed output should beNone. - Exercise 3
LEFT JOIN +
GROUP BY+COALESCE(SUM(amount), 0)so every user has a numeric total — even if they have no orders. Print Carol's total (should be0).