# Join on an inequality or interval

Join / range

A variant of [Join on a range or nearest time](https://meridian.online/operations/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.

Page: <https://meridian.online/operations/join-on-a-range-or-nearest-time/join-on-an-inequality-or-interval>

## Status

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.

## What other tools call it

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

- EasyMorph: [`Interval merge`](https://help.easymorph.com/doku.php?id=transformations:intervalmerge) (read 2026-09-27)
- KNIME Analytics Platform: [`Subset Matcher`](https://api.hub.knime.com/nodes/*Rj5vxjLi7FjrFN15?details=full) (read 2026-09-27)
- Polars: [`DataFrame.join_where`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- RapidMiner Studio: [`Calculate Overlaps`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/attribute_generation/get_overlaps.html) (read 2026-09-27)
- Talend: [`tIntervalMatch`](https://help.qlik.com/talend/en-US/components/8.0/standardization/standardization-component) (read 2026-09-27)