Joins

Four equi-join flavors and a time-series asof join, all lazy and all running through the same optimizer as everything else — filters push into the join sides where that cannot change the answer.

Enriching: inner and left

import keyten as kt

orders = kt.DataFrame([
    kt.Series.int("order_id", [1, 2, 3, 4]),
    kt.Series.int("cust_id", [10, 20, 10, 99]),
    kt.Series.float("amount", [50.0, 30.0, 20.0, 10.0]),
])
customers = kt.DataFrame([
    kt.Series.int("id", [10, 20]),
    kt.Series.string("name", ["ann", "bo"]),
])

inner = orders.lazy().inner_join(customers.lazy(), [("cust_id", "id")]).sort("order_id").collect()
assert inner.shape[0] == 3            # order 4 has no customer

left = orders.lazy().left_join(customers.lazy(), [("cust_id", "id")]).sort("order_id").collect()
assert left.shape[0] == 4
assert left.column("name").to_list()[-1] is None   # unmatched -> nulls

The contract: null keys never match, output is the left columns then the right columns minus its keys, and a right column colliding with a left name gets a _right suffix. Join row order is unspecified, so the examples sort explicitly before asserting positions.

Filtering by membership: semi and anti

When the right side only decides which left rows survive — not what columns they carry — say so. A semi or anti join emits left columns only, never duplicates a left row however many matches exist, and lets the engine probe with membership structures instead of materializing the join.

active = kt.DataFrame([kt.Series.int("id", [10])])

kept = orders.lazy().semi_join(active.lazy(), [("cust_id", "id")]).sort("order_id").collect()
assert kept.column("order_id").to_list() == [1, 3]

dropped = orders.lazy().anti_join(active.lazy(), [("cust_id", "id")]).sort("order_id").collect()
assert dropped.column("order_id").to_list() == [2, 4]

Multi-key joins

on takes as many (left, right) pairs as the key needs:

a = kt.DataFrame([
    kt.Series.string("sym", ["x", "x", "y"]),
    kt.Series.int("day", [1, 2, 1]),
    kt.Series.int("v", [1, 2, 3]),
])
b = kt.DataFrame([
    kt.Series.string("sym", ["x", "y"]),
    kt.Series.int("day", [2, 1]),
    kt.Series.int("w", [20, 30]),
])
out = a.lazy().inner_join(b.lazy(), [("sym", "sym"), ("day", "day")]).sort(["sym", "day"]).collect()
assert out.column("w").to_list() == [20, 30]

The asof join

join_asof is the time-series workhorse. The default matches each left row to the latest right row at or before it, per group. strategy="forward" selects the earliest row at or after it, while strategy="nearest" selects the closest candidate and breaks ties backward. tolerance rejects a candidate that is too far away without dropping the left row. See the time series tutorial for worked examples.

trades.lazy().join_asof(
    quotes.lazy(),
    ("ts", "ts"),
    by=[("sym", "sym")],
    strategy="backward",
    tolerance="5s",
)

Both sides must arrive sorted ascending on the on key with no nulls. The engine verifies that — from block metadata when it can prove it cheaply — and raises rather than silently sorting your data.

Reading a join plan

explain() shows the join tree and which filters were pushed into which side. A filter mentioning only left columns sinks into the left subtree for every flavor; right-only filters sink for inner joins but deliberately not for left joins (that would resurrect rows) or asof joins (removing a right row can change which row is "latest").