Three outputs
Recreating SQL join types
| SQL | Alteryx |
|---|---|
| INNER | J |
| LEFT | J unioned with L |
| RIGHT | J unioned with R |
| FULL OUTER | L, J and R all unioned |
Use the Union tool to combine them.
The record count check
Before the join, note the left record count. After it, L + J should equal that number.
If J is larger than expected, the right side has duplicate keys and rows have fanned out — every measure downstream is now inflated.
Fixing a fan-out
Put a Summarize tool on the right input first, grouping by the join key so there is exactly one row per key. Then join.
This is the same fix as pre-aggregating a subquery in SQL, or groupby before merge in pandas.
Join by position
The Join tool also offers join by record position, which pairs row 1 with row 1 regardless of content. It is occasionally useful and much more often a mistake — if you did not deliberately choose it, use join by field.
Join Multiple
Join Multiple joins several inputs at once on a shared key. Convenient, but it only produces the equivalent of an inner or left join and gives no unmatched outputs, so you lose the diagnostic value. For anything where matching is uncertain, chain ordinary Join tools instead.