Join / range · All operations

Join on an inequality or interval

A variant of Join on a range or nearest time

Match rows where a value falls in a range, intervals overlap, or another inequality or containment condition holds.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Join on a range, an overlap, a containment and a plain inequality

SQL
CREATE TEMP TABLE events AS SELECT * FROM (VALUES (1, 3), (2, 10), (3, 12), (4, NULL), (5, 25), (6, 40)) t(e, v);
CREATE TEMP TABLE bands AS SELECT * FROM (VALUES ('low', 0, 10), ('mid', 10, 20), ('wide', 5, 15), ('open', NULL, 30)) t(band, lo, hi);
-- a value in a range: BETWEEN is inclusive at both ends, so 10 is in low, mid and wide (three rows for one event); a NULL bound or value matches nothing
CREATE TEMP TABLE in_range AS SELECT events.e, bands.band FROM events JOIN bands ON events.v BETWEEN bands.lo AND bands.hi;
CREATE TEMP TABLE spans AS SELECT * FROM (VALUES (1, 1, 5), (2, 4, 8), (3, 8, 10), (4, NULL, 12), (5, 5, 6)) t(id, s, f);
-- intervals [s, f) overlap when each starts before the other ends; 2 and 3 only touch
CREATE TEMP TABLE overlapping AS SELECT a.id AS a_id, b.id AS b_id FROM spans a JOIN spans b ON a.id < b.id AND a.s < b.f AND b.s < a.f;
-- containment: the inner span lies wholly inside the outer one
CREATE TEMP TABLE inside AS SELECT a.id AS inner_id, b.id AS outer_id FROM spans a JOIN spans b ON a.id <> b.id AND a.s >= b.s AND a.f <= b.f;
-- another inequality: each pair in which the first value is larger
CREATE TEMP TABLE larger AS SELECT a.e AS bigger, b.e AS smaller FROM events a JOIN events b ON a.v > b.v;
SELECT * FROM in_range ORDER BY e, band;
SELECT CASE WHEN ((SELECT list((e, band) ORDER BY e, band) = [(1, 'low'), (2, 'low'), (2, 'mid'), (2, 'wide'), (3, 'mid'), (3, 'wide')] FROM in_range)
  AND (SELECT list((a_id, b_id) ORDER BY a_id, b_id) = [(1, 2), (2, 5)] FROM overlapping)
  AND (SELECT list((inner_id, outer_id)) = [(5, 2)] FROM inside)
  AND (SELECT count(*) = 10 AND count(*) FILTER (WHERE bigger = 4 OR smaller = 4) = 0 FROM larger)) THEN 'as expected' ELSE error('the range join should give event 2 (value 10) three bands and events 4, 5 and 6 none, the spans should overlap as (1, 2) and (2, 5) only, span 5 should lie inside span 2, and 5 non-NULL values should make 10 larger-smaller pairs') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the range join should give event 2 (value 10) three bands and events 4, 5 and 6 none, the spans should overlap as (1, 2) and (2, 5) only, span 5 should lie inside span 2, and 5 non-NULL values should make 10 larger-smaller pairs”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

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 (1)
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

A theta join (θ-join) combines tuples of two relations where the condition is not simply equality of shared attributes, using an operator such as < or ≥.[1] In EasyMorph, Interval Merge matches a value to the range it falls in, and where ranges overlap it inserts one row for each match.[2]

When to use it

In EasyMorph, Interval Merge is used to categorise or bucket values that fall within a given range or scale, such as survey responses, temperature ranges or size scales.[2] In DuckDB, a band join, which joins values that are close to each other, is written as a double inequality join condition.[3] Inequality joins answer questions such as which transactions lasted longer yet produced smaller revenues than transactions in another region.[4]

In EasyMorph's Interval Merge, the lower boundary of a range is inclusive and the upper boundary exclusive.[2] Settle whether each range includes its upper bound before joining, and where bands share an edge write the half-open form, lower <= value AND value < upper. (Meridian's view) In DuckDB, BETWEEN x AND y uses x as the lower bound, so if x is greater than y the result is always false.[5] Check that no range has its bounds the wrong way round before joining. (Meridian's view)

In DuckDB, whenever either input of a comparison is NULL, the output of the comparison is NULL.[5] DuckDB provides infinite time values, from Postgres, which compare as expected, though arithmetic with them produces NULLs or infinities.[3] Give an open-ended range an explicit bound, or an infinite value where the engine has one, rather than leaving it NULL. (Meridian's view)

In Polars, join_where does not preserve the row order of its inputs, and the function is marked experimental.[6]

The method

In relational algebra, a θ-join is the selection of the rows that satisfy θ from the Cartesian product of the two relations.[1] For a θ-join, forming the Cartesian product and then filtering it is conceptually correct, but an implementation would use more sophisticated data structures.[1]

In DuckDB, a range comparison (<, <=, >, >=) in a join condition lets the engine use the ordering it implies by sorting the input relations.[3] For two range conditions, the Inequality Join (IEJoin) sorts both tables on the first condition, sorts positions on the second, and then scans for matches that pass both.[3] IEJoin was introduced in the paper Lightning Fast and Space Efficient Inequality Joins, by Zuhair Khayyat and colleagues, in the Proceedings of the VLDB Endowment in 2015.[4] IEJoin sorts a relation on both join attributes, creates a permutation array for one with respect to the other, and leverages a bit-array to emit join results.[4]

In DuckDB, IEJoin also speeds up interval joins on the intersection of two intervals, and band joins on values that are close to each other.[3]

Compared with the alternatives

When θ is the equality operator, the θ-join is also called an equijoin.[1] Hash joins require an equijoin predicate, comparing records of the two tables with a conjunction of equality operators on one or more columns.[7]

Sources

  1. [1]Join (relational algebra) - Wikipediasecondary source
  2. [2]INTERVAL MERGE [EasyMorph Help]documentation
  3. [3]Range Joins in DuckDB – DuckDBdocumentation
  4. [4]Lightning Fast and Space Efficient Inequality Joinssecondary source
  5. [5]Comparisons – DuckDBdocumentation
  6. [6]polars.DataFrame.join_where — Polars documentationdocumentation
  7. [7]Hash join - Wikipediasecondary source