Joins explained
Normalization split your data into tidy linked tables — Users here, Orders there. That's great for keeping facts in one place, but now a single question like 'show me each order WITH the buyer's name' needs columns from BOTH tables. A JOIN is how you glue them back together for one answer: match each order's user_id to a user's id, and stitch the rows side by side. It's the everyday move that makes split tables usable, and once you picture it as 'match by id, then combine', it stops being scary.
Why you need a join at all
Because of normalization, an order row doesn't carry the buyer's name — it only stores the buyer's id (user_id). The name lives over in the Users table. So 'list every order and who bought it' can't be answered from one table alone; you need a column from Orders (the item) and a column from Users (the name). A join is the operation that brings them together. You tell the database the matching rule — 'where Orders.user_id equals Users.id' — and it walks the orders, finds each order's matching user by id, and produces a new wider row holding columns from both. You didn't undo normalization; you just temporarily combined the tidy tables to answer one question.
INNER join: keep only the matches
The default join is the INNER join, and its rule is strict: keep a row ONLY when there's a match on BOTH sides. Think of two overlapping circles — an inner join hands you just the OVERLAP, the rows that pair up. Walk the example. The 'Book' order has user_id 1, and Users has a row with id 1 (Maya) — they match, so out comes one combined row: 'Book, 1, Maya'. But suppose there's a 'Pen' order pointing at user_id 9, and no user 9 exists — no match, so that order is DROPPED from the result. And Ali (user 2) who never ordered anything? He has no matching order, so he's dropped too. An inner join shows you only the pairs that connect on both ends.
LEFT join: keep all of the left, fill blanks
Sometimes dropping the unmatched rows is wrong. 'Show me EVERY user and their orders — even users who haven't ordered yet' shouldn't silently erase Ali. That's what a LEFT join is for. A LEFT join keeps EVERY row from the left table (the one you wrote first), whether or not it found a match. Where a match exists, it fills in the right table's columns as usual. Where there's NO match, it keeps the left row anyway and leaves the right-side columns blank (the database calls that blank NULL — 'nothing here'). So 'Users LEFT JOIN Orders' gives you Maya with her orders AND Ali with an empty order column — nobody disappears. That's the whole difference: INNER keeps only the overlap; LEFT keeps all of the left side, padding the gaps with blanks. (There's a RIGHT join too — same idea, keep all of the right — and a FULL join that keeps both sides, but INNER and LEFT cover almost everything you'll meet.)
The overlap mental picture
Draw two overlapping circles, one per table. The middle, where they overlap, is the rows that MATCH by id. The outer crescents are the lonely rows — orders with no matching user, users with no orders. • INNER join = just the overlap (the matched middle). • LEFT join = the whole left circle (overlap + the left crescent), right-side blanks where there's no partner. • RIGHT join = the whole right circle, mirror image. • FULL join = both whole circles, blanks wherever a side has no partner. If you can picture which parts of the two circles you want to keep, you've already chosen your join. The SQL keyword is just the name for the picture in your head.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| INNER join (keep only matches) | Clean result — every row is a real, complete pair. No blanks to handle. Exactly right when you only care about rows that connect on both sides. | Silently drops rows that don't match. If you wanted to see the users with zero orders, an inner join hides them — easy to get a surprising 'missing rows' result. |
| LEFT join (keep all of the left) | Nothing on the left disappears — perfect for 'show every X, even the ones with no Y'. The unmatched rows show up with blanks so you can SEE the gaps. | You now have NULL (blank) values to deal with in the result, which you have to handle carefully in code or further filters. Slightly more to think about than a clean inner join. |
Questions you might have
▸Wait — why did we split the tables if we just have to join them back?
Splitting (normalization) is about how the data is STORED — each fact in one place, so nothing can contradict itself. A join is about how you READ it for one question — temporarily combining the tidy tables. You get both wins: storage stays clean and trustworthy, and you can still see an order with the buyer's name whenever you ask. The join doesn't un-split your data; it just stitches a view of it on demand.
▸Isn't joining two tables slow?
It can be more work than reading one table, but databases are built to do joins fast — especially when the matching column has an index (the id columns usually do). For everyday queries you won't notice. You only worry about join speed at huge scale, and even then the fix is usually an index, not avoiding the join. Don't keep duplicated data just to dodge a join — that brings back the anomalies normalization removed.
▸What's the real difference between INNER and LEFT again?
INNER keeps a row only if BOTH tables have a match — the overlap of the two circles. LEFT keeps EVERY row from the left table no matter what, and just leaves the right-side columns blank when there's no match. So 'users LEFT JOIN orders' still shows a user who never ordered (with empty order columns), while the INNER join would silently drop them.
▸What is this 'NULL' that shows up in a LEFT join?
NULL is the database's word for 'nothing here / no value'. In a LEFT join, when a left row has no match on the right, the right-side columns can't be filled — so the database puts NULL there. It's not zero and not an empty word; it literally means 'there was no matching row'. You handle it like 'this person has no orders yet'.
▸How does the database know which columns to match on?
You tell it, in the join's ON condition — for example 'ON orders.user_id = users.id'. That's the rule that says which two columns must be equal for rows to be considered a match. It's almost always a foreign key on one side matching the primary key on the other — the very pointer you set up when you split the tables. You name the matching columns; the database does the matching.
Best read after: Keys & relationships