Data Blending, Joins, and Unions
Join vs. Union vs. Append
Join combines columns horizontally on matching keys. Union stacks rows vertically and matches fields by name (or by position if configured), filling missing fields with nulls. Append adds fields from a second stream to every row of the first without a key. Choosing the wrong one is the most common blending trap.
Join Outputs and Types
A Join tool emits J (matched), L (left unmatched), and R (right unmatched). An inner join uses only J; a left join is J plus L; a full outer join is all three. Duplicate keys multiply rows - a one-to-many join inflates counts, which questions test by asking why totals doubled.
Join vs. Find Replace vs. Fuzzy Match
Find Replace looks up a value without requiring the full key structure. Fuzzy Match scores string similarity for messy keys (names, addresses). Join requires exact matches by default; case sensitivity and whitespace differences break matches, so standardize keys first.
Union Configuration
Union can match by field name (safe) or by position (fast but fragile if schemas differ). It also lets you choose whether to output all fields or only common ones.
Worked example: Two files, same columns, different months: Union. Two files, one with IDs and one with names keyed on ID: Join. Rule of thumb: same fields, more rows → Union; different fields, same rows → Join.