Matching 1,000 orders to a customer list on customer id returns 940 rows: which match shape was used, and what do the others keep?

juniormust knowfreq 80%

answer

the shape decides survivors, not partners
four shapes, named by what they keep
fewer rows out means rows were dropped
kept unpartnered rows arrive widened with absence

basics

~20 s

Losing 60 rows means the match kept only rows partnered on both sides. The three alternatives keep the orders table whole, the customer list whole, or both whole, and unpartnered rows then survive with the other side's columns holding the absent-value marker.

solid answer

~50 s

A key match pairs rows of two tables whose named key columns compare equal; the match shape is the separate choice of which unpartnered rows still survive. There are four: keep only rows that found a partner, keep the orders table whole, keep the customer list whole, or keep both whole. Here 940 came back from 1,000, so 60 orders carried a customer id with no equal value on the list and the partnered-only shape discarded them — that shape is a filter as well as a widening, and it is the only one of the four that can lose a row. Keeping the orders table whole returns all 1,000, with every customer-list column holding the absent-value marker on those 60. Keeping the customer list whole adds one row per customer who placed no order; keeping both whole does both. All of this assumes no customer id repeats on the list.

A key match pairs rows of two tables whose named key columns compare equal; the *match shape* is the separate choice of which **unpartnered** rows still survive. There are four: keep only rows that found a partner, keep the orders table whole, keep the customer list whole, or keep both whole. Here 940 came back from 1,000, so 60 orders carried a customer id with no equal value on the list and the partnered-only shape discarded them — that shape is a filter as well as a widening, and it is the only one of the four that can lose a row. Keeping the orders table whole returns all 1,000, with every customer-list column holding the absent-value marker on those 60. Keeping the customer list whole adds one row per customer who placed no order; keeping both whole does both. All of this assumes no customer id repeats on the list.

go deeper

for a junior

Know that the choice of which unpartnered rows survive is yours, and that there are four of them: keep only partnered rows, keep either one table whole, or keep both whole. Know that a smaller output than the input is normal and means rows were dropped.

for a middle

Predict the row count for each of the four shapes before running the operation, and explain that kept unpartnered rows come back widened with the absent-value marker in every column the other table contributed.

for a senior

Treat the partnered-only shape as an undeclared filter in a pipeline: say which downstream totals silently became totals over matched rows only, and never rely on output row order, which is unspecified in some designs.

for a principal

The question is which shape a pipeline defaults to and what it then does with unpartnered rows — drop them, keep and mark them, or stop. Each pushes cost somewhere different, and the choice should be written at the seam, not inferred later.

Two different things decide the output

A key match pairs rows of two tables whose named key columns compare equal — here, a customer identifier carried by both the orders table and the customer list. Two independent things decide what comes back, and conflating them is the commonest confusion on this subject:

The data decides which rows find a partner, and how many partners each one finds.
The match shape decides what happens to the rows that found none.

Only the second is a choice you make at the call. The shape is not a performance setting and it says nothing about how the partners are located; it is a statement about which unpartnered rows you still want in the answer.

The four shapes, named by what they keep

Shape	Unpartnered rows that survive	Rows back in this example
Keep only partnered rows	none	940
Keep the orders table whole	every order	1,000
Keep the customer list whole	every customer	940 plus one per customer with no order
Keep both tables whole	both sides	1,000 plus one per customer with no order

Those counts assume each customer identifier appears at most once on the customer list. If a key value can occur more than once on a side, the partnered rows are no longer one output row per order and the arithmetic changes — which is the first thing to rule out before reading a row count as evidence about the shape.

Reading the 940

940 out of 1,000 says that 60 order rows carried a customer identifier with no equal value on the customer list, and that the shape in force discarded them. Nothing raised and nothing warned. Keeping only partnered rows is a filter as well as a widening, and that is the whole lesson of the number. Two things follow immediately, without looking at any data:

