{"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":"joins-that-multiply","title":"Joins: reason about them, do not memorise them","url":"https://addaly.com/learn/data-and-sql/joins-that-multiply","position":13,"of":66,"minutes":9,"answer":"A join emits one row per match; zero matches and two matches are where every join bug lives.","keyIdea":"A join emits one row per match; zero matches and two matches are where every join bug lives."},"body":{"format":"markdown","text":"## Forget the Venn diagrams\n\nThe circles do not tell you what actually happens, which is this:\n\n**For each row on the left, the database looks for rows on the right where the ON condition is true, and emits one output row per match.**\n\nEverything follows from that one sentence. In particular, two questions decide the behaviour of any join you will ever write:\n\n1. What happens to a left row with **zero** matches?\n2. What happens to a left row with **two** matches?\n\nAn inner join drops the zero-match row. A left join keeps it and fills the right-hand columns with NULL. That is the entire difference between them.\n\nAnd for two matches, both kinds of join do the same thing: they emit two rows. The left row is duplicated. This is not a bug and there is no flag to turn it off. It is what a join is.\n\n## Row multiplication, which is where the money goes wrong\n\n```sql\nSELECT c.name, c.credit_limit, o.amount\nFROM customers c\nJOIN orders o ON o.customer_id = c.id;\n```\n\nIf Mariana has three orders, Mariana appears three times, and her credit limit appears three times with her. Now watch this go wrong:\n\n```sql\nSELECT SUM(c.credit_limit)\nFROM customers c\nJOIN orders o ON o.customer_id = c.id;\n```\n\nThe total credit limit is now roughly the number of orders times the average limit. It is enormous. It is also completely plausible-looking, which is the dangerous part. Nothing errors. You get a number.\n\nThe defence is a sentence you say before you write the query: **\"one row of my result is ______.\"**\n\n- \"One row per order, with the customer's city attached.\" Each order has exactly one customer, so at most one match. Joining is safe.\n- \"One row per customer, with their total order value.\" That is one row per customer, but the join produces one row per order. So the join has to be followed by a grouping, or the total belongs in a subquery.\n\nIf you cannot finish that sentence, you are not ready to write the join.\n\n## The trap that turns a LEFT JOIN into an inner join\n\nThis is the single most common join bug in working code.\n\n```sql\nSELECT o.id, r.reason\nFROM orders o\nLEFT JOIN refunds r ON r.order_id = o.id\nWHERE r.reason <> 'damaged';\n```\n\nThe intent is \"all orders, with refund reason where there is one, excluding damage refunds.\" What you get is only orders that have a refund.\n\nWhy: the LEFT JOIN faithfully keeps the unmatched orders and sets `r.reason` to NULL for them. Then WHERE asks whether NULL is different from `'damaged'`, and the answer is not true: it is unknown. WHERE keeps only rows where the test is true. Every unmatched order is discarded a step after the join carefully preserved it.\n\nTwo fixes, and they mean different things:\n\n```sql\n-- Filter the right-hand side before matching: keeps all orders,\n-- and simply never attaches a damage refund to them.\nLEFT JOIN refunds r ON r.order_id = o.id AND r.reason <> 'damaged'\n\n-- Filter after matching, but spare the unmatched rows explicitly.\nWHERE (r.reason <> 'damaged' OR r.id IS NULL)\n```\n\nGeneral rule: a condition on the right-hand table of a LEFT JOIN belongs in ON. A condition on the left-hand table belongs in WHERE.\n\n## The anti-join, which is worth knowing by name\n\n\"Orders that have no refund at all\":\n\n```sql\nSELECT o.id\nFROM orders o\nLEFT JOIN refunds r ON r.order_id = o.id\nWHERE r.id IS NULL;\n```\n\nJoin everything, then keep only the rows where the right side came back empty. Read it as \"look for a match, and keep the failures.\" It is the standard way to ask \"which of these has none of those\", and once you have seen it you will use it constantly: students with no payment this month, products never sold, users who never opened the app.\n\n## The reasoning habit\n\nWhen a join surprises you, do not reach for a different join type and hope. Count. Run `SELECT COUNT(*)` on the left table alone, then on the join. If the join returned more rows than the left table has, something on the right matched more than once, and you have found your bug. If it returned fewer, an inner join or a WHERE clause is dropping rows you meant to keep."},"check":{"question":"A query LEFT JOINs orders to refunds and adds `WHERE r.reason <> 'damaged'`. It returns 812 rows, but the orders table has 40,000 rows and the team expected roughly that many. What has happened?","options":[{"text":"The refunds table contains duplicate rows, so the join multiplied some orders and the count is unreliable","correct":false,"tempting":"Reaches for row multiplication, the other famous join bug, which would raise the count rather than collapse it."},{"text":"A LEFT JOIN guarantees every left row survives, so the count cannot have dropped and the 40,000 figure must be wrong","correct":false,"tempting":"Trusts the LEFT JOIN's promise without noticing that WHERE runs after the join and can undo it."},{"text":"For orders with no refund, r.reason is NULL, the WHERE test is not true, and those rows are discarded after the join keeps them","correct":true},{"text":"The join direction is backwards and a RIGHT JOIN from refunds to orders would restore the missing rows","correct":false,"tempting":"Treats the problem as a choice of join type when the join itself was already doing the right thing."}]},"prev":{"slug":"sql-from-an-ai","title":"Asking an AI for SQL without being lied to","url":"https://addaly.com/learn/data-and-sql/sql-from-an-ai"},"next":{"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"},"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) 'Joins: reason about them, do not memorise them', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/joins-that-multiply (Accessed: 31 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Joins: reason about them, do not memorise them","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"A join emits one row per match; zero matches and two matches are where every join bug lives.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"forget-the-venn-diagrams","runs":[{"text":"Forget the Venn diagrams"}]},{"t":"p","runs":[{"text":"The circles do not tell you what actually happens, which is this:"}]},{"t":"p","runs":[{"text":"For each row on the left, the database looks for rows on the right where the ON condition is true, and emits one output row per match.","b":true}]},{"t":"p","runs":[{"text":"Everything follows from that one sentence. In particular, two questions decide the behaviour of any join you will ever write:"}]},{"t":"ol","start":1,"items":[[{"text":"What happens to a left row with "},{"text":"zero","b":true},{"text":" matches?"}],[{"text":"What happens to a left row with "},{"text":"two","b":true},{"text":" matches?"}]]},{"t":"p","runs":[{"text":"An inner join drops the zero-match row. A left join keeps it and fills the right-hand columns with NULL. That is the entire difference between them."}]},{"t":"p","runs":[{"text":"And for two matches, both kinds of join do the same thing: they emit two rows. The left row is duplicated. This is not a bug and there is no flag to turn it off. It is what a join is."}]},{"t":"h2","id":"row-multiplication-which-is-where-the-money-goes-wrong","runs":[{"text":"Row multiplication, which is where the money goes wrong"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT c.name, c.credit_limit, o.amount\nFROM customers c\nJOIN orders o ON o.customer_id = c.id;"},{"t":"p","runs":[{"text":"If Mariana has three orders, Mariana appears three times, and her credit limit appears three times with her. Now watch this go wrong:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT SUM(c.credit_limit)\nFROM customers c\nJOIN orders o ON o.customer_id = c.id;"},{"t":"p","runs":[{"text":"The total credit limit is now roughly the number of orders times the average limit. It is enormous. It is also completely plausible-looking, which is the dangerous part. Nothing errors. You get a number."}]},{"t":"p","runs":[{"text":"The defence is a sentence you say before you write the query: "},{"text":"”one row of my result is ______.”","b":true}]},{"t":"ul","items":[[{"text":"“One row per order, with the customer’s city attached.” Each order has exactly one customer, so at most one match. Joining is safe."}],[{"text":"“One row per customer, with their total order value.” That is one row per customer, but the join produces one row per order. So the join has to be followed by a grouping, or the total belongs in a subquery."}]]},{"t":"p","runs":[{"text":"If you cannot finish that sentence, you are not ready to write the join."}]},{"t":"h2","id":"the-trap-that-turns-a-left-join-into-an-inner-join","runs":[{"text":"The trap that turns a LEFT JOIN into an inner join"}]},{"t":"p","runs":[{"text":"This is the single most common join bug in working code."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.id, r.reason\nFROM orders o\nLEFT JOIN refunds r ON r.order_id = o.id\nWHERE r.reason <> 'damaged';"},{"t":"p","runs":[{"text":"The intent is “all orders, with refund reason where there is one, excluding damage refunds.” What you get is only orders that have a refund."}]},{"t":"p","runs":[{"text":"Why: the LEFT JOIN faithfully keeps the unmatched orders and sets "},{"text":"r.reason","code":true},{"text":" to NULL for them. Then WHERE asks whether NULL is different from "},{"text":"'damaged'","code":true},{"text":", and the answer is not true: it is unknown. WHERE keeps only rows where the test is true. Every unmatched order is discarded a step after the join carefully preserved it."}]},{"t":"p","runs":[{"text":"Two fixes, and they mean different things:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- Filter the right-hand side before matching: keeps all orders,\n-- and simply never attaches a damage refund to them.\nLEFT JOIN refunds r ON r.order_id = o.id AND r.reason <> 'damaged'\n\n-- Filter after matching, but spare the unmatched rows explicitly.\nWHERE (r.reason <> 'damaged' OR r.id IS NULL)"},{"t":"p","runs":[{"text":"General rule: a condition on the right-hand table of a LEFT JOIN belongs in ON. A condition on the left-hand table belongs in WHERE."}]},{"t":"h2","id":"the-anti-join-which-is-worth-knowing-by-name","runs":[{"text":"The anti-join, which is worth knowing by name"}]},{"t":"p","runs":[{"text":"“Orders that have no refund at all”:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.id\nFROM orders o\nLEFT JOIN refunds r ON r.order_id = o.id\nWHERE r.id IS NULL;"},{"t":"p","runs":[{"text":"Join everything, then keep only the rows where the right side came back empty. Read it as “look for a match, and keep the failures.” It is the standard way to ask “which of these has none of those”, and once you have seen it you will use it constantly: students with no payment this month, products never sold, users who never opened the app."}]},{"t":"h2","id":"the-reasoning-habit","runs":[{"text":"The reasoning habit"}]},{"t":"p","runs":[{"text":"When a join surprises you, do not reach for a different join type and hope. Count. Run "},{"text":"SELECT COUNT(*)","code":true},{"text":" on the left table alone, then on the join. If the join returned more rows than the left table has, something on the right matched more than once, and you have found your bug. If it returned fewer, an inner join or a WHERE clause is dropping rows you meant to keep."}]}],"check":{"question":[{"text":"A query LEFT JOINs orders to refunds and adds "},{"text":"WHERE r.reason <> 'damaged'","code":true},{"text":". It returns 812 rows, but the orders table has 40,000 rows and the team expected roughly that many. What has happened?"}],"options":[{"text":[{"text":"The refunds table contains duplicate rows, so the join multiplied some orders and the count is unreliable"}],"correct":false,"tempting":[{"text":"Reaches for row multiplication, the other famous join bug, which would raise the count rather than collapse it."}]},{"text":[{"text":"A LEFT JOIN guarantees every left row survives, so the count cannot have dropped and the 40,000 figure must be wrong"}],"correct":false,"tempting":[{"text":"Trusts the LEFT JOIN’s promise without noticing that WHERE runs after the join and can undo it."}]},{"text":[{"text":"For orders with no refund, r.reason is NULL, the WHERE test is not true, and those rows are discarded after the join keeps them"}],"correct":true,"tempting":null},{"text":[{"text":"The join direction is backwards and a RIGHT JOIN from refunds to orders would restore the missing rows"}],"correct":false,"tempting":[{"text":"Treats the problem as a choice of join type when the join itself was already doing the right thing."}]}],"order":[3,1,0,2],"why":null},"prev":{"slug":"sql-from-an-ai","title":"Asking an AI for SQL without being lied to","minutes":8},"next":{"slug":"the-four-joins","title":"The four joins, and what each one is for","minutes":8}}}