join on(...), [columnbinding: expression]*

Performs an inner join between the pipeline input and exactly one other dataset, keeping rows where the join predicate holds and optionally projecting additional columns from the right-hand side.

Use join when both sides must match; when left rows should survive with null right-hand values, use leftjoin or fulljoin. To test membership without adding columns, use exists.

Predicate

Write the join condition as on(...), a boolean expression that references the other dataset with @datasetName.column paths. The expression may combine equalities, inequalities, and and/or. Conjunctive equalities between default-input columns and @other columns are used for key propagation; arbitrary predicates still perform a relational join at execution time.

Trailing column bindings

After on(...), optional name:expression arguments add or replace columns. Expressions may reference the other dataset. Window functions are not allowed in these trailing bindings.

Output shape

The output dataset kind is the higher-precedence kind of the two inputs: event over interval over resource over table. When the left input is a table and the right side has valid-from or valid-to columns, those time columns are included in the output with names prefixed by the other dataset’s label to avoid collisions.

Temporal frame

A frame may appear inside on(...) to widen temporal matching. frame is not allowed when either input is a table, nor when both inputs are resource- or interval-shaped; convert at least one side with make_event first.

Acceleration

If the join predicate or any trailing binding uses a non-accelerable expression, acceleration for this stage is downgraded accordingly.

Categories

• Join

Accelerable

join is always accelerable if the input is accelerable. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.

Examples

join on(station_id = @station.id), city:@station.city

Inner-join measurements to the station dataset on station_id, then project the remote city column to enrich each matching measurement row.

join on(month_number = @months.month_number and temp > 0.0), label:@months.month_name

Combine an equi-join on month_number with an extra predicate on the default input (temp) inside on(...), then project month_name from months as label.