Every downstream total is now a total over matched orders rather than over orders. A revenue figure computed after this match is short by whatever those 60 rows were worth, and the report will not say so.
The 60 are not a random sample. Rows fail to find a partner for reasons — a customer created after the reference extract was taken, an internal test account, a stale copy of the list — and those reasons are usually correlated with something the report is about.

What the unpartnered rows look like when you keep them

Choose one of the whole-keeping shapes and the 60 rows come back, but they come back widened: every column contributed by the customer list holds the absent-value marker, the tool's representation of a value that is not there. Those cells were manufactured by the match itself; the rows were complete before it ran. That is the trade the four shapes offer you. The partnered-only shape hides the problem by removing the evidence; the whole-keeping shapes surface the problem as absence that every later step now has to mean something by.

Some tools can also attach a provenance marker — an extra column recording which side each output row came from — which is what turns "this cell is empty" into "this row never found a partner".

Where designs in this family differ

Part of answering well is saying which of your beliefs are properties of the operation and which are properties of the tool in front of you:

The row arithmetic is universal. Which rows survive under each shape is the same everywhere; that part is not a tool question.
Output row order is not universal. Some designs document an order for particular shapes, typically preserving one side's order when that side is being kept whole; others leave it unspecified, and the same tool can behave differently between shapes. If order matters downstream, put the rows in an order yourself after the match.
Absent values inside the key column are not universal. In some designs the absent marker is just another comparable value, so rows with an absent key pair with each other; in others absent never compares equal, so those rows fall out of a partnered-only match entirely. Decide what an absent key means and remove those rows deliberately before the match rather than discovering which rule your tool follows.
The spelling is not universal. Every tool names these four shapes differently, and in some the whole-keeping shapes are separate operations rather than an argument to one.

The habit to demonstrate

Before running the match, say what the result is one row per: one row per order, one row per customer, or one row per order-and-customer pairing. Pick the shape that preserves that grain. Then compare the row count you got with the count you predicted, and treat any difference as a finding rather than as noise. A candidate who can name four shapes has recited something; a candidate who predicted 1,000 and immediately asked about the missing 60 has done the job.

## Two different things decide the output A **key match** pairs rows of two tables whose named key columns compare equal — here, a customer identifier carried by both the orders table and the customer list. Two independent things decide what comes back, and conflating them is the commonest confusion on this subject: - **The data** decides which rows find a partner, and how many partners each one finds. - **The match shape** decides what happens to the rows that found none. Only the second is a choice you make at the call. The shape is not a performance setting and it says nothing about how the partners are located; it is a statement about which unpartnered rows you still want in the answer. ## The four shapes, named by what they keep | Shape | Unpartnered rows that survive | Rows back in this example | |---|---|---| | Keep only partnered rows | none | 940 | | Keep the orders table whole | every order | 1,000 | | Keep the customer list whole | every customer | 940 plus one per customer with no order | | Keep both tables whole | both sides | 1,000 plus one per customer with no order | Those counts assume each customer identifier appears at most once on the customer list. If a key value can occur more than once on a side, the partnered rows are no longer one output row per order and the arithmetic changes — which is the first thing to rule out before reading a row count as evidence about the shape. ## Reading the 940 940 out of 1,000 says that 60 order rows carried a customer identifier with no equal value on the customer list, and that the shape in force discarded them. Nothing raised and nothing warned. **Keeping only partnered rows is a filter as well as a widening**, and that is the whole lesson of the number. Two things follow immediately, without looking at any data: 1. Every downstream total is now a total over *matched* orders rather than over orders. A revenue figure computed after this match is short by whatever those 60 rows were worth, and the report will not say so. 2. The 60 are not a random sample. Rows fail to find a partner for reasons — a customer created after the reference extract was taken, an internal test account, a stale copy of the list — and those reasons are usually correlated with something the report is about. ## What the unpartnered rows look like when you keep them Choose one of the whole-keeping shapes and the 60 rows come back, but they come back *widened*: every column contributed by the customer list holds the **absent-value marker**, the tool's representation of a value that is not there. Those cells were manufactured by the match itself; the rows were complete before it ran. That is the trade the four shapes offer you. The partnered-only shape hides the problem by removing the evidence; the whole-keeping shapes surface the problem as absence that every later step now has to mean something by. Some tools can also attach a **provenance marker** — an extra column recording which side each output row came from — which is what turns "this cell is empty" into "this row never found a partner". ## Where designs in this family differ Part of answering well is saying which of your beliefs are properties of the operation and which are properties of the tool in front of you: - **The row arithmetic is universal.** Which rows survive under each shape is the same everywhere; that part is not a tool question. - **Output row order is not universal.** Some designs document an order for particular shapes, typically preserving one side's order when that side is being kept whole; others leave it unspecified, and the same tool can behave differently between shapes. If order matters downstream, put the rows in an order yourself after the match. - **Absent values inside the key column are not universal.** In some designs the absent marker is just another comparable value, so rows with an absent key pair with each other; in others absent never compares equal, so those rows fall out of a partnered-only match entirely. Decide what an absent key means and remove those rows deliberately before the match rather than discovering which rule your tool follows. - **The spelling is not universal.** Every tool names these four shapes differently, and in some the whole-keeping shapes are separate operations rather than an argument to one. ## The habit to demonstrate Before running the match, say what the result is one row per: one row per order, one row per customer, or one row per order-and-customer pairing. Pick the shape that preserves that grain. Then compare the row count you got with the count you predicted, and treat any difference as a finding rather than as noise. A candidate who can name four shapes has recited something; a candidate who predicted 1,000 and immediately asked about the missing 60 has done the job.

