polars.LazyFrame.join#

Add a join operation to the Logical Plan.

Changed in version 1.24: The join_nulls parameter was renamed nulls_equal.

Parameters:

other

Lazy DataFrame to join with.

on

Name(s) of the join columns in both DataFrames. If set, left_on and right_on should be None. This should not be specified if how='cross'.

how{‘inner’,’left’, ‘right’, ‘full’, ‘semi’, ‘anti’, ‘cross’}

Join strategy.

inner	(Default) Returns rows that have matching values in both tables.
left	Returns all rows from the left table, and the matched rows from the right table.
right	Returns all rows from the right table, and the matched rows from the left table.
full	Returns all rows from both tables, joining matching rows and filling non-matches with null values.
cross	Returns the Cartesian product of rows from both tables
semi	Returns rows from the left table that have a match in the right table. Does not return columns from the right table.
anti	Returns rows from the left table that have no match in the right table. Does not return columns from the right table.

left_on

Join column of the left DataFrame.

right_on

Join column of the right DataFrame.

suffix

Suffix to append to columns with a duplicate name.

validate: {‘m:m’, ‘m:1’, ‘1:m’, ‘1:1’}

Checks if join is of specified type.

m:m	(Default) Many-to-many. Does not result in checks.
1:1	One-to-one. Checks if join keys are unique in both left and right datasets.
1:m	One-to-many. Checks if join keys are unique in left dataset.
m:1	Many-to-one. Check if join keys are unique in right dataset.

nulls_equal

Join on null values. By default null values will never produce matches.

coalesce

Coalescing behavior (merging of join columns).

None	(Default) Coalesce unless `how='full'` is specified.
True	Always coalesce join columns.
False	Never coalesce join columns.

Note

Joining on any other expressions than col will turn off coalescing.

maintain_order{‘none’, ‘left’, ‘right’, ‘left_right’, ‘right_left’}

Which DataFrame row order to preserve, if any. Do not rely on any observed ordering without explicitly setting this parameter, as your code may break in a future release. Not specifying any ordering can improve performance.

none	(Default) No specific ordering is desired. The ordering might differ across Polars versions or even between different runs.
left	Preserves the order of the left DataFrame.
right	Preserves the order of the right DataFrame.
left_right	First preserves the order of the left DataFrame, then the right.
right_left	First preserves the order of the right DataFrame, then the left.

build_side: {‘auto’, ‘prefer_left’, ‘prefer_right’, ‘force_left’, ‘force_right’}

Which side of the join will be used as the build side. This side will be likely be held in memory as a hash table. Note that unless a force_ variant is chosen, the chosen side might differ across Polars versions or even between different runs.

auto	(Default) Let Polars figure out the build side.
prefer_left	Unless there’s a very good reason to believe that the right side is smaller, use the left side.
prefer_right	Unless there’s a very good reason to believe that the left side is smaller, use the right side.
force_left	Always use the left side.
force_right	Always use the right side.

Warning

This functionality is considered experimental. It may be removed or changed at any point without it being considered a breaking change.

allow_parallel

Allow the physical plan to optionally evaluate the computation of both DataFrames up to the join in parallel.

force_parallel

Force the physical plan to evaluate the computation of both DataFrames up to the join in parallel.

See also

join_asof

Examples

>>> lf = pl.LazyFrame(
...     {
...         "foo": [1, 2, 3],
...         "bar": [6.0, 7.0, 8.0],
...         "ham": ["a", "b", "c"],
...     }
... )
>>> other_lf = pl.LazyFrame(
...     {
...         "apple": ["x", "y", "z"],
...         "ham": ["a", "b", "d"],
...     }
... )
>>> lf.join(other_lf, on="ham").collect()
shape: (2, 4)
┌─────┬─────┬─────┬───────┐
│ foo ┆ bar ┆ ham ┆ apple │
│ --- ┆ --- ┆ --- ┆ ---   │
│ i64 ┆ f64 ┆ str ┆ str   │
╞═════╪═════╪═════╪═══════╡
│ 1   ┆ 6.0 ┆ a   ┆ x     │
│ 2   ┆ 7.0 ┆ b   ┆ y     │
└─────┴─────┴─────┴───────┘
>>> lf.join(other_lf, on="ham", how="full").collect()
shape: (4, 5)
┌──────┬──────┬──────┬───────┬───────────┐
│ foo  ┆ bar  ┆ ham  ┆ apple ┆ ham_right │
│ ---  ┆ ---  ┆ ---  ┆ ---   ┆ ---       │
│ i64  ┆ f64  ┆ str  ┆ str   ┆ str       │
╞══════╪══════╪══════╪═══════╪═══════════╡
│ 1    ┆ 6.0  ┆ a    ┆ x     ┆ a         │
│ 2    ┆ 7.0  ┆ b    ┆ y     ┆ b         │
│ null ┆ null ┆ null ┆ z     ┆ d         │
│ 3    ┆ 8.0  ┆ c    ┆ null  ┆ null      │
└──────┴──────┴──────┴───────┴───────────┘
>>> lf.join(other_lf, on="ham", how="left", coalesce=True).collect()
shape: (3, 4)
┌─────┬─────┬─────┬───────┐
│ foo ┆ bar ┆ ham ┆ apple │
│ --- ┆ --- ┆ --- ┆ ---   │
│ i64 ┆ f64 ┆ str ┆ str   │
╞═════╪═════╪═════╪═══════╡
│ 1   ┆ 6.0 ┆ a   ┆ x     │
│ 2   ┆ 7.0 ┆ b   ┆ y     │
│ 3   ┆ 8.0 ┆ c   ┆ null  │
└─────┴─────┴─────┴───────┘
>>> lf.join(other_lf, on="ham", how="semi").collect()
shape: (2, 3)
┌─────┬─────┬─────┐
│ foo ┆ bar ┆ ham │
│ --- ┆ --- ┆ --- │
│ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╡
│ 1   ┆ 6.0 ┆ a   │
│ 2   ┆ 7.0 ┆ b   │
└─────┴─────┴─────┘
>>> lf.join(other_lf, on="ham", how="anti").collect()
shape: (1, 3)
┌─────┬─────┬─────┐
│ foo ┆ bar ┆ ham │
│ --- ┆ --- ┆ --- │
│ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╡
│ 3   ┆ 8.0 ┆ c   │
└─────┴─────┴─────┘

>>> lf.join(other_lf, how="cross").collect()
shape: (9, 5)
┌─────┬─────┬─────┬───────┬───────────┐
│ foo ┆ bar ┆ ham ┆ apple ┆ ham_right │
│ --- ┆ --- ┆ --- ┆ ---   ┆ ---       │
│ i64 ┆ f64 ┆ str ┆ str   ┆ str       │
╞═════╪═════╪═════╪═══════╪═══════════╡
│ 1   ┆ 6.0 ┆ a   ┆ x     ┆ a         │
│ 1   ┆ 6.0 ┆ a   ┆ y     ┆ b         │
│ 1   ┆ 6.0 ┆ a   ┆ z     ┆ d         │
│ 2   ┆ 7.0 ┆ b   ┆ x     ┆ a         │
│ 2   ┆ 7.0 ┆ b   ┆ y     ┆ b         │
│ 2   ┆ 7.0 ┆ b   ┆ z     ┆ d         │
│ 3   ┆ 8.0 ┆ c   ┆ x     ┆ a         │
│ 3   ┆ 8.0 ┆ c   ┆ y     ┆ b         │
│ 3   ┆ 8.0 ┆ c   ┆ z     ┆ d         │
└─────┴─────┴─────┴───────┴───────────┘