Join node

Transform

What it does

The Join node combines rows from two upstream streams that match on a key, with the same meaning as a SQL join. Keys can be a plain field or an expression such as LOWER(email), and several key pairs make a composite key.

When the two sides come from different Sources, the Join waits until every Source has finished, then joins once.

When to use

• Two streams share a key and you need the matching rows side by side, such as customers and their orders.
• You need the rows that did not match too (left, right or full outer).
• The key needs preparing first, such as LOWER(email) on one side and UPPER(contact_email) on the other.

To stack two streams without matching, use a Union.

Configure

Connect the two upstreams to the left and right ports. Which side is which matters for left and right joins. Then select the node and press Configure in the side panel. The side panel shows the join type, the key and the build-side settings.

Join type

Type	Rows out
Inner (default)	Only rows that matched on both sides.
Left	Every left row. Right columns are null where nothing matched.
Right	Every right row. Left columns are null where nothing matched.
Full outer	Every row from both sides.

Left and right joins keep every row of their side, and a full outer join every row of both, including rows whose key is empty. Those rows never match, so the other side's columns are null.

Key expressions

One pair per key part, Left key and Right key. The names do not have to be the same. Add key adds a pair. Pick a field, or press fx to write an expression with the function list, the side's fields and live checking. A row matches only when every pair agrees.

A key is required. Save and Done stay unavailable until at least one pair has both a left and a right key, and every pair you fill needs both sides. Save keeps the dialog open, Done saves and closes it, and Cancel discards the changes since the last save. A Join saved without a key shows Not set up on its card, and a run does not start until the key is set. A message confirms each save.

• A key part that is null, empty text or a column the row does not have never matches. In a composite key, one such part stops the whole row matching.
• Text keys are compared without leading and trailing spaces.
• Matching is by value as text: the number 1 matches the text "1", and true matches "true". Case still counts.

Output fields

• Leave the table empty to output every column from both sides. Every row has the same columns in the same order: the left side's columns first, then the columns only the right side has. A column with no value in a row is null.
• Where both sides have a column of the same name, the key column included, the right side's value is kept unless it is null. Then the left side's value is kept.
• To choose columns, tick them in the table. Rename to gives a column a new name. When upstream columns are known, every column is ticked to start with.
• Computed fields: extra columns, each a Column name and an expression that can read both sides. Where both sides have a column of the same name, prefix it: CONCAT(left_firstName, ' ', right_lastName). A plain name follows the same right-unless-null rule. A column whose real name starts with left_ or right_ is read as that column. Computed columns come last and replace a plain column of the same name.

Build side: Auto (smaller side) (default), Left or Right. The build side is held in memory while the other side is matched against it. Choose a side only when you know it is the smaller one.

Large joins are processed on disk: a build side over the Build-side row limit, and a left, right or full outer join with more than 500,000 rows across both sides. The rows do not change. The run log says when a join is processed on disk.

Row order is not guaranteed. To sort the output, add a Compute node with Derived Columns + Sort after the Join.

Memory guard: Build-side row limit, default 1,000,000. Past it the join is processed on disk. Where that is not possible, a build side over the limit fails the node with a message naming the side and its row count.

Ports

Port	Direction	Kind	Cardinality	Notes
left	in	data	one	The left upstream.
right	in	data	one	The right upstream.
out	out	data	one	The joined rows.
error	out	control	one	Receives the node's input rows when the node fails, each with the error message in dl_error.

Examples

Attach orders to customers from two systems.

Start

CRM contacts

DB customers

Combine

Orders

Attach orders

Map columns

Load

Inner join on customerId = customerId. With customers C1, C2 and C3 on the left and orders for C1 and C3 on the right, two rows leave the Join. C2 has no order and is dropped. With Left, C2 comes through with empty order columns.

Match on email, ignoring case. Left key LOWER(email), right key LOWER(contact_email).

Troubleshooting

Symptom	Cause	What to do
Save is unavailable in Configure Join	A key pair is missing a side, or there is no key yet.	Choose a left and a right key in every pair, or remove the incomplete pair.
The node fails with Join has no complete key pair.	The saved Join has no key pair.	Open Configure Join and choose a left and a right key.
The node fails with Join key pair is missing a left or right key.	A saved key pair has only one side.	Complete the pair in Configure Join, or remove it.
The node fails with Join type is not one of inner, left, right, fullOuter.	The saved join type is not one of the four types.	Open Configure Join and choose a Join type.
No rows, or fewer than expected	The key values differ in case.	Wrap both keys in LOWER(...) with fx.
Rows with an empty key are missing from an inner join	An empty key never matches.	Use a left, right or full outer join to keep them.
A left value is replaced by the right one	Both sides have a column of that name, and the right value is kept where it is not null.	Use Output fields with Rename to, or a computed field with left_ or right_.
Rows reach error and dl_error names the build-side row limit	The build side is over the Build-side row limit and the join could not be processed on disk.	Filter the input first, switch Build side, or raise the limit.
Rows arrive in a different order between runs	The Join does not guarantee row order.	Sort after the Join with a Compute node.
A computed field is empty	The expression names a column neither side has.	Check the field list in the fx editor.
The fx preview is blank but the run works	The preview has no sample rows on a Join.	Check the result in a run.

Related nodes

Union

Stack two streams without matching

Lookup

Enrich rows from a second connection

Filter

Drop rows before or after the join