`polars join` for lazyframe

Joins a lazy frame with other lazy frame.

Signature

> polars join {flags} (other) (left_on) (right_on)

Flags

--inner, -i: Inner joining between lazyframes (default).
--left, -l: Left join between lazyframes.
--full, -f: Full join between lazyframes.
--cross, -c: Cross join between lazyframes.
--coalesce-columns: Sets the join coalesce strategy to colesce columns. Most useful when used with --full, which will not otherwise coalesce.
--nulls-equal: Join on null values. By default null values will never produce matches.
--suffix, -s {string}: Suffix to use on columns with same name.

Parameters

other: LazyFrame to join with.
left_on: Left column(s) to join on.
right_on: Right column(s) to join on.

Input/output types:

input	output
polars_dataframe	polars_dataframe
polars_lazyframe	polars_lazyframe

Examples

Join two lazy dataframes

> let df_a = ([[a b c];[1 "a" 0] [2 "b" 1] [1 "c" 2] [1 "c" 3]] | polars into-lazy)
    let df_b = ([["foo" "bar" "ham"];[1 "a" "let"] [2 "c" "var"] [3 "c" "const"]] | polars into-lazy)
    $df_a | polars join $df_b a foo | polars collect
╭───┬───┬───┬───┬─────┬─────╮
│ # │ a │ b │ c │ bar │ ham │
├───┼───┼───┼───┼─────┼─────┤
│ 0 │ 1 │ a │ 0 │ a   │ let │
│ 1 │ 2 │ b │ 1 │ c   │ var │
│ 2 │ 1 │ c │ 2 │ a   │ let │
│ 3 │ 1 │ c │ 3 │ a   │ let │
╰───┴───┴───┴───┴─────┴─────╯

Join one eager dataframe with a lazy dataframe

> let df_a = ([[a b c];[1 "a" 0] [2 "b" 1] [1 "c" 2] [1 "c" 3]] | polars into-df)
    let df_b = ([["foo" "bar" "ham"];[1 "a" "let"] [2 "c" "var"] [3 "c" "const"]] | polars into-lazy)
    $df_a | polars join $df_b a foo
╭───┬───┬───┬───┬─────┬─────╮
│ # │ a │ b │ c │ bar │ ham │
├───┼───┼───┼───┼─────┼─────┤
│ 0 │ 1 │ a │ 0 │ a   │ let │
│ 1 │ 2 │ b │ 1 │ c   │ var │
│ 2 │ 1 │ c │ 2 │ a   │ let │
│ 3 │ 1 │ c │ 3 │ a   │ let │
╰───┴───┴───┴───┴─────┴─────╯

Perform a full join of two dataframes and coalesce columns

> let table1 = [[A B]; ["common" "common"] ["table1" "only"]] | polars into-df
                let table2 = [[A C]; ["common" "common"] ["table2" "only"]] | polars into-df
                $table1 | polars join -f $table2 --coalesce-columns A A
╭───┬────────┬────────┬────────╮
│ # │   A    │   B    │   C    │
├───┼────────┼────────┼────────┤
│ 0 │ common │ common │ common │
│ 1 │ table2 │        │ only   │
│ 2 │ table1 │ only   │        │
╰───┴────────┴────────┴────────╯

Join one eager dataframe with another using a cross join

> let tokens = [[monopoly_token]; [hat] [shoe] [boat]] | polars into-df
    let players = [[name, cash]; [Alice, 78] [Bob, 135]] | polars into-df
    $players | polars select (polars col name) | polars join --cross $tokens | polars collect
╭───┬───────┬────────────────╮
│ # │ name  │ monopoly_token │
├───┼───────┼────────────────┤
│ 0 │ Alice │ hat            │
│ 1 │ Alice │ shoe           │
│ 2 │ Alice │ boat           │
│ 3 │ Bob   │ hat            │
│ 4 │ Bob   │ shoe           │
│ 5 │ Bob   │ boat           │
╰───┴───────┴────────────────╯

Join on column expressions

>
                let df1 = [[a b]; ["2025-01-01 01:00:00+0000" 1] ["2025-01-02 05:36:42+0000" 2]] | polars into-df --schema {a: "datetime<ms,UTC>", b: i8}

                let df2 = [[a c]; ["2025-01-01 00:00:00+0000" a] ["2025-01-02 00:00:00+0000" b]] | polars into-df --schema {a: "datetime<ms,UTC>", c: str}

                $df1 | polars join $df2 [(polars col a | polars truncate 1d)] [a]
╭───┬────────────┬───┬────────────┬───╮
│ # │     a      │ b │    a_x     │ c │
├───┼────────────┼───┼────────────┼───┤
│ 0 │ a year ago │ 1 │ a year ago │ a │
│ 1 │ a year ago │ 2 │ a year ago │ b │
╰───┴────────────┴───┴────────────┴───╯

Join on nulls