Keys, inner vs left, unmatched rows

What the lookup was doing all along

Lesson 6.3 filled a price column by looking each SKU up. Seen from the database side, that operation has a name: a join. Two tables share a key — sku — and the join pairs every lines row with the products row that has the same key. The schema figure shows it: sku is the primary key of products and a foreign key in lines. Naming it matters because the join has variants, and they differ in exactly the rows a VLOOKUP user never sees.

Worked example — merge(), twice

R joins two data frames with merge():

> merge(lines, products, by = "sku")
   sku line_id qty       product unit_price
1 A100       1   2 Ledger binder         24
2 A100       4   5 Ledger binder         24
3 B200       3   1     Desk lamp         60
4 C300       2   4  Office chair        180
> nrow(merge(lines, products, by = "sku"))
[1] 4

by names the key column. The result has four rows: the D400 line is gone, because merge()'s default is an inner join — keep only rows whose key appears in both tables (all = FALSE). Two other things to notice: the key comes out as the first column, and the rows are sorted by the key, not in lines' original order. Now the other variant:

> merge(lines, products, by = "sku", all.x = TRUE)
   sku line_id qty       product unit_price
1 A100       1   2 Ledger binder         24
2 A100       4   5 Ledger binder         24
3 B200       3   1     Desk lamp         60
4 C300       2   4  Office chair        180
5 D400       5   3          <NA>         NA

all.x = TRUE keeps every row of the first (left) table and fills the unmatched one with NA: a left join, five rows. This is exactly what VLOOKUP did — every line kept, #N/A where the product was missing. all.y = TRUE is the right join (every product, even those never ordered) and all = TRUE the full join.

Which to use is a business question. Pricing an invoice: left, because a line with no price is a problem to see, not a row to drop. Counting revenue by product: inner is fine — but the row count tells you whether anything was silently lost. Check nrow() before and after every merge.

Two ways a join goes wrong

More rows than you started with. Add a second A100 row to products (an old price left in the master list) and the inner merge returns 6 rows: each A100 line now pairs with two product rows. Duplicate keys on the lookup side multiply rows; the fix is to dedupe products on sku first (Lesson 6.7). VLOOKUP hides this by always returning the first match.

Keys that look equal and are not. VLOOKUP treats the text "1001" and the number 1001 as different values, so a text-formatted ID column against a numeric one returns #N/A down the page. R is more forgiving: merge() and == coerce the number to text, so "1001" and 1001 do match — but nothing rescues leading zeros. A postal-code-style key "00123" never matches 123 in any tool, because the text has five characters and the number has three digits. When a join loses rows it should have matched, check the key columns' types and the zeros first.

Type both, then fill the row-count worksheet

Type the two merge calls. The worksheet asks for the row counts after each.

CRISP-DM: joining two tables is data preparation → integrate data.