Why can an INNER JOIN return more rows than either of the joined tables contains?
answer
- Rows are produced per pair, not per row
- Count matches per key value, then add up
- Two on the left, three on the right
- Uniqueness of the key decides everything
- m times n for a many-to-many key
basics
~20 sA join emits one row per qualifying pair, not per input row. If a key value appears twice on the left and three times on the right, those rows alone produce six output rows, so the result can far exceed both inputs.
solid answer
~50 sThe join result is built from **pairs**: every combination of a left row and a right row whose ON predicate is TRUE becomes its own output row. So the output size is driven by the *matching cardinality* of the join key, not by the size of either table. A one-to-one key gives at most one row per input row. A one-to-many key (order to its line items) repeats the one side once per match. A many-to-many key — the value appears m times on the left and n times on the right — yields m × n rows for that value. Nothing deduplicates: the join does not apply DISTINCT and does not stop at the first match. Before writing a join, check whether the key is unique on each side; that is what tells you the shape of the output. An inner join can only be guaranteed not to grow the left table when the join key is unique on the right.
code
sql · 5 lines-- a: two rows with k = 'X' b: three rows with k = 'X'
SELECT a.k, a.id AS a_id, b.id AS b_id
FROM a
INNER JOIN b ON a.k = b.k;
-- 6 rows: every a row paired with every b row sharing the keygo deeper
Be able to count the rows for a small example by hand: two matching rows on one side and three on the other give six. Know that the join does not remove duplicates.
Explain the one-to-one, one-to-many and many-to-many cases and name the exact condition — uniqueness of the key on the other side — that bounds the output size.
Demonstrate that you verify the join's cardinality before trusting a result, and that you fix an accidental multiplication at the predicate or by pre-collapsing the many side rather than papering over it with DISTINCT.
Own the modelling angle: a many-to-many join on a shared parent id usually signals a missing relationship or a missing bridge table, and repeated incidents point at conventions the team should encode in review.
## The rule that produces the rows An inner join emits one output row per qualifying **pair** of rows. That single sentence explains both directions of surprise: pairs that do not qualify remove rows, and a row that qualifies with several partners contributes several rows. The join never deduplicates and never stops after the first match — SQL works on multisets, so identical rows are perfectly legal in a result. ## Multiplication, concretely ```sql -- a has two rows with k = 'X' -- b has three rows with k = 'X' SELECT a.k FROM a INNER JOIN b ON a.k = b.k; -- 6 rows ``` Every one of the two left rows pairs with every one of the three right rows: 2 × 3 = 6. Do this per distinct key value and sum, and you have the exact output size. If `a` has 2 rows for `'X'` and 1 for `'Y'`, and `b` has 3 for `'X'` and 0 for `'Y'`, the result is 2×3 + 1×0 = 6 rows — bigger than `a`, bigger than `b`, and missing `'Y'` entirely. ## The three shapes of a join key **One-to-one.** The key is unique on both sides — typically a primary key joined to a unique column. Each row matches at most one partner; the result cannot exceed either input. **One-to-many.** The key is unique on one side only. This is the everyday foreign-key case: `orders` to `order_items`. The order's columns are repeated once for every item it has. This is expected and usually harmless as long as you *know* it: the order data is now duplicated across rows. ```sql SELECT o.id, o.customer_id, i.sku, i.qty FROM orders o INNER JOIN order_items i ON i.order_id = o.id; -- an order with 4 items occupies 4 rows; o.customer_id repeats 4 times ``` **Many-to-many.** The key is unique on neither side. This is where results explode. Joining two tables on `customer_id` when both hold multiple rows per customer — say `orders` and `support_tickets` — pairs every order with every ticket for that customer. Ten orders and ten tickets become one hundred rows, and every one of them is a meaningless pairing: nothing connects order #3 to ticket #7 except that they share a customer. That is a modelling bug, not just a size problem. ## How to know the shape before you run it Read the constraints, or check the data: ```sql -- is the join key unique on this side? SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) > 1; ``` If a primary key or unique constraint covers the join key on a side, that side contributes at most one match. If not, assume it can multiply. The guarantee worth remembering: **an inner join returns at most one row per left row exactly when the join key is unique on the right side.** ## Why this matters more than it looks Duplicated rows are the input to whatever comes next in the query, and downstream operators cannot tell an intentional duplicate from an accidental one. Row counts stop matching the source table. Anything you compute over the joined rows is now computed over the multiplied set. Interviewers ask this question because it is the root cause of a large fraction of "the numbers are wrong" bugs. ## Fixing an unintended multiplication The honest fixes attack the cause, not the symptom: - **Join on a key that is actually unique on one side.** Usually the join predicate was under-specified: two columns identify the match, not one, so add the missing condition to the ON clause. - **Reduce the many side to one row per key first**, in a subquery or CTE, then join to that pre-collapsed result. Now the join is one-to-one and cannot multiply. - **Ask whether you needed the rows at all.** If the join exists only to test "does a related row exist", a join is the wrong tool — an existence test does not multiply, because it never emits the partner rows. Slapping `DISTINCT` on the select list is the tempting fix and usually the wrong one: it hides the duplication instead of explaining it, it silently removes rows that were legitimately identical in the source data, and it does nothing when the duplicated rows differ in some column. ## The short version for an interview "One row per matching pair. Unique key on the right means at most one row per left row; duplicate keys on both sides multiply. I check the key's uniqueness on each side before I trust the row count."
- What single property guarantees the join cannot return more rows than the left table?The join key must be unique on the right side — enforced by a primary key or a unique constraint over exactly the ON columns. Then each left row has at most one partner, so the result is bounded by the left table's row count. Without that guarantee, assume the join can multiply.
- Is adding DISTINCT a good fix for a join that multiplied rows?Rarely. It masks the cause, and it only works when the duplicated rows are identical across every selected column — add one more column and the duplicates reappear. It also destroys legitimate duplicates from the source. Fix the ON predicate, or collapse the many side to one row per key before joining.
- How do you spot an accidental many-to-many join in a query you are reviewing?Look at the ON columns and ask whether either side is unique on them. Joining two detail tables on a shared parent id — two tables that each hold many rows per customer, joined on customer_id — is the classic tell. The pairings it produces are arbitrary and the row count is the product.
saying these in an interview costs you the question
- Thinks the join automatically deduplicates the result
- Says an inner join returns at most one row per left row
- Assumes the join stops at the first match found
- Reaches for DISTINCT instead of fixing the predicate
- Believes the output can never exceed the larger table