Joins, Unions, and Data Relationships
What to know
Understand Join (inner by default), Join Multiple (for 3+ inputs, one output, configurable join type), Union (stacking rows; configure by name or position, and how mismatched fields are handled), Find Replace (append vs. replace, matching on whole field vs. substring), and Append Fields (cartesian-style cross join). Know the Join output anchors: Join (matched), Left, Right, and the Join output containing both sides' fields. Recognize key-field type mismatches and duplicate keys (many-to-many blowups).
Traps
- Join vs. Join Multiple: Regular Join takes two inputs; Join Multiple handles many and lets you choose Cartesian vs. by-record.
- Union configuration: "Auto config by name" aligns fields by name; by position aligns by column order-mixing them causes misaligned data.
- Duplicate keys: A many-to-many join multiplies rows; questions may ask why record counts exploded.
- Find Replace modes: Append adds matched fields; Replace overwrites; matching method (entire field vs. any part) changes hit counts.
- Left/Right anchors: The Left anchor holds unmatched left rows; the Right anchor holds unmatched right rows-not the matched set.
Rule of thumb
Before joining, confirm key fields share the same data type and check for duplicates with a Summarize Count. If row counts multiply unexpectedly, suspect duplicate keys or a Cartesian configuration.
Worked example
Joining Orders to Customers on CustomerID where Customers has duplicate IDs will duplicate order rows. Fix by deduplicating Customers (Unique or Summarize First) before the Join, or use Join Multiple with the appropriate aggregation.