Join — join
Combine two inputs (left, right) on a key. Join is one of two multi-input
nodes — connect one upstream node to its left handle and another to its
right handle.
Use cases
- Enrich transactions with customer attributes.
- Look up reference data (region names, prices) by key.
- Keep rows whose key exists in another table with a semi join.
- Find rows whose key is missing from another table with an anti join.
What it does
Join combines the left and right inputs on a shared key. The how parameter
controls which rows survive — inner keeps only matches, left keeps all rows from
the left input (filling nulls for unmatched right-side columns), semi keeps left
rows with a match, and anti keeps left rows without a match. Semi and anti joins
return only the left input's columns.
- TransformLeft inputorders aggregated by customer_id
- InputRight inputcustomers.csv
- JoinJoinon: customer_id · how: left
- TransformEnriched resulttransactions + customer name/
country
| customer_id | total_spent |
|---|---|
| 1 | 100 |
| 2 | 500 |
| 3 | 15 |
| customer_id | total_spent | namenew | countrynew |
|---|---|---|---|
| 1 | 100 | Ada | UK |
| 2 | 500 | Grace | US |
| 3 | 15 | Linus | FI |
Configuration
| Config key | Type | Required | Description |
|---|---|---|---|
on | string | string[] | Conditional | Key(s) present in both frames |
left_on / right_on | string | string[] | Conditional | Use when key names differ (supply both) |
how | string | No | inner (default), left, right, outer, semi, anti |
suffixes | [string, string] | No | Suffixes for overlapping columns (default _x, _y) |
Provide either on (same key name on both sides) or both left_on and
right_on (different names).
Generated Python code
These exports left-join df_1 and df_2 on customer_id, preserving unmatched left
rows and expanding duplicate right keys. pandas matches null keys in this join; Polars
leaves them unmatched. Ciaren explicitly enables null matching for Polars semi/anti
joins. Input names come from the two upstream exports.
pandas
df_3 = df_1.merge(df_2, on='customer_id', how='left')Polars
df_3 = df_1.join(df_2, on='customer_id', how='left', suffix='_y', coalesce=True)Tips & common mistakes
howcontrols which rows survive:innerkeeps matches only;left/rightkeep all rows from one side;outerkeeps everything (unmatched cells become null);semikeeps matching left rows; andantikeeps non-matching left rows.- Semi and anti joins return exactly the left columns. Duplicate keys on the right do not duplicate left rows.
- Null keys match null keys in semi and anti joins. Ciaren enables this explicitly in Polars so pandas and Polars produce the same rows.
- Overlapping non-key columns get
suffixes. Rename or drop them upstream to avoid_x/_ycolumns. - pandas applies both configured suffixes to overlapping columns; Polars keeps
left names and applies only the right suffix (
suffixes[1]). - A shared-key (
on=) outer join produces a single key column (the keys are coalesced), matching pandas — verified across both engines. - Join takes two inputs at a time; chain join nodes to combine three or more.