{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"the-four-joins","title":"The four joins, and what each one is for","url":"https://addaly.com/learn/data-and-sql/the-four-joins","position":14,"of":66,"minutes":8,"answer":"All four joins share one mechanism, one output row per match, and differ only in what they do with the leftovers.","keyIdea":"All four joins share one mechanism, one output row per match, and differ only in what they do with the leftovers; the row count after a join is the check that tells you whether a key matched more than once."},"body":{"format":"markdown","text":"## One mechanism, four decisions about the leftovers\n\nEvery join does the same thing: for each row on the left, find rows on the right where the ON condition holds, and emit one output row per match. The four join types differ in a single respect: what happens to rows that found nothing.\n\n- **INNER JOIN**: unmatched rows on either side are dropped.\n- **LEFT JOIN**: every left row survives; unmatched ones get NULLs on the right.\n- **RIGHT JOIN**: the mirror image. Every right row survives.\n- **FULL OUTER JOIN**: everything from both sides survives, with NULLs wherever there was no match.\n\nThat is the whole taxonomy. There is nothing else to memorise.\n\n## When each is the right choice\n\n**INNER** is right when the relationship is guaranteed and you want it enforced. `orders` joined to `customers` on a NOT NULL foreign key should match every time; if the row count drops, you have found a data problem and you want to know.\n\n> Figure: One mechanism, and what the join types do with the leftovers. A two by two grid, rows No match and Two matches, columns INNER JOIN and LEFT JOIN: No match and INNER JOIN: Row dropped; No match and LEFT JOIN: Row kept; Two matches and INNER JOIN: Two rows out; Two matches and LEFT JOIN: Two rows out. The join types differ in one column of this table, not two. Zero matches is the only thing INNER and LEFT disagree about; two matches produces two output rows in both, which is where a total silently doubles.\n\n**LEFT** is right whenever the left table is your population. \"All students, with their payments\" is a left join, because a student who has never paid is still a student and must appear in the report. This is the join you will write most often, and the reason is worth internalising: **the left table defines who is in the answer**, and the right table only adds detail.\n\n**RIGHT** is rare in practice. Anything a right join does, a left join with the tables swapped does more readably. Its main appearance in real code is accidental, when somebody reorders a query and does not adjust the join type.\n\n**FULL OUTER** has one great use: reconciliation. When you have two systems that should agree and want to see everything that does not line up:\n\n```sql\nSELECT\n  coalesce(a.reference, b.reference) AS reference,\n  a.amount AS in_ledger,\n  b.amount AS in_bank\nFROM ledger a\nFULL OUTER JOIN bank b ON b.reference = a.reference\nWHERE a.reference IS NULL       -- in the bank, missing from the ledger\n   OR b.reference IS NULL       -- in the ledger, missing from the bank\n   OR a.amount <> b.amount;     -- present in both, disagreeing\n```\n\nThree failure modes in one result set. MySQL has no FULL OUTER JOIN; the standard workaround is a `LEFT JOIN` unioned with a `RIGHT JOIN`.\n\n## The join that has no ON clause\n\n`CROSS JOIN` pairs every left row with every right row. Ten rows against twelve gives 120. It is deliberate and useful for generating grids (every product against every month) and it is also what you get by accident when you write an old-style comma join and forget the WHERE:\n\n```sql\nSELECT * FROM orders, customers;   -- 31,940 x 8,200 = 261 million rows\n```\n\nUse explicit `JOIN ... ON` syntax always. It puts the join condition next to the join, so a missing condition is a syntax error instead of a query that runs for an hour.\n\n## USING and NATURAL, and why to be careful\n\n`USING (customer_id)` is shorthand for `ON a.customer_id = b.customer_id`, and it merges the two columns into one in the output. It is tidy and safe.\n\n`NATURAL JOIN` joins on *every* column with a matching name in both tables. It reads beautifully and is a trap: the day somebody adds a `created_at` column to both tables, your natural join silently starts requiring those timestamps to be equal, and your result becomes empty. Nothing errors. Do not use it in anything that has to keep working.\n\n## Reading a join out loud\n\nThe habit that makes joins stop being confusing is to narrate each one in a fixed shape:\n\n> For each **order**, find the **customer** whose id matches, of which there is exactly **one**, and it is **guaranteed to exist**.\n\nThree facts: the left thing, the right thing, and the multiplicity. If the multiplicity is \"one, guaranteed\", use INNER and expect the row count to stay the same. If it is \"at most one\", use LEFT and expect the row count to stay the same. If it is \"possibly many\", the row count will grow and you must decide what to do about that before you write an aggregate.\n\n## The count that checks your work\n\n```sql\nSELECT count(*) FROM orders;                    -- 31,940\nSELECT count(*) FROM orders o\nJOIN customers c ON c.id = o.customer_id;       -- 31,940 → good\n```\n\nSame number: every order matched exactly one customer. Fewer: an inner join dropped orders with a missing or non-matching customer, and you should find out how many and why. More: a customer id appears more than once in `customers`, which means either the table's grain is not what you thought or there are duplicates, and either way your revenue total is now too big.\n\nRun this comparison every time. It is two queries, it takes ten seconds, and it turns join bugs from something you discover in a meeting into something you discover while writing."},"check":{"question":"`orders` has 31,940 rows. `SELECT ... FROM orders o LEFT JOIN customers c ON c.id = o.customer_id` returns 34,100 rows. What do the extra 2,160 rows tell you?","options":[{"text":"Some orders have a NULL `customer_id`, and the LEFT JOIN is keeping them with NULL customer details.","correct":false,"tempting":"LEFT JOIN keeps unmatched rows but does not duplicate them; NULL keys would leave the count unchanged at 31,940."},{"text":"Some `customer_id` values match more than one row in `customers`, so those orders were emitted more than once.","correct":true},{"text":"The join condition is missing, so the query has degenerated into a cross join.","correct":false,"tempting":"A cross join of 31,940 orders against a customer table would produce hundreds of millions of rows, not 2,160 extra."},{"text":"A LEFT JOIN always returns at least as many rows as both tables combined, so 34,100 is expected and nothing needs checking.","correct":false,"tempting":"Invents a rule; a LEFT JOIN returns at least as many rows as the left table, and exactly that many when each key matches at most once."}]},"prev":{"slug":"joins-that-multiply","title":"Joins: reason about them, do not memorise them","url":"https://addaly.com/learn/data-and-sql/joins-that-multiply"},"next":{"slug":"join-keys-that-do-not-match","title":"When the join key looks right and matches nothing","url":"https://addaly.com/learn/data-and-sql/join-keys-that-do-not-match"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The four joins, and what each one is for', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/the-four-joins (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The four joins, and what each one is for","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"All four joins share one mechanism, one output row per match, and differ only in what they do with the leftovers; the row count after a join is the check that tells you whether a key matched more than once.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"cols":["INNER JOIN","LEFT JOIN"],"rows":["No match","Two matches"],"type":"matrix","cells":[["Row dropped","Row kept"],["Two rows out","Two rows out"]],"title":"One mechanism, and what the join types do with the leftovers","caption":"The join types differ in one column of this table, not two. Zero matches is the only thing INNER and LEFT disagree about; two matches produces two output rows in both, which is where a total silently doubles.","cellLabels":[["nothing is emitted","right columns NULL"],["the left row repeats","the left row repeats"]]},"alt":"Figure: One mechanism, and what the join types do with the leftovers. A two by two grid, rows No match and Two matches, columns INNER JOIN and LEFT JOIN: No match and INNER JOIN: Row dropped; No match and LEFT JOIN: Row kept; Two matches and INNER JOIN: Two rows out; Two matches and LEFT JOIN: Two rows out. The join types differ in one column of this table, not two. Zero matches is the only thing INNER and LEFT disagree about; two matches produces two output rows in both, which is where a total silently doubles."}},"blocks":[{"t":"h2","id":"one-mechanism-four-decisions-about-the-leftovers","runs":[{"text":"One mechanism, four decisions about the leftovers"}]},{"t":"p","runs":[{"text":"Every join does the same thing: for each row on the left, find rows on the right where the ON condition holds, and emit one output row per match. The four join types differ in a single respect: what happens to rows that found nothing."}]},{"t":"ul","items":[[{"text":"INNER JOIN","b":true},{"text":": unmatched rows on either side are dropped."}],[{"text":"LEFT JOIN","b":true},{"text":": every left row survives; unmatched ones get NULLs on the right."}],[{"text":"RIGHT JOIN","b":true},{"text":": the mirror image. Every right row survives."}],[{"text":"FULL OUTER JOIN","b":true},{"text":": everything from both sides survives, with NULLs wherever there was no match."}]]},{"t":"p","runs":[{"text":"That is the whole taxonomy. There is nothing else to memorise."}]},{"t":"h2","id":"when-each-is-the-right-choice","runs":[{"text":"When each is the right choice"}]},{"t":"p","runs":[{"text":"INNER","b":true},{"text":" is right when the relationship is guaranteed and you want it enforced. "},{"text":"orders","code":true},{"text":" joined to "},{"text":"customers","code":true},{"text":" on a NOT NULL foreign key should match every time; if the row count drops, you have found a data problem and you want to know."}]},{"t":"p","runs":[{"text":"LEFT","b":true},{"text":" is right whenever the left table is your population. “All students, with their payments” is a left join, because a student who has never paid is still a student and must appear in the report. This is the join you will write most often, and the reason is worth internalising: "},{"text":"the left table defines who is in the answer","b":true},{"text":", and the right table only adds detail."}]},{"t":"p","runs":[{"text":"RIGHT","b":true},{"text":" is rare in practice. Anything a right join does, a left join with the tables swapped does more readably. Its main appearance in real code is accidental, when somebody reorders a query and does not adjust the join type."}]},{"t":"p","runs":[{"text":"FULL OUTER","b":true},{"text":" has one great use: reconciliation. When you have two systems that should agree and want to see everything that does not line up:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  coalesce(a.reference, b.reference) AS reference,\n  a.amount AS in_ledger,\n  b.amount AS in_bank\nFROM ledger a\nFULL OUTER JOIN bank b ON b.reference = a.reference\nWHERE a.reference IS NULL       -- in the bank, missing from the ledger\n   OR b.reference IS NULL       -- in the ledger, missing from the bank\n   OR a.amount <> b.amount;     -- present in both, disagreeing"},{"t":"p","runs":[{"text":"Three failure modes in one result set. MySQL has no FULL OUTER JOIN; the standard workaround is a "},{"text":"LEFT JOIN","code":true},{"text":" unioned with a "},{"text":"RIGHT JOIN","code":true},{"text":"."}]},{"t":"h2","id":"the-join-that-has-no-on-clause","runs":[{"text":"The join that has no ON clause"}]},{"t":"p","runs":[{"text":"CROSS JOIN","code":true},{"text":" pairs every left row with every right row. Ten rows against twelve gives 120. It is deliberate and useful for generating grids (every product against every month) and it is also what you get by accident when you write an old-style comma join and forget the WHERE:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM orders, customers;   -- 31,940 x 8,200 = 261 million rows"},{"t":"p","runs":[{"text":"Use explicit "},{"text":"JOIN ... ON","code":true},{"text":" syntax always. It puts the join condition next to the join, so a missing condition is a syntax error instead of a query that runs for an hour."}]},{"t":"h2","id":"using-and-natural-and-why-to-be-careful","runs":[{"text":"USING and NATURAL, and why to be careful"}]},{"t":"p","runs":[{"text":"USING (customer_id)","code":true},{"text":" is shorthand for "},{"text":"ON a.customer_id = b.customer_id","code":true},{"text":", and it merges the two columns into one in the output. It is tidy and safe."}]},{"t":"p","runs":[{"text":"NATURAL JOIN","code":true},{"text":" joins on "},{"text":"every","i":true},{"text":" column with a matching name in both tables. It reads beautifully and is a trap: the day somebody adds a "},{"text":"created_at","code":true},{"text":" column to both tables, your natural join silently starts requiring those timestamps to be equal, and your result becomes empty. Nothing errors. Do not use it in anything that has to keep working."}]},{"t":"h2","id":"reading-a-join-out-loud","runs":[{"text":"Reading a join out loud"}]},{"t":"p","runs":[{"text":"The habit that makes joins stop being confusing is to narrate each one in a fixed shape:"}]},{"t":"quote","runs":[{"text":"For each "},{"text":"order","b":true},{"text":", find the "},{"text":"customer","b":true},{"text":" whose id matches, of which there is exactly "},{"text":"one","b":true},{"text":", and it is "},{"text":"guaranteed to exist","b":true},{"text":"."}]},{"t":"p","runs":[{"text":"Three facts: the left thing, the right thing, and the multiplicity. If the multiplicity is “one, guaranteed”, use INNER and expect the row count to stay the same. If it is “at most one”, use LEFT and expect the row count to stay the same. If it is “possibly many”, the row count will grow and you must decide what to do about that before you write an aggregate."}]},{"t":"h2","id":"the-count-that-checks-your-work","runs":[{"text":"The count that checks your work"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*) FROM orders;                    -- 31,940\nSELECT count(*) FROM orders o\nJOIN customers c ON c.id = o.customer_id;       -- 31,940 → good"},{"t":"p","runs":[{"text":"Same number: every order matched exactly one customer. Fewer: an inner join dropped orders with a missing or non-matching customer, and you should find out how many and why. More: a customer id appears more than once in "},{"text":"customers","code":true},{"text":", which means either the table’s grain is not what you thought or there are duplicates, and either way your revenue total is now too big."}]},{"t":"p","runs":[{"text":"Run this comparison every time. It is two queries, it takes ten seconds, and it turns join bugs from something you discover in a meeting into something you discover while writing."}]}],"check":{"question":[{"text":"orders","code":true},{"text":" has 31,940 rows. "},{"text":"SELECT ... FROM orders o LEFT JOIN customers c ON c.id = o.customer_id","code":true},{"text":" returns 34,100 rows. What do the extra 2,160 rows tell you?"}],"options":[{"text":[{"text":"Some orders have a NULL "},{"text":"customer_id","code":true},{"text":", and the LEFT JOIN is keeping them with NULL customer details."}],"correct":false,"tempting":[{"text":"LEFT JOIN keeps unmatched rows but does not duplicate them; NULL keys would leave the count unchanged at 31,940."}]},{"text":[{"text":"Some "},{"text":"customer_id","code":true},{"text":" values match more than one row in "},{"text":"customers","code":true},{"text":", so those orders were emitted more than once."}],"correct":true,"tempting":null},{"text":[{"text":"The join condition is missing, so the query has degenerated into a cross join."}],"correct":false,"tempting":[{"text":"A cross join of 31,940 orders against a customer table would produce hundreds of millions of rows, not 2,160 extra."}]},{"text":[{"text":"A LEFT JOIN always returns at least as many rows as both tables combined, so 34,100 is expected and nothing needs checking."}],"correct":false,"tempting":[{"text":"Invents a rule; a LEFT JOIN returns at least as many rows as the left table, and exactly that many when each key matches at most once."}]}],"order":[3,0,2,1],"why":null},"prev":{"slug":"joins-that-multiply","title":"Joins: reason about them, do not memorise them","minutes":9},"next":{"slug":"join-keys-that-do-not-match","title":"When the join key looks right and matches nothing","minutes":8}}}