Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Joins, Groups and Windows

Joins in Polars

Combine tables with DataFrame.join in Polars. Learn inner, left, full, semi, anti and cross joins, different key names, suffixes, validation and as-of joins.

Real data rarely lives in one table. Orders sit in one place, customers in another, and product details in a third. A join connects two tables by matching values in one or more key columns, so each order row can pick up the customer's name and country.

Polars gives you one method for all of it: DataFrame.join() (and the same join() on a LazyFrame). You choose the strategy with the how argument. In this lesson you will meet every strategy, learn what happens to the key columns and to duplicate names, and see how to catch accidental row multiplication before it ruins a report.

The basic join

Two tables share a key, customer_id. The first argument is the other table, on names the key.

Python An inner join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The default is how="inner": only rows whose key exists on both sides survive. Order 4 belongs to customer 40, who is not in customers, so it disappears. Customer 30 has no orders, so she disappears too.

flowchart LR
    L["left table<br/>keys 10, 20, 10, 40"] --> J{"how"}
    R["right table<br/>keys 10, 20, 30"] --> J
    J --> I["inner: 10, 20, 10"]
    J --> LJ["left: 10, 20, 10, 40"]
    J --> F["full: 10, 20, 10, 40, 30"]

The same two tables produce different results depending on how.

The join strategies

how Keeps
"inner" Rows with a match on both sides
"left" Every left row; right columns are null where nothing matches
"right" Every right row; left columns are null where nothing matches
"full" Every row from both sides
"semi" Left rows that have a match (only left columns)
"anti" Left rows that have no match (only left columns)
"cross" Every left row paired with every right row (no key)
Python Left, full, semi and anti

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the full join output: it has two key columns, customer_id and customer_id_right. A full join cannot merge them without losing information, because a row may have a key on only one side. Pass coalesce=True to merge them into one column.

Semi and anti joins are filters in disguise. They never add columns and never multiply rows, which makes them perfect for questions like "which orders have no matching customer?" (anti) or "which customers placed at least one order?" (semi, with the tables swapped).

Prefer semi and anti over joining and then filtering on nulls. They say what you mean, run faster, and cannot duplicate rows.

Different key names, several keys

When the key columns have different names, use left_on and right_on. To join on several columns together, pass lists.

Python left_on, right_on and composite keys

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The output keeps the left key names (store, day). Rows match only when both keys agree at once.

Duplicate column names and suffixes

If both tables have a non-key column with the same name, Polars keeps the left one and gives the right one the suffix _right. Choose your own with suffix.

Python Choosing a suffix

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Cross joins

A cross join has no key. It builds every combination, so the result has len(left) * len(right) rows. It is handy for generating grids, such as every store with every day of the week.

Python Every store on every day

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Be careful with big tables: 100,000 rows crossed with 100,000 rows is ten billion rows.

Guarding against row multiplication

The most common join bug is a key that is not unique on the side you thought was unique. If a customer appears twice in customers, every order of that customer is duplicated and your totals become wrong, silently. The validate argument turns that bug into an error:

  • "1:1": keys are unique on both sides
  • "m:1": unique on the right side only (many left rows, one right row)
  • "1:m": unique on the left side only
  • "m:m": no check (the default)
Python validate catches duplicate keys

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Nulls in keys

By default, a null key never matches another null key, the same as in SQL. If you want nulls to match each other, pass nulls_equal=True. Missing keys therefore usually drop out of an inner join; check for them first with df["key"].null_count().

As-of joins

An as-of join matches each left row to the closest right row, instead of an exactly equal one. It is how you attach "the price in effect at that moment" to events. Both tables must be sorted on the key.

Python join_asof: latest price at the time of each trade

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

strategy="backward" takes the last price at or before each trade; "forward" takes the next one; "nearest" picks the closest. Add by="symbol" to do this per group, and tolerance to limit how far back to look.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join keeps every row of the left table and fills right-hand columns with null where nothing matches?

    A left join keeps all left rows. Unmatched rows get null in the columns that come from the right table.
  2. 2. You want the orders whose customer is missing from the customers table, with only the order columns. Which is best?

    An anti join returns left rows with no match and keeps only the left columns.
  3. 3. What does validate='m:1' do?

    'm:1' means many left rows may match one right row, so the keys must be unique on the right. Duplicates there raise an error instead of silently multiplying rows.
  4. 4. A cross join of a 4-row table with a 5-row table has how many rows?

    A cross join pairs every left row with every right row: 4 x 5 = 20.

Summary

  • df.join(other, on=..., how=...) is the single entry point; the default is inner.
  • left, right and full keep unmatched rows; semi and anti filter the left table; cross builds all pairs.
  • Use left_on/right_on for different key names and lists for composite keys.
  • Duplicate non-key names get the _right suffix; set suffix to choose another.
  • validate="m:1" (or "1:1") stops duplicate keys from multiplying rows.
  • join_asof matches the nearest key, ideal for time-based lookups.

Next: group_by and Aggregations