Join / range · All operations

Join on a range or nearest time

Inequality, interval, as-of, nearest-in-time join.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one table from two or more
Level
several clauses of SQL, or a window

The DuckDB step

The price in force on the order date

It reads the orders and prices tables the proofs share. Create them first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE prices AS SELECT * FROM (VALUES
  ('widget', DATE '2026-01-01', 10.0),
  ('widget', DATE '2026-01-10', 11.0),
  ('gadget', DATE '2026-01-01', 20.0),
  ('gizmo',  DATE '2026-01-15', 33.0)
) t(product, valid_from, unit_price);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT o.order_id, o.product, o.order_date, p.valid_from, p.unit_price
FROM orders o ASOF LEFT JOIN prices p ON o.product = p.product AND o.order_date >= p.valid_from ORDER BY o.order_id;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 10 AND max(unit_price) FILTER (WHERE order_id = 1) = 10 AND max(unit_price) FILTER (WHERE order_id = 6) = 11 AND count(*) FILTER (WHERE order_id = 4 AND unit_price IS NULL) = 1 FROM result)) THEN 'as expected' ELSE error('an order did not take the price in force on its date') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “an order did not take the price in force on its date”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Join to the nearest earlier or later row

Match each row to the one row of another table whose ordered key is nearest before, after or either side.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache DataFusion (1)
  • ASOF JOINread 2026-09-27

    Match each left row with at most one right row by an ordered comparison, Snowflake syntax

EasyMorph (1)
  • Interval mergeread 2026-09-27

    Join columns from another table where a key falls between two interval columns

KNIME Analytics Platform (1)
  • Subset Matcherread 2026-09-27

    Pair each itemset of one table with the transactions of another that contain it

Polars (2)
  • DataFrame.join_asofread 2026-09-27

    Join each row to the nearest key of the other frame: backward, forward or nearest, within groups

  • DataFrame.join_whereread 2026-09-27

    Join two frames on one or more inequality predicates

RapidMiner Studio (1)
  • Calculate Overlapsread 2026-09-27

    Compute all pairwise overlaps among a set of named time intervals

Talend (1)
  • tIntervalMatchread 2026-09-27

    Match a value from the main flow to a range of values in a lookup flow and return related data

Background

Inequality joins join relational tables on inequality conditions, and are used in various applications.[1] In DuckDB, an as-of join finds the nearest event in a reference table, such as prices, when working with temporal or similarly ordered data.[2]

When to use it

DuckDB offers as-of joins for time series that are not perfectly aligned, as when clocks are slightly off or there is a delay between cause and effect.[3] In DuckDB, as-of joins are used to find the value of a varying property at a specific point in time, the use case the name came from.[3] Reach for an as-of join when each row needs the one value that was in force at its moment: the price on the order date, the rate on the day of the payment. (Meridian's view)

In DuckDB, an inner as-of join returns at most one right row per left row, so the left table will not grow but can shrink where there are missing times on the right.[3]

In DataFusion, if several right rows have the same equality keys and ordered value, which tied row an as-of join selects is nondeterministic.[4] Make the reference table unique on its key and time before the join, so the same query always picks the same row. (Meridian's view)

In pandas and Polars, both inputs to an as-of join must already be sorted by the as-of key.[5][6] Polars' join_asof takes a tolerance, so the join is only done where the near keys are within that distance.[6] Decide how old a match may be and set a tolerance where the tool has one, because the nearest earlier row can be older than the data can bear. (Meridian's view)

In a DuckDB as-of join written with USING and SELECT *, the query returns the left side's values of the joined columns, not the right side's.[2] List both time columns explicitly, so the time of the matched row can be read beside the time it was matched to. (Meridian's view)

The method

A DuckDB ASOF join requires at least one inequality condition on the ordering field, and any other conditions must be equalities (or NOT DISTINCT).[2] In DuckDB, the left and right order of the tables in an ASOF join is significant.[2] In DataFusion, an optional ON clause of equality conditions divides rows into equality groups before the ordered match.[4]

pandas' merge_asof can search backward for the last right row whose key is less than or equal to the left's key, or forward for the first greater than or equal.[5] A nearest search in pandas' merge_asof selects the right row whose key is closest in absolute distance to the left's key.[5] Polars and pandas both have an allow_exact_matches setting, which when False matches only strictly less-than or strictly greater-than keys.[5][6]

In kdb+, aj matches the leading columns for equality and takes the last value of the final column, which is of a sortable type, typically time.[7] For each record of the left table, kdb+'s aj returns one record, appending the last matching record of the right table, or nulls where none matches.[7]

Compared with the alternatives

In pandas and Polars, an as-of join is similar to a left join, except that it matches on the nearest key rather than on equal keys.[5][6] In DuckDB, an as-of join embodies common temporal analytic semantics that can be cumbersome and slow to implement in standard SQL.[3] In DuckDB 2.0, a nearest join matches each left row with the top-k right rows by a similarity or distance measure, which is handy for vector and embedding workloads.[2]

Sources

  1. [1]Lightning Fast and Space Efficient Inequality Joinssecondary source
  2. [2]FROM and JOIN Clauses – DuckDBdocumentation
  3. [3]AsOf Join – DuckDBdocumentation
  4. [4]SELECT syntax — Apache DataFusion main documentationdocumentation
  5. [5]pandas.merge_asof — pandas 3.1.1 documentationdocumentation
  6. [6]polars.DataFrame.join_asof — Polars documentationdocumentation
  7. [7]aj, aj0, ajf, ajf0 – as-of join | Reference | kdb+ and q documentation - kdb+ and q documentationdocumentation