Joins
Two tables walk into a query…
▶ the lesson · narrated sketch video · tap for sound
the intro
A JOIN combines rows from two tables using a matching column. INNER JOIN keeps only matches, LEFT JOIN keeps every row on the left even without a match. Joins are how normalized data becomes useful answers — and the number one interview topic in SQL.
fun fact ✦
There is even a “self join” — a table joined to itself. Org charts and comment threads run on it.
the docs
the deep dive
the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid
Normalization deliberately scattered your data across tables; the JOIN is how you stitch it back together at query time. The real skill is not memorizing join flavors — it is predicting, before you run anything, roughly how many rows the answer should hold. If you know the expected row count, every join type is just syntax.
know the row count before you run
key terms
join key
The shared column the match is made on. Usually a primary key meeting a foreign key.
ON o.user_id = u.idINNER JOIN
Keep only the rows that match in both tables.
orders that have a userLEFT JOIN
Keep every left row; fill the right side with NULL when there is no match.
users with zero orderscross join
Every row paired with every row. Usually a missing ON clause, occasionally exactly what you need.
sizes x colors = every SKUhow it works
- 1Name your tables
FROM users u gives you a short alias, so every column reference stays readable.
- 2Find the shared key
Orders carry user_id; users carry id. The join condition connects exactly those two.
- 3Pick the join type
Need only matches? INNER. Need every left row, gaps included? LEFT.
- 4Check the row count
A join can duplicate rows before you aggregate. Count the right column or the totals will inflate.
the code, explained
every user with their order count — zeros included
side by side
INNER JOIN
- only rows matching both sides
- missing pairs silently vanish
- counts can shrink surprisingly
LEFT JOIN
- every left row survives
- missing right becomes NULL
- count zeros: users with 0 orders
A join can multiply rows before you aggregate. One user with five orders turns users JOIN orders into five rows, so count(*) counts orders, not users. Count the far table id, or aggregate in a subquery first.
quick answers
How many joins is too many?
When you can no longer predict the row count. Five well-understood joins beat one scary number every time.
Why did my COUNT get bigger after a join?
Fan-out: a user with five orders appears five times. Count the far-side id, or aggregate per user in a subquery first.
Do joins scale?
Yes, with indexes on the join keys. Postgres hashes and merges millions of rows happily; missing indexes are what make joins feel slow.
quick hits about Joins
short answers · interviews, tips, trivia & hidden gems