The output came back with 1,000 rows. Does that prove every order found a partner?
No. Under a shape that keeps the orders table whole, 1,000 is what you get whether every order matched or none did — the unpartnered ones are present but widened with the absent-value marker. Even under the partnered-only shape, 1,000 is only reassuring once you know no key value repeats on the other side. Check how many order rows carry the absent marker in the columns the other table contributed, not the total.
Can you rely on the rows coming back in the order of the orders table?
No. Some designs document an output order for particular shapes and commonly preserve the order of a side being kept whole; others state the order is unspecified, and one tool can differ between shapes. Treat output order as undefined and put the rows in an order explicitly after the match if anything downstream depends on it.
What does keeping the customer list whole give you that keeping the orders table whole does not?
It surfaces the opposite gap: customers with no order at all. Those rows come back with every order-side column holding the absent-value marker, and counting them answers a different question — coverage of the reference table — rather than completeness of the transactions. Keeping both whole answers both at once, at the price of an output where absence can have come from either direction.

An invitation list and a pile of replies, paired on name. Keep only the people on both and you have your confirmed guests — and you have quietly lost every invitee who never replied. Keep the invitation list whole instead and those people are still on the sheet with the reply columns blank. The blanks are not missing facts about them; they are the shape of the match telling you no reply arrived.

saying these in an interview costs you the question

Thinks a match can only ever shrink a table, never keep unpartnered rows
Reads 940 as a data error rather than the chosen shape doing its job
Assumes rows always come back in the order of the first table
Treats keeping a side whole as free, forgetting it manufactures absent-marker cells
Believes the shape also decides how many partners a row may have

Matching 1,000 orders to a customer list on customer id returns 940 rows: which match shape was used, and what do the others keep?

§ answer

basics

solid answer

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two different things decide the output

The four shapes, named by what they keep

Reading the 940

What the unpartnered rows look like when you keep them

Where designs in this family differ

The habit to demonstrate

▾common follow-ups3 questions

▾analogy~30 s

▾red flags5 flags

Two different things decide the output

The four shapes, named by what they keep

Reading the 940

What the unpartnered rows look like when you keep them

Where designs in this family differ

The habit to demonstrate

answer

go deeper