TopolearnReal analyses pull together several tables, and choosing the right join type decides which rows survive.
left.join(right, on="key", how=...) matches rows by key columns. Use left_on=/right_on= when the names differ, and a list for composite keys.
how="inner"(default): only keys present in both.how="left": every left row, nulls where the right has no match."right"is the mirror.how="full": everything from both; passcoalesce=Trueto merge the key columns into one.how="semi": left rows that have a match, without adding right columns (like SQLEXISTS).how="anti": left rows with no match, perfect for "customers who never ordered".how="cross": every combination.
Clashing non-key column names get a _right suffix (change it with suffix=). Null keys don't match each other unless you pass nulls_equal=True.
Duplicated keys multiply rows. Guard against that with validate="m:1" (or "1:1", "1:m"), which raises if the right side isn't unique, a cheap check that catches many bugs.
Join output order is not guaranteed by default; pass maintain_order="left" or sort afterwards.
To stack frames, use pl.concat([a, b]) (same columns) or how="diagonal" to union different columns, filling gaps with null. how="horizontal" puts frames side by side and, in Polars 2, requires equal heights.
Example
import polars as pl
users = pl.DataFrame({"uid": [1, 2, 3], "name": ["Ana", "Ben", "Cy"]})
orders = pl.DataFrame({"uid": [1, 1, 3, 4], "total": [10, 5, 7, 9]})
left = users.join(orders, on="uid", how="left", maintain_order="left")
print(left.rows())
# [(1, 'Ana', 10), (1, 'Ana', 5), (2, 'Ben', None), (3, 'Cy', 7)]
never = users.join(orders, on="uid", how="anti")
print(never["name"].to_list()) # ['Ben']
try:
orders.join(users, on="uid", how="left", validate="1:1")
except Exception as e:
print(type(e).__name__) # ComputeError (uid repeats in orders)
stacked = pl.concat([users, pl.DataFrame({"uid": [9]})], how="diagonal")
print(stacked.tail(1).rows()) # [(9, None)]Remember
- Pick the join type by which rows must survive: inner, left, full, semi, anti
validate="m:1"catches unexpected duplicate keyspl.concat(how="diagonal")unions frames with different columns
Watch out for
- Joining on a key that repeats on both sides and silently multiplying rows.