Ciaren

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.

Left input
  1. Transform
    Left input
    orders aggregated by customer_id
Right input
  1. Input
    Right input
    customers.csv
  1. Join
    Join
    on: customer_id · how: left
  2. Transform
    Enriched result
    transactions + customer name/country
Join (on=customer_id, how=left)
Before
customer_idtotal_spent
1100
2500
315
3 rows · 2 cols
After
customer_idtotal_spentnamenewcountrynew
1100AdaUK
2500GraceUS
315LinusFI
3 rows · 4 cols

Configuration

Config keyTypeRequiredDescription
onstring | string[]ConditionalKey(s) present in both frames
left_on / right_onstring | string[]ConditionalUse when key names differ (supply both)
howstringNoinner (default), left, right, outer, semi, anti
suffixes[string, string]NoSuffixes 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

python
df_3 = df_1.merge(df_2, on='customer_id', how='left')

Polars

python
df_3 = df_1.join(df_2, on='customer_id', how='left', suffix='_y', coalesce=True)

Tips & common mistakes

  • how controls which rows survive: inner keeps matches only; left/right keep all rows from one side; outer keeps everything (unmatched cells become null); semi keeps matching left rows; and anti keeps 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/_y columns.
  • 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.

See also