A match of 1,000 order rows against a customer reference table returns 1,340 rows - how is that possible?
answer
- more rows out than either input had
- one order, two partners, two rows
- per key value, the two counts multiply
- row count against distinct key count
basics
~20 sA key match returns, for each key value, the first side's occurrence count multiplied by the second side's. If a customer identifier appears more than once in the reference table, the orders carrying it are copied and the total grows.
solid answer
~50 sYes, it is possible, and it is the ordinary behaviour of a **key match** - pairing rows of two tables whose named key columns compare equal. For one key value, the output holds the first side's occurrence count multiplied by the second side's: one order against one customer row gives one output row, one order against two customer rows gives two. So 1,340 rows says some customer identifiers appear more than once in the reference table, and the orders carrying them were copied. Those extra rows are not new data - they are the same order repeated - so every sum over the result is now inflated while a count of distinct orders is unchanged. Before reading a single row I would compare each side's row count with its number of distinct key values; the side where those differ is the one doing the multiplying.
go deeper
Recall that a match returns every pair of equal-keyed rows, not one answer per lookup, so the output can be longer than either input. Saying out loud that this is possible already puts you ahead of most first-screen candidates.
Explain the arithmetic rather than the symptom: for one key value the output holds the product of the two sides' occurrence counts, and the whole output is that product summed over the shared key values.
Show the diagnosis you would run without looking at rows: row count against distinct key count on each side, and a total that should not have changed measured before and after the step.
Frame it as an assumption with no owner. Someone believed the reference held one row per customer, nothing recorded that belief, and the number reached a report before anyone tested it.
## The operation is not a lookup A **key match** pairs rows of two tables whose named key columns compare equal: you say which column, on each side, the comparison is made against, and the operation returns one output row for every pair of rows that compares equal. Most people arrive with a spreadsheet model in mind - *look this identifier up and bring back the customer's name* - and a lookup returns at most one answer. A match returns **every** pair. That difference is the whole of the 1,340. Both sides here are **labelled tables**: rectangles of named columns that, in some tools, also carry an identifier for each row. Those row labels play no part in what follows - this is arithmetic over the values in the key columns and nothing else. ## The arithmetic, one key value at a time Take a single key value and ignore the rest of the data. If it occurs `m` times in the order table and `n` times in the customer reference table, the match emits `m * n` rows for that value. The output row count of the whole match is that product summed over every key value present on both sides - the **per-key row product**: ``` output rows = sum over key values k of (occurrences of k in orders) * (occurrences of k in customers) ``` Three familiar relationships are the same formula with different numbers: | relationship | occurrences per key | rows out for that key | how it feels | |---|---|---|---| | one-to-one | 1 and 1 | 1 | a lookup; the row count holds | | one-to-many | 1 and n | n | an expansion, usually intended | | many-to-many | m and n | m * n | an expansion nobody intended | Notice what the table does **not** mention: size, cost, or how the pairs were found. The output count is a property of the values in the key columns. ## Reading the number 1,340 - The result is 340 rows longer than the order table, so some orders found more than one partner in the reference. - It is consistent with 340 orders having two matching customer rows each, or 170 having three, or a single order matching 341. The total alone does not tell them apart. - The extra rows are **copies**: the same order identifier, the same amount, repeated. A sum over the amount column now counts those orders twice or more, while a count of distinct order identifiers is unchanged. **That gap between the two numbers is the tell.** - The number is also a *net*. If the shape you chose keeps only rows that found a partner, unpartnered orders fall out in the same step in which other orders are copied. A result of exactly 1,000 rows would therefore not prove that nothing multiplied. ## Why nothing raised an error There is nothing ambiguous here for a tool to complain about. A key value occurring twice on one side is legal data, every pair it forms compares equal, and every such pair is a correct output row by the definition of the operation. The row arithmetic above is universal. **What differs between tools is whether anyone tells you.** Some return the inflated table in complete silence; some warn once they notice the match was many-to-many; some accept a declaration on the call of how many partners each row is allowed, and fail the call instead of returning a wrong-sized table. Write as though you will not be told, and your code is correct under all three. ## The three numbers to look at, in order 1. **Per side, the row count against the number of distinct key values.** Equal means that side is unique on the key and cannot multiply anything. Different on both sides means the match is many-to-many and the product is unavoidable. 2. **The output row count against the number you predicted before running it.** Predicting nothing makes surprise impossible, which is how this ships. 3. **One total that must not have changed** - the number of distinct orders, or a revenue sum - measured before and after. A sum that grew while a distinct count held steady is the signature of copied rows. ## The repair is upstream of the match - Decide which side is supposed to be unique on the key. Matching an order stream to a customer reference, it is the reference. - Reduce that side to one row per key value on purpose, by a rule you write down, then check it: its row count must equal its distinct key count. - Where the repeats on the other side are real - three shipments recorded against one order - the expansion is correct and the thing to fix is the downstream total, not the match. - Re-measure after the repair instead of assuming it worked.
- If the match had returned exactly 1,000 rows, would that prove no key value multiplied?No. Under a shape that keeps only partnered rows, copies and drops happen in the same step and can cancel in the total: 50 orders with no partner disappearing while 50 others are paired twice leaves the count unchanged. The count is a net, so confirm it with a distinct count of orders before and after, not with the total alone.
- The reference table genuinely holds two rows for some customers. What is the smallest change that fixes the total?Reduce the reference to one row per key value before the match, by a rule you state explicitly rather than whatever the previous step left behind, and assert its row count equals its distinct key count. If both rows are genuinely needed, keep the expansion and fix the downstream sum instead - the match is then correct and the aggregation is what is wrong.
- Which side should you suspect first when the output is longer than both inputs?Neither by reputation - measure both. Compare each side's row count with its distinct key count; a side where they are equal is unique on the key and cannot multiply anything. When both differ, the match is many-to-many and the product is coming from both sides at once.
A guest list matched to a seating chart. If one guest's name was typed onto the chart twice, that single guest is booked into two seats: the head count you are billed for rises, but no extra guest walked through the door.
saying these in an interview costs you the question
- Says a match can never return more rows than the larger input
- Assumes a reference table is unique on its key because of what it is called
- Reads the extra rows as extra customers rather than repeated orders
- Checks only that the result is non-empty, never its row count
- Blames the choice of which unpartnered rows survive rather than a repeated key value