Non-equi join

Match rows by a comparison or a range instead of a key.

5 min read

Overview

Every join so far has asked whether two values are equal. A non-equi join asks something looser. Is this amount inside that range, is this date after that one, is this value larger. The join condition is a comparison and the match is whatever satisfies it.

sales

id	amount
1	30
2	75
3	120

tiers

tier	lo	hi
Basic	0	49
Mid	50	99
Premium	100	999

Trace 30, 75, and 120 against the tier bounds. Write down the three placements before reading on.

How it works

The mechanics are an ordinary join with the condition swapped: instead of on a.key = b.key, the match is on s.amount between t.lo and t.hi. Each sale pairs with every tier whose test it passes.

select s.id, s.amount, t.tier
from sales s
join tiers t
  on s.amount between t.lo and t.hi
order by s.id;

Run the demo and compare it with your three placements.

Match by range, not keyPress play, or step through it yourself.

sales s

id	amount
1	30
2	75
3	120

tiers t

tier	lo	hi
Basic	0	49
Mid	50	99
Premium	100	999

result

idamounttier

s.amount between t.lo and t.hi

Each sale appears once because these integer bands cover the values without overlap. The comparison condition creates the relationship by testing which band contains each amount.

Patterns

Three relationships that only a comparison can express.

1
Banding
Price tiers, grade boundaries, cohort buckets. The bands live in a table and each fact lands in the band that contains it. Changing a boundary is an update rather than a code change.
2
Time windows
Match an event to the session or promotion or shift that was active when it happened, where the event time falls between the window start and end. Time rarely joins on equality, because an event belongs to whatever window contains it.
```
select e.id, w.name
from events e
join windows w
  on e.at >= w.starts_at
 and e.at <  w.ends_at;
```
3
Ordered comparisons
Pair each row with the rows before it, or above it: a.id < b.id, h.salary > e.salary. You met these shapes in the self join guide. The inequality is what makes them work.

Trade-offs

Banding has a rival in the case expression that hardcodes the boundaries in the query. It works, yet it buries the bands where no one can see or change them. A tier table makes the bands data. They become editable and auditable and shared by every query that needs them. Prefer the table, because making the bands data is the instinct the modeling track teaches.

Keep equality in the condition where it exists. Matching sales to tiers within one region is on s.region_id = t.region_id and s.amount between t.lo and t.hi, where the equality narrows the candidates and the range completes the match. A non-equi join adds comparisons to keys rather than replacing them.

Pitfalls

Overlapping bands bring the fan-out back

If one band ends at 50 and the next begins at 50, a sale of exactly 50 passes both tests and appears twice. Every count downstream then inflates. Bands must tile, meaning they touch without overlapping and cover without gaps. The convention that guarantees it is a closed lower bound and an open upper one. Write it lo <= amount and amount < hi and set each band’s hi to the next band’s lo.

between is inclusive on both ends. A band written 0 to 50 and a band written 50 to 100 both claim 50. Decide which end is open and write the comparison out when it matters.
Gaps drop rows silently. An amount no band contains simply vanishes under an inner join, exactly like any unmatched row. A left join against the bands shows you what fell through.
One row can legitimately match many. The ordered-comparison shapes are supposed to fan out, but a sale-to-tier match is not, so know which of the two you are writing.

Performance

Comparison joins can cost more than selective equality joins. Their cost depends on the predicates and data distribution as well as the plan. Retain available equality predicates to narrow the candidates, then inspect the plan when both sides are large.

Practice

EasyLabel sales through a range join whose tier bands do not overlap. Every sale must appear exactly once with the tier whose bounds contain its amount.MediumMatch events to promotion windows with half-open time bounds and the shared business key. Each event should match at most one promotion. An event at starts_at must match while one at ends_at must not.HardRepair a banding join by converting its ranges to contiguous half-open intervals. A boundary test suite must yield one match per covered amount and zero matches outside.

Recap

Use a non-equi join when the relationship is a range, time window, or ordered comparison.
Use half-open bands and check both gaps and overlaps with boundary values.
Retain equality predicates such as a business key so fewer rows need the comparison test.
A left join audit exposes unmatched values that inner semantics would drop silently.
Inspect the plan when both sides are large because comparison cost depends on data and predicates.