Joins¶
Join(other, options...) matches this frame's rows with other's by equal keys. The
examples join five orders with four customers: customer 2, Ben, has no order, and
order 13's customer, 5, is not a customer.
func customers() *ursus.LazyFrame {
return ursus.Frame(
ursus.Values("customer_id", []int64{1, 2, 3, 4}),
ursus.Values("name", []string{"Ada", "Ben", "Cai", "Dee"}),
)
}
func orders() *ursus.LazyFrame {
return ursus.Frame(
ursus.Values("order", []int64{10, 11, 12, 13, 14}),
ursus.Values("customer_id", []int64{1, 3, 1, 5, 4}),
ursus.Values("amount", []float64{25, 40, 10, 99, 15}),
)
}
and show collects a query and prints it:
func show(lf *ursus.LazyFrame) {
df, err := lf.Collect(context.Background())
if err != nil {
log.Fatal(err)
}
fmt.Println(df)
}
The kinds¶
JoinOn names the keys, and JoinHow the kind; inner is the default.
Pairs of rows whose keys match. Order 13 and Ben are left out.
on := ursus.JoinOn(ursus.Col("customer_id"))
show(orders().Join(customers(), on, ursus.JoinMaintainOrder(ursus.JoinOrderLeft)))
shape: (4, 4)
┌───────┬─────────────┬─────────┬────────┐
│ order │ customer_id │ amount │ name │
│ Int64 │ Int64 │ Float64 │ String │
├───────┼─────────────┼─────────┼────────┤
│ 10 │ 1 │ 25 │ "Ada" │
│ 11 │ 3 │ 40 │ "Cai" │
│ 12 │ 1 │ 10 │ "Ada" │
│ 14 │ 4 │ 15 │ "Dee" │
└───────┴─────────────┴─────────┴────────┘
Every row of the left frame, with nulls where the right has no match.
on := ursus.JoinOn(ursus.Col("customer_id"))
show(orders().Join(customers(), on, ursus.JoinHow(ursus.JoinLeft)))
shape: (5, 4)
┌───────┬─────────────┬─────────┬────────┐
│ order │ customer_id │ amount │ name │
│ Int64 │ Int64 │ Float64 │ String │
├───────┼─────────────┼─────────┼────────┤
│ 10 │ 1 │ 25 │ "Ada" │
│ 11 │ 3 │ 40 │ "Cai" │
│ 12 │ 1 │ 10 │ "Ada" │
│ 13 │ 5 │ 99 │ null │
│ 14 │ 4 │ 15 │ "Dee" │
└───────┴─────────────┴─────────┴────────┘
Every row of the right frame, with nulls where the left has no match.
on := ursus.JoinOn(ursus.Col("customer_id"))
show(orders().Join(customers(), on, ursus.JoinHow(ursus.JoinRight)))
shape: (5, 4)
┌───────┬─────────────┬─────────┬────────┐
│ order │ customer_id │ amount │ name │
│ Int64 │ Int64 │ Float64 │ String │
├───────┼─────────────┼─────────┼────────┤
│ 10 │ 1 │ 25 │ "Ada" │
│ 11 │ 3 │ 40 │ "Cai" │
│ 12 │ 1 │ 10 │ "Ada" │
│ 14 │ 4 │ 15 │ "Dee" │
│ null │ 2 │ null │ "Ben" │
└───────┴─────────────┴─────────┴────────┘
Every row of both, each key column kept, since either side's can be null.
on := ursus.JoinOn(ursus.Col("customer_id"))
show(orders().Join(customers(), on, ursus.JoinHow(ursus.JoinFull)))
shape: (6, 5)
┌───────┬─────────────┬─────────┬───────────────────┬────────┐
│ order │ customer_id │ amount │ customer_id_right │ name │
│ Int64 │ Int64 │ Float64 │ Int64 │ String │
├───────┼─────────────┼─────────┼───────────────────┼────────┤
│ 10 │ 1 │ 25 │ 1 │ "Ada" │
│ 11 │ 3 │ 40 │ 3 │ "Cai" │
│ 12 │ 1 │ 10 │ 1 │ "Ada" │
│ 13 │ 5 │ 99 │ null │ null │
│ 14 │ 4 │ 15 │ 4 │ "Dee" │
│ null │ null │ null │ 2 │ "Ben" │
└───────┴─────────────┴─────────┴───────────────────┴────────┘
The left rows that have a match, once each, and only the left's columns: the customers who ordered.
The left rows that have no match: the customers who never ordered.
Row order. An inner join's order is not promised, as in Polars: ursus hashes the
smaller input and the rows follow the other. JoinMaintainOrder keeps an order, as
Polars' maintain_order does, for every kind: JoinOrderLeft the left frame's, each
row's matches in the right frame's order, as the inner example does, and
JoinOrderRight the right frame's. Changed in 0.6.7: it took a Boolean, and kept
an inner join's left order alone. Left, semi, anti and full joins keep the left
frame's order without it while they fit in memory; JoinMaintainOrder keeps it when
they spill too.
A right join's order and columns are not Polars'
A right join here keeps the left frame's row order, with the right rows that
matched nothing after them, and keeps the key where the left frame has it. Polars
gives a right join's rows in the right frame's order, and its key among the right
frame's columns. JoinMaintainOrder(ursus.JoinOrderRight) gives Polars' rows;
Select the columns in the order you want them.
Keys¶
Merged keys¶
A key named the same on both sides appears once, for every kind but full, which keeps
both, customer_id and customer_id_right. JoinCoalesce(true) merges them, the
left's where a row has a left side and the right's where it does not:
on := ursus.JoinOn(ursus.Col("customer_id"))
show(orders().Join(customers(), on,
ursus.JoinHow(ursus.JoinFull),
ursus.JoinCoalesce(true)))
shape: (6, 4)
┌───────┬─────────────┬─────────┬────────┐
│ order │ customer_id │ amount │ name │
│ Int64 │ Int64 │ Float64 │ String │
├───────┼─────────────┼─────────┼────────┤
│ 10 │ 1 │ 25 │ "Ada" │
│ 11 │ 3 │ 40 │ "Cai" │
│ 12 │ 1 │ 10 │ "Ada" │
│ 13 │ 5 │ 99 │ null │
│ 14 │ 4 │ 15 │ "Dee" │
│ null │ 2 │ null │ "Ben" │
└───────┴─────────────┴─────────┴────────┘
Keys of different names¶
JoinLeftOn and JoinRightOn name the keys on each side, paired by position. Both
key columns are kept, customer_id and id: merging them would delete a column you
named. Polars drops the right one; JoinCoalesce(true) does here too.
people := ursus.Frame(
ursus.Values("id", []int64{1, 3}),
ursus.Values("name", []string{"Ada", "Cai"}),
)
show(orders().
Join(people,
ursus.JoinLeftOn(ursus.Col("customer_id")),
ursus.JoinRightOn(ursus.Col("id"))).
Sort(ursus.Asc(ursus.Col("order"))))
shape: (3, 5)
┌───────┬─────────────┬─────────┬───────┬────────┐
│ order │ customer_id │ amount │ id │ name │
│ Int64 │ Int64 │ Float64 │ Int64 │ String │
├───────┼─────────────┼─────────┼───────┼────────┤
│ 10 │ 1 │ 25 │ 1 │ "Ada" │
│ 11 │ 3 │ 40 │ 3 │ "Cai" │
│ 12 │ 1 │ 10 │ 1 │ "Ada" │
└───────┴─────────────┴─────────┴───────┴────────┘
Several keys match on all of them: JoinOn(Col("a"), Col("b")).
Columns of the same name¶
A right column whose name the left frame already has takes a suffix, _right unless
JoinSuffix says otherwise:
shipped := ursus.Frame(
ursus.Values("order", []int64{10, 11, 12}),
ursus.Values("amount", []float64{25, 38, 10}),
)
show(orders().
Join(shipped,
ursus.JoinOn(ursus.Col("order")),
ursus.JoinSuffix("_shipped"),
ursus.JoinMaintainOrder(ursus.JoinOrderLeft)).
Select(ursus.Col("order"), ursus.Col("amount"), ursus.Col("amount_shipped")))
shape: (3, 3)
┌───────┬─────────┬────────────────┐
│ order │ amount │ amount_shipped │
│ Int64 │ Float64 │ Float64 │
├───────┼─────────┼────────────────┤
│ 10 │ 25 │ 25 │
│ 11 │ 40 │ 38 │
│ 12 │ 10 │ 10 │
└───────┴─────────┴────────────────┘
Null keys¶
A null key matches nothing, as in SQL, unless JoinNullsEqual(true) makes nulls
equal:
key := ursus.ValuesNullable("k", []int64{1, 0}, []bool{true, false}) // 1 and null
left := ursus.Frame(key, ursus.Values("l", []string{"a", "b"}))
right := ursus.Frame(key, ursus.Values("r", []string{"x", "y"}))
on := ursus.JoinOn(ursus.Col("k"))
ordered := ursus.JoinMaintainOrder(ursus.JoinOrderLeft)
show(left.Join(right, on, ordered))
show(left.Join(right, on, ordered, ursus.JoinNullsEqual(true)))
shape: (1, 3)
┌───────┬────────┬────────┐
│ k │ l │ r │
│ Int64 │ String │ String │
├───────┼────────┼────────┤
│ 1 │ "a" │ "x" │
└───────┴────────┴────────┘
shape: (2, 3)
┌───────┬────────┬────────┐
│ k │ l │ r │
│ Int64 │ String │ String │
├───────┼────────┼────────┤
│ 1 │ "a" │ "x" │
│ null │ "b" │ "y" │
└───────┴────────┴────────┘
Validation¶
A join whose keys repeat where you expected them unique multiplies rows silently.
JoinValidate checks the keys' cardinality and fails the query when the data breaks
it:
twice := ursus.Frame(
ursus.Values("customer_id", []int64{1, 1}),
ursus.Values("name", []string{"Ada", "Ada Lovelace"}),
)
_, err := orders().
Join(twice,
ursus.JoinOn(ursus.Col("customer_id")),
ursus.JoinValidate(ursus.ValidateManyToOne)).
Collect(context.Background())
fmt.Println(err)
ursus: join: validate=m:1 requires unique keys on the right side, but a key is duplicated
first duplicate at right row 1
key: col("customer_id")
each duplicate multiplies every matching row on the other side
de-duplicate with .Unique(...), or pass JoinValidate(ursus.ValidateManyToMany)
The checks are ValidateOneToOne, ValidateOneToMany, ValidateManyToOne and
ValidateManyToMany, the first side named being the left: ValidateManyToOne on
orders.Join(customers) asks for each customer once.
As-of joins¶
JoinAsOf matches each left row with the nearest right row before it, rather than an
equal one: each trade with the quote in force when it happened. AsOfBy matches
within equal keys first, and AsOfTolerance limits how far back a match may be:
at := func(s int) time.Time { return time.Date(2026, 1, 5, 9, 0, s, 0, time.UTC) }
trades := ursus.Frame(
ursus.Values("time", []time.Time{at(1), at(5), at(9)}),
ursus.Values("ticker", []string{"ACME", "BOLT", "ACME"}),
ursus.Values("qty", []int64{100, 50, 70}),
)
quotes := ursus.Frame(
ursus.Values("time", []time.Time{at(0), at(2), at(4), at(6)}),
ursus.Values("ticker", []string{"ACME", "BOLT", "ACME", "BOLT"}),
ursus.Values("bid", []float64{10.0, 20.0, 10.1, 20.2}),
)
show(trades.JoinAsOf(quotes,
ursus.AsOfOn(ursus.Col("time")),
ursus.AsOfBy(ursus.Col("ticker")),
ursus.AsOfTolerance(ursus.Every("3s")),
))
shape: (3, 4)
┌────────────────────────────────┬────────┬───────┬─────────┐
│ time │ ticker │ qty │ bid │
│ Datetime(ns, UTC) │ String │ Int64 │ Float64 │
├────────────────────────────────┼────────┼───────┼─────────┤
│ 2026-01-05T09:00:01.000000000Z │ "ACME" │ 100 │ 10 │
│ 2026-01-05T09:00:05.000000000Z │ "BOLT" │ 50 │ 20 │
│ 2026-01-05T09:00:09.000000000Z │ "ACME" │ 70 │ null │
└────────────────────────────────┴────────┴───────┴─────────┘
The 09:00:09 ACME trade's last quote is five seconds old, past the three-second tolerance, so its bid is null.
- It is a left join: every left row comes back, its right columns null when nothing is near enough.
AsOfStrategyOpt(AsOfForward)takes the nearest row after, andAsOfNearestthe nearest either way.- Both frames must be sorted by the as-of key, and ursus checks that they are rather than trusting it: an unsorted side fails with an error.
- New in 0.6.7: a numeric key's tolerance is a number,
AsOfTolerance(5), and a tolerance of zero admits only an exact match, as Polars'.AsOfCoalesce(false)keeps the right frame's key too,ts_right; a key named apart on the two sides is kept on both, as in Polars.
Joins on a condition¶
JoinWhere(other, predicates...) pairs the rows that satisfy every predicate, such as
an interval: each order with the promotion running on its date. An equality among the
predicates becomes a hash join's key; without one, every pair is tested.
JoinWhereSuffix(other, suffix, predicates...) names a colliding right column with
another suffix, which the predicates use.
promotions := ursus.Frame(
ursus.Values("promotion", []string{"new year", "mid week"}),
ursus.Values("from", []string{"2026-01-01", "2026-01-06"}),
ursus.Values("to", []string{"2026-01-04", "2026-01-07"}),
).WithColumns(ursus.Col("from").Str().ToDate(), ursus.Col("to").Str().ToDate())
show(sales().
Select(ursus.Col("order_id"), ursus.Col("date")).
JoinWhere(promotions,
ursus.Col("date").Ge(ursus.Col("from")),
ursus.Col("date").Le(ursus.Col("to"))).
Select(ursus.Col("order_id"), ursus.Col("date"), ursus.Col("promotion")).
Sort(ursus.Asc(ursus.Col("order_id"))))
shape: (6, 3)
┌──────────┬────────────┬────────────┐
│ order_id │ date │ promotion │
│ Int64 │ Date │ String │
├──────────┼────────────┼────────────┤
│ 1 │ 2026-01-03 │ "new year" │
│ 2 │ 2026-01-03 │ "new year" │
│ 3 │ 2026-01-04 │ "new year" │
│ 6 │ 2026-01-06 │ "mid week" │
│ 7 │ 2026-01-07 │ "mid week" │
│ 8 │ 2026-01-07 │ "mid week" │
└──────────┴────────────┴────────────┘
Updating from another frame¶
New in 0.6.7. Update(other, options) writes another frame's values over this
one's, in the columns both have, matched by keys, as Polars' update: a null in
other leaves the value unless IncludeNulls, and the frame keeps its rows, its
columns and their order.
prices := ursus.Frame(
ursus.Values("sku", []string{"a", "b", "c"}),
ursus.ValuesNullable("price", []float64{9.5, 3, 0}, []bool{true, true, false}),
)
fixes := ursus.Frame(
ursus.Values("sku", []string{"c", "a"}),
ursus.Values("price", []float64{4.25, 9.75}),
)
show(prices.Update(fixes, ursus.UpdateOptions{On: []string{"sku"}}))
shape: (3, 2)
┌────────┬─────────┐
│ sku │ price │
│ String │ Float64 │
├────────┼─────────┤
│ "a" │ 9.75 │
│ "b" │ 3 │
│ "c" │ 4.25 │
└────────┴─────────┘
How keeps the frame's every row, UpdateLeft, the default; only those other
matches, UpdateInner; or also other's unmatched ones, as new rows, UpdateFull.
Without keys, rows are matched by their places. A column whose types differ takes the
type that holds both. It is a join, so it spills as one does and keeps its order.
Filtering by another frame¶
WhereExists(other, predicates...) keeps this frame's rows that have at least one row
in other satisfying every predicate, once each, with only this frame's columns:
SQL's EXISTS. WhereNotExists keeps those that have none. The predicates name
other's columns as a join would, with the suffix where a name collides:
show(customers().WhereExists(orders(),
ursus.Col("customer_id").Eq(ursus.Col("customer_id_right")),
ursus.Col("amount").Gt(20.0)))
shape: (2, 2)
┌─────────────┬────────┐
│ customer_id │ name │
│ Int64 │ String │
├─────────────┼────────┤
│ 1 │ "Ada" │
│ 3 │ "Cai" │
└─────────────┴────────┘
A semi or anti join asks the same question when the condition is equal keys alone.
Under a memory limit¶
Equi-joins spill both sides to disk under a memory budget, and give the answer they
give in memory; under a limit a join's row order is promised only with
JoinMaintainOrder. An as-of join streams both sides, whatever its strategy. See
Memory and spilling.