# SQL Joins, Finally Explained > What a JOIN actually is, why your data lives in separate tables in the first place, and how INNER, LEFT, and the others stitch those tables back together without surprising you. --- # SQL Joins, Finally Explained You learned to split your data into separate tables - users in one place, orders in another, linked by an id. That was the right call. But now every real question you want to ask ("which customer placed this order?") lives across *two* tables, and you're stuck. A `JOIN` is how you put them back together for a single query. Joins confuse almost everyone at first, and it's not because you're slow - it's because nobody draws you the picture: a join is just **matching rows from one table to rows in another, using a shared key**. Once you can see that picture, INNER, LEFT, and the rest stop being magic words and become choices you make on purpose. ## How to read this - **Need the difference between INNER and LEFT right now?** Jump to [Phase 2: INNER vs LEFT (and the Others)](02-inner-vs-left.md) - it leads with annotated queries and result tables. - **Want joins to finally make sense?** Read in order. Phase 1 installs the mental model, Phase 2 shows the everyday joins, and Phase 3 covers the gotchas that bite people. ## The phases 1. **[Why Joins Exist](01-why-joins-exist.md)** - you split data into linked tables on purpose; a JOIN matches rows across them on the shared key. The core picture, with two tables becoming one result. 2. **[INNER vs LEFT (and the Others)](02-inner-vs-left.md)** - INNER (only matching rows), LEFT (every left row, NULLs where there's no match), plus a calm note on RIGHT and FULL. Each shown with a query and a result table side by side. 3. **[Join Gotchas](03-join-gotchas.md)** - the accidental cartesian explosion, NULLs from outer joins quietly breaking your `WHERE`, joining on the wrong columns, and how to sanity-check a join's row count. > This guide is about *getting the right rows*. Making a slow join *fast* - indexes on join keys, how the database actually executes a join - is its own topic, covered in [Why Is My Query Slow?](/guides/why-is-my-query-slow). > Related: [Relationships and Keys](/guides/relationships-and-keys) · [Querying Basics: SELECT & WHERE](/guides/querying-basics-select-where) · [Why Is My Query Slow?](/guides/why-is-my-query-slow) --- # Why Joins Exist If you've just come from learning about [relationships and keys](/guides/relationships-and-keys), you did something that felt slightly counterintuitive: you took information that "belongs together" - a customer and their orders - and deliberately split it into *separate tables*. That was correct. But it leaves you with a real problem the moment you want to answer an everyday question, and that problem is exactly what a join solves: **a join matches rows in one table to rows in another, using a value they share.** Get that picture and the rest of this guide is just variations on it. ## The two tables we'll use everywhere Throughout this guide we'll use the same tiny example: a `users` table and an `orders` table. Here they are. ```text users orders ┌────┬─────────┐ ┌──────────┬─────────┬────────┐ │ id │ name │ │ order_id │ user_id │ amount │ ├────┼─────────┤ ├──────────┼─────────┼────────┤ │ 1 │ Ada │ │ 101 │ 1 │ 40 │ │ 2 │ Grace │ │ 102 │ 1 │ 15 │ │ 3 │ Linus │ │ 103 │ 2 │ 90 │ └────┴─────────┘ │ 104 │ 7 │ 25 │ └──────────┴─────────┴────────┘ ``` Notice the link: every order carries a `user_id`. That column is how an order points back to the person who placed it. Order `101` has `user_id = 1`, and user `1` is Ada - so order `101` is Ada's. 📝 **Terminology - foreign key.** A *foreign key* is a column in one table whose value refers to a row in another table. Here, `orders.user_id` is a foreign key pointing at `users.id`. It's the thread that ties the two tables together, and it's the column a join will match on. (Look closely and you'll spot two oddities: order `104` has `user_id = 7`, but there's no user `7`. And user `3`, Linus, has no orders at all. Those aren't mistakes - they're the exact cases that make Phase 2 and Phase 3 matter. Hold onto them.) ## Why the data is split in the first place **The common wrong instinct.** When people first feel this pain, they think: "This is silly - I should have put the user's name directly *in* the orders table. Then I wouldn't need a join at all." **Why that falls apart.** Imagine you did that - every order row carries its own copy of `name = "Ada"`. Now Ada changes her name. You have to find and update every order she ever placed, and if you miss one, your data disagrees with itself. Worse, a user who hasn't ordered yet has nowhere to exist. Splitting users and orders into separate tables means each fact lives in exactly one place: Ada's name is stored once, in `users`. That's why joins exist - the cost of storing each fact once is that you need a way to *recombine* facts on demand. 💡 **Key point.** You didn't split your tables to make querying harder. You split them so each fact is stored once. A join is the tool that pays that back: it recombines the split tables for a single question, without duplicating anything on disk. ## What a join actually is **What it actually is.** A join is an instruction to the database: "for each row in table A, go find the matching row(s) in table B, and glue them together into wider rows." You tell it *how* to decide what "matching" means - almost always "where this column equals that column." **What it does in real life.** Let's ask the everyday question: *show each order along with the name of the person who placed it.* That answer needs columns from both tables, so we join them. ```sql SELECT orders.order_id, users.name, orders.amount FROM orders JOIN users ON orders.user_id = users.id; ``` The `ON orders.user_id = users.id` is the heart of it. It's the matching rule - the *join condition*. It tells the database: a row in `orders` matches a row in `users` when the order's `user_id` equals the user's `id`. ```text order_id │ name │ amount ──────────┼───────┼──────── 101 │ Ada │ 40 102 │ Ada │ 15 103 │ Grace │ 90 ``` *What just happened:* For each order, the database looked up the user whose `id` matched that order's `user_id`, then produced one wide row combining columns from both tables. Order `101` (`user_id = 1`) got paired with Ada; order `103` (`user_id = 2`) got paired with Grace. The two separate tables became one result that answers your actual question. You can run the same move right now on two built-in tables - `books` and `authors`, linked by `books.author_id`: ```sql runnable SELECT books.title, authors.name FROM books JOIN authors ON books.author_id = authors.id; ``` *What just happened:* For each book, the database looked up the author whose `id` matched the book's `author_id`, then glued the two together into one wide row - every book shown beside the person who wrote it. Two separate tables became one result that answers a question spanning both. ⚠️ **Where did orders go?** You started with four orders but got back three rows. Order `104` (`user_id = 7`) vanished, because there's no user `7` to match it to. That's not a bug - it's the *defining behavior* of the plain `JOIN` you just wrote (it's an INNER join, which keeps only rows that match on both sides). Whether that's what you want is the entire subject of Phase 2. For now, notice that a join doesn't only combine rows - it can also *drop* the ones that don't match. ## How to read a join condition out loud When you see a join, read it as a sentence and it stops looking cryptic: ```text FROM orders "start with the orders table" JOIN users "bring in the users table" ON orders.user_id = users.id "matching each order to the user whose id equals the order's user_id" ``` The `ON` clause is always answering one question: *how do I know which row over here goes with which row over there?* For the rest of this guide, whenever a join surprises you, the first thing to check is the `ON` clause - it's the rule the whole result is built from. 📝 **Terminology - table alias.** You'll often see joins written with short aliases to save typing: `FROM orders o JOIN users u ON o.user_id = u.id`. The `o` and `u` are just nicknames for the tables. We'll mostly spell the table names out in full here for clarity, but aliases mean exactly the same thing. ## Why this saves you later Once you see a join as "match rows from A to rows in B on a shared key," a huge amount of SQL stops being intimidating - reports pulling from five tables are just this same move, repeated. The day a query returns the wrong number of rows, you'll know exactly where to look: the matching rule in the `ON` clause, and whether you wanted non-matching rows kept or dropped. That single decision is Phase 2. ## Recap 1. You split data into separate tables so each fact is stored **once** - that's good design, not a mistake. 2. A **foreign key** (like `orders.user_id`) is the thread linking a row in one table to a row in another. 3. A **join** recombines those tables for one query by **matching rows on a shared value**, defined in the `ON` clause. 4. A plain `JOIN` keeps only rows that find a match on both sides - so it can *drop* rows (order `104` disappeared). Controlling that is what comes next. --- # INNER vs LEFT (and the Others) In Phase 1 you wrote a plain `JOIN` and noticed something: an order disappeared, and a user with no orders never showed up. That wasn't random - it's the single most important choice in joining tables: **do you want to keep the rows that don't have a match, or drop them?** SQL gives it different join *types* with different names. Two join types cover almost everything you'll ever write: `INNER JOIN` and `LEFT JOIN`. We'll do each with a query and its result side by side, then explain RIGHT and FULL calmly so they hold no mystery either. Same tables as Phase 1: ```text users orders ┌────┬─────────┐ ┌──────────┬─────────┬────────┐ │ id │ name │ │ order_id │ user_id │ amount │ ├────┼─────────┤ ├──────────┼─────────┼────────┤ │ 1 │ Ada │ │ 101 │ 1 │ 40 │ │ 2 │ Grace │ │ 102 │ 1 │ 15 │ │ 3 │ Linus │ │ 103 │ 2 │ 90 │ └────┴─────────┘ │ 104 │ 7 │ 25 │ └──────────┴─────────┴────────┘ ``` Remember the two oddities: order `104` points at a non-existent user `7`, and Linus (user `3`) has no orders. Watch what each join type does with them. ## The picture: which rows survive Before the syntax, hold this picture. Every join is choosing which rows to keep when a match is missing. ```mermaid flowchart LR subgraph INNER[INNER JOIN: keep only rows matching on BOTH sides] UI[users] --> MI[matched rows only] OI[orders] --> MI MI --> DI[unmatched rows on either side are dropped] end subgraph LEFT[LEFT JOIN: keep ALL left rows, attach matches where they exist] UL[ALL users] --> ML[every left row; NULLs where no matching order] OL[orders] --> ML end ``` The word that controls everything is "left." The **left table** is the one named first, in the `FROM` clause. In `FROM users LEFT JOIN orders`, `users` is the left table - so a LEFT JOIN guarantees every user appears, matched up with their orders or padded with NULLs. ## INNER JOIN - only the matches `INNER JOIN` keeps a row only when it finds a match on *both* sides. No match, no row. (Plain `JOIN` *is* an `INNER JOIN` - the word `INNER` is optional. Spelling it out makes your intent obvious, which is worth the four extra letters.) **A real example.** Show each order with the buyer's name: ```sql SELECT users.name, orders.order_id, orders.amount FROM users INNER JOIN orders ON orders.user_id = users.id; ``` ```text name │ order_id │ amount ───────┼──────────┼──────── Ada │ 101 │ 40 Ada │ 102 │ 15 Grace │ 103 │ 90 ``` *What just happened:* The database kept only rows where a user and an order matched on `user_id = id`. Linus is gone - no orders, no match. Order `104` is gone too - its `user_id = 7` matches no user. INNER is ruthless in both directions: anything without a partner is dropped. **When you want this.** Use INNER when the question only makes sense for matched rows: "list all orders with who bought them," "show employees with the department they belong to." If an unmatched row would be meaningless in the answer, INNER is right. Run an INNER JOIN yourself on the built-in `authors` and `books` tables: ```sql runnable SELECT authors.name, books.title FROM authors INNER JOIN books ON books.author_id = authors.id; ``` *What just happened:* The database kept only rows where an author matched a book on `author_id = id`. Every author who wrote at least one book in the table shows up beside each of their books; an author with no book here would simply not appear. ## LEFT JOIN - every left row, no matter what `LEFT JOIN` keeps **every** row from the left table. Where a left row has a matching right row, it attaches it. Where it doesn't, it still keeps the left row and fills the right table's columns with `NULL`. 📝 **Terminology - NULL.** `NULL` is SQL's "there is no value here." It is not zero and not an empty string - it specifically means *unknown / absent*. A LEFT JOIN produces NULLs on purpose, to say "this left row had no match on the right." **A real example.** List *every* user, with their orders if they have any: ```sql SELECT users.name, orders.order_id, orders.amount FROM users LEFT JOIN orders ON orders.user_id = users.id; ``` ```text name │ order_id │ amount ───────┼──────────┼──────── Ada │ 101 │ 40 Ada │ 102 │ 15 Grace │ 103 │ 90 Linus │ NULL │ NULL ``` *What just happened:* Every user from the left table survived - including Linus. Ada and Grace got their orders attached as before. Linus had no matching order, so the database kept his row anyway and put `NULL` in the columns from `orders`. That NULL row is the whole reason to reach for a LEFT JOIN: "this user has zero orders" shows up instead of silently vanishing. Notice what's *still* missing: order `104`. It points at the non-existent user `7`, and we put `users` on the left, so an unmatched *order* still gets dropped. A LEFT JOIN protects the left table's rows, not the right table's. **When you want this.** Use LEFT when you need the complete left side regardless of matches: "all users and how many orders each has (including zero)," "every product and its reviews, even products with none." Any time "show me the ones with *none*" is part of the question, you want a LEFT JOIN. 💡 **Key point.** The difference in one line: **INNER answers "where both exist," LEFT answers "everything on the left, plus the right where it exists."** Choosing wrong is how you accidentally hide your zero-order users - or accidentally include rows you meant to filter out. ## RIGHT and FULL - the other two, demystified Two more names, neither mysterious once you have INNER and LEFT. **RIGHT JOIN** is just a LEFT JOIN with the tables flipped: it keeps every row from the *right* table (the one after the `JOIN` keyword) and fills NULLs on the left where there's no match. These two queries return the same rows: ```sql FROM users LEFT JOIN orders ON ... -- keeps every user FROM orders RIGHT JOIN users ON ... -- also keeps every user ``` Because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order, most people pick LEFT and stick with it - it reads left-to-right, the way you think. RIGHT isn't wrong; it's just rarely the clearer choice. **FULL JOIN** (also written `FULL OUTER JOIN`) keeps **every row from both tables**: matched rows are paired up, and any unmatched row from *either* side is kept with NULLs on the other side. With our data: ```sql SELECT users.name, orders.order_id, orders.amount FROM users FULL JOIN orders ON orders.user_id = users.id; ``` ```text name │ order_id │ amount ───────┼──────────┼──────── Ada │ 101 │ 40 Ada │ 102 │ 15 Grace │ 103 │ 90 Linus │ NULL │ NULL ← user with no order NULL │ 104 │ 25 ← order with no user ``` *What just happened:* FULL kept the matched rows, *plus* Linus (a user with no order, NULLs on the right), *plus* order `104` (an order with no user, NULLs on the left). It's the only join here that surfaces *both* kinds of orphan at once - which is exactly why it's handy for data-quality checks like "find everything that doesn't line up." 📝 **Terminology - outer join.** LEFT, RIGHT, and FULL are collectively called *outer joins* - they all keep unmatched ("outer") rows and pad with NULLs. INNER is the only one that doesn't. So when someone says "use an outer join," they mean "keep the non-matching rows too." ⚠️ **Heads-up on database support.** INNER and LEFT work everywhere. FULL JOIN is supported by PostgreSQL, SQL Server, and Oracle, but **MySQL does not support `FULL JOIN`** (you emulate it with a `UNION` of a LEFT and a RIGHT join). If a `FULL JOIN` throws a syntax error, check which database you're on before assuming you mistyped. ## Why this saves you later The day a report is "missing" rows, or a count comes out too low, the cause is almost always an INNER join where you needed a LEFT - the unmatched rows got silently dropped. And the day NULLs appear where you didn't expect them, you'll recognize them instantly as outer-join padding, not corrupt data. ## Recap 1. **INNER JOIN** keeps only rows that match on both sides; unmatched rows on either side are dropped. 2. **LEFT JOIN** keeps every row from the **left** (first-named) table, attaching matches where they exist and **NULLs where they don't** - this is how "has none" shows up. 3. **RIGHT JOIN** is LEFT with the tables flipped; you can almost always just write LEFT instead. 4. **FULL JOIN** keeps unmatched rows from **both** sides with NULLs - great for finding orphans, but unsupported in MySQL. 5. LEFT/RIGHT/FULL are **outer joins** (they keep non-matches); INNER is the only one that doesn't. Now you can get exactly the rows you intend. Next, the ways a join can betray you even when the type is right - and how to catch them. --- Switch the join type and watch which rows survive - and where NULLs appear: ```playground-join ``` Watch it animated: [SQL joins](/explainers/Joins.dc.html) ## Try it yourself Run a real join against the sample `authors` and `books` tables: ```sql runnable SELECT authors.name, books.title, books.year FROM authors JOIN books ON books.author_id = authors.id ORDER BY books.year; ``` --- # Join Gotchas You understand the join types now. This phase is about moments where the *type* was right but the result still came out wrong - the query runs without an error, returns a plausible number, and quietly lies to you. These are the bugs that ship to production because nothing turned red. All three have the same cure: a thirty-second sanity check, at the end. Same `users` and `orders` tables from Phases 1 and 2. ## The gotcha cheat-card > **Result looks wrong? Find the symptom, then read the section.** | Symptom | Likely cause | Section | |---|---|---| | Way too many rows; counts inflated; duplicates everywhere | Missing or wrong `ON` → cartesian explosion | §1 | | LEFT JOIN's "no match" rows vanished after you added a `WHERE` | `WHERE` on the right table filters out the NULL rows | §2 | | Rows match that shouldn't (or none match) | Joining on the wrong columns | §3 | | "Is this even right?" | Compare the join's row count to the base table | §4 | --- ## 1. The accidental cartesian explosion ⚠️ **This is the big one.** If you forget the `ON` clause, or write a join condition that's always true, the database doesn't error - it pairs **every** row on the left with **every** row on the right. That's called a *cartesian product* (or *cross join*), and it makes your row count explode. 📝 **Terminology - cartesian product / cross join.** Pairing every row of A with every row of B. If A has 3 rows and B has 4, you get 3 × 4 = 12 rows. The numbers stay small here, but on real tables - 10,000 users and 50,000 orders - a cartesian product is 500,000,000 rows. That's how a forgotten `ON` clause locks up a database. **What it looks like.** Here's the join with no condition (some databases write this as `CROSS JOIN`; a comma between tables does the same thing): ```sql SELECT users.name, orders.order_id FROM users CROSS JOIN orders; ``` ```text name │ order_id ───────┼────────── Ada │ 101 Ada │ 102 Ada │ 103 Ada │ 104 Grace │ 101 Grace │ 102 Grace │ 103 Grace │ 104 Linus │ 101 ... │ ... (12 rows total: 3 users × 4 orders) ``` *What just happened:* With no matching rule, every user got paired with every order - Ada with order `104` (not hers), Linus with all four (none are his). 3 users × 4 orders = 12 meaningless rows. The query "worked." The answer is garbage. **The subtler version.** You don't have to omit `ON` entirely to cause this. Join on a column that isn't unique - say two order rows happen to share some value you joined on - and each left row fans out across all of them. The row count balloons into duplicate-looking rows you didn't expect. The fix is always the same: **join on a key that uniquely identifies a row** (like `users.id`), not a column that repeats. **The calm fix.** Always write an `ON` clause, and make it compare the foreign key to the key it references: ```sql SELECT users.name, orders.order_id FROM users INNER JOIN orders ON orders.user_id = users.id; ``` This brings you back to the sensible 3-row result from Phase 2. If you ever *intend* a cross join (it has legitimate uses, like generating every combination of two small lists), write `CROSS JOIN` explicitly so the next person knows you meant it. ## 2. NULLs from an outer join quietly breaking your WHERE This one is sneaky because it punishes you for doing two correct things at once. You write a LEFT JOIN to keep users with no orders - good. You add a `WHERE` clause to filter on something in the orders table - also reasonable. Your no-order users silently vanish, turning your LEFT JOIN back into an INNER JOIN without warning. **What it looks like.** "Show every user, plus their order amount, but only orders over 20." Tempting to write: ```sql SELECT users.name, orders.amount FROM users LEFT JOIN orders ON orders.user_id = users.id WHERE orders.amount > 20; ``` ```text name │ amount ───────┼──────── Ada │ 40 Grace │ 90 ``` *What just happened:* Linus is gone - the very row your LEFT JOIN worked to keep. The LEFT JOIN first produced Linus with `amount = NULL` (no orders). Then `WHERE orders.amount > 20` ran, and `NULL > 20` is **not true** (in SQL, any comparison with NULL is "unknown," which `WHERE` treats as a no) - so Linus got filtered out. The `WHERE` on a right-table column quietly undid your outer join. **The calm fix.** If a condition is about *which right rows to match*, put it in the `ON` clause, not in `WHERE`. The `ON` clause is applied *during* matching, so unmatched left rows are still kept: ```sql SELECT users.name, orders.amount FROM users LEFT JOIN orders ON orders.user_id = users.id AND orders.amount > 20; ``` ```text name │ amount ───────┼──────── Ada │ 40 Grace │ 90 Linus │ NULL ``` *What just happened:* Linus is back. The `amount > 20` test now happens *while* matching, so Ada keeps only her order over 20 and Linus - no matching order at all - is still preserved with a NULL. Rule to remember: on an outer join, **conditions about the matched table go in `ON`; conditions that filter the final result go in `WHERE`.** (If you genuinely want to filter on a right column *and* drop non-matches, an INNER join is what you meant - write it as INNER.) ## 3. Joining on the wrong columns The database matches whatever columns you tell it to, even when that's nonsense - it can't know `orders.user_id` is meant to point at `users.id`; it only does what the `ON` clause says. Two ways this bites: - **Right name, wrong table's column.** Both tables might have an `id`. Writing `ON orders.id = users.id` matches *order ids* against *user ids* - pure coincidence when anything matches at all. You wanted `orders.user_id = users.id`. - **Plausible but incorrect key.** Joining on something like a name or an email that *looks* unique but isn't guaranteed to be. Two users named "Ada" and you'll cross-match their orders. Always join on the real key (the id), not on a human-readable field that merely seems unique. **The calm fix.** Say the `ON` clause out loud as a sentence (the trick from Phase 1): "match each order to the user whose `id` equals the order's `user_id`." If the sentence is true about your data, the columns are right. If it sounds absurd ("match each order to the user whose id equals the order's id"), you've found the bug. ## 4. The sanity check: does the row count make sense? Here's the habit that catches all three gotchas above before they reach anyone else. After writing a join, **ask what the row count *should* be, then check it.** If each order belongs to exactly one user (a many-orders-to-one-user relationship), joining `orders` to `users` should give you **one row per matching order - never more.** ```sql SELECT COUNT(*) FROM orders; -- the baseline SELECT COUNT(*) FROM orders INNER JOIN users ON orders.user_id = users.id; -- the join ``` ```text COUNT(*) ────────── 4 ← orders alone (our baseline) COUNT(*) ────────── 3 ← after INNER JOIN ``` *What just happened:* Four orders, three after the join. That drop is expected and explainable - order `104` has no matching user, so INNER dropped it. The number went *down* by exactly the orphan we already knew about. That's a healthy join. Now you know what each direction of surprise means: - **Count went UP** (more rows than your baseline table)? You've got a cartesian/duplicate problem (§1) - you're joining on something that isn't unique. This is the dangerous one; investigate immediately. - **Count dropped more than you expected**? An INNER join is silently discarding non-matching rows you wanted (the §2 trap), or your `ON` columns are wrong (§3). A LEFT join may be what you need. - **Count is exactly what you predicted**? You've earned your confidence. 💡 **Key point.** A join you can't predict the row count of is a join you don't understand yet. Predicting it first, then checking, is the single cheapest way to catch every gotcha in this phase. ## When the join is correct but slow Everything here has been about getting the *right* rows. A different problem - a correct join that takes ages on a big table - is usually about whether the columns in your `ON` clause are indexed, and how the database executes the match. That's covered in [Why Is My Query Slow?](/guides/why-is-my-query-slow). Get the join *correct* first (this guide); make it *fast* second (that one). ## Recap 1. **A missing or always-true `ON` clause** causes a cartesian explosion - every row paired with every row. Always join on a unique key. 2. **A `WHERE` on a right-table column** turns a LEFT JOIN back into an INNER JOIN by filtering out its NULL rows; put match conditions in `ON` instead. 3. **Joining on the wrong columns** matches nonsense without erroring - read the `ON` clause aloud to check it. 4. **Predict the row count, then verify it** - up means duplication, an unexpected drop means lost matches, exact means you're good. 5. Right rows first (this guide); fast joins second ([Why Is My Query Slow?](/guides/why-is-my-query-slow)). You can now join tables on purpose, pick the type that keeps exactly the rows you mean, and catch the three classic ways a join lies. The data you split apart goes back together - correctly, and without surprises.