Join node
Join
Combine two inputs on matching keys, returning the matched rows plus the unmatched orphans from both sides.
What it does
Combines two inputs, Left and Right, into THREE outputs at once: Join (rows that matched, with the Left and Right columns side by side), Left (Left rows with no match), and Right (Right rows with no match). Match on one or more key fields, or by record position. Keeping the unmatched orphans is what lets you audit a join instead of silently losing rows.
Inputs and outputs
| Inputs | Left, Right. |
| Outputs | Left, Join, Right. |
When to use it
- Enrich a table by matching it to another on a key (orders ↔ customers)
- Find the rows that DIDN'T match: the Left / Right anchors are your orphan sets
- Rebuild any join downstream: LEFT OUTER = Join + Left, FULL = Join + Left + Right (add a Union)
Examples
Inner join on a key
Left.customer_id = Right.customer_id
Matched rows go to Join; unmatched Left rows to Left, unmatched Right rows to Right.
Find unmatched orders
Join orders (Left) to customers (Right) on customer_id
The Left output is the orders with no customer: a ready-made data-quality report.
By record position
Pair Left row 1 with Right row 1, row 2 with row 2…
Surplus rows on the longer side fall out the Left or Right anchor.
Tips
- Three outputs: Join (matches), Left (Left-only), Right (Right-only). Wire the anchors you need; the others are still produced.
- Null keys never match (a null equals nothing), so null-keyed rows fall to the Left/Right anchors.
- Turn on Case-insensitive text keys to match Apple with apple; the joined rows keep their original casing.
- The output-field grid shapes ONLY the Join output: keep/drop, rename, reorder, retype. The Left and Right outputs always keep their original columns; a colliding Right column name gets a suffix (default _right).
- Output rows keep their input order, so results stay stable and diff-friendly.