When would you write an INNER JOIN whose ON predicate is a range instead of an equality?

## ON is a predicate, not a key comparison The join rule is "emit the pairs where the ON predicate is TRUE". Nothing in that rule mentions equality. `ON` accepts any boolean expression over columns of the two sides: inequalities, ranges, several conditions combined with `AND` or `OR`, expressions over columns. A join whose predicate is not a plain equality is conventionally called a **non-equi join**; when the predicate is an interval containment, a **range join**. ## The canonical use: an interval-keyed lookup ```sql -- tiers: (min_amount, max_amount, tier_name), non-overlapping bands SELECT s.id, s.amount, t.tier_name FROM sales s INNER JOIN tiers t ON s.amount >= t.min_amount AND s.amount < t.max_amount; ``` The match is defined by containment: this sale's amount falls inside this tier's band. There is no shared key column to equi-join on, and inventing one (storing a `tier_id` on every sale) would duplicate the banding rule in the data and go stale the moment the bands change. The same shape covers temporal lookups — the rate, price or exchange rate that was in force at a given instant: ```sql SELECT o.id, o.amount * r.rate AS amount_usd FROM orders o INNER JOIN fx_rates r ON r.currency = o.currency AND o.placed_at >= r.valid_from AND o.placed_at < r.valid_to; ``` Note the mix: one equality (`currency`) plus a range. That is common and entirely normal — an ON clause combines conditions freely. ## Overlapping ranges silently duplicate This is the failure mode interviewers probe. A join does not choose a best or first match; it emits **every** qualifying pair. If two tiers overlap and a sale falls in both, that sale appears twice, once per tier, and every downstream consumer sees two rows where the business means one. The join is behaving exactly as specified; the *data* violated the assumption that the bands partition the space. The defensive habits are: enforce non-overlap where you can, and verify it when you cannot — ```sql -- does any amount fall into more than one tier? SELECT a.tier_name, b.tier_name FROM tiers a INNER JOIN tiers b ON a.tier_name <> b.tier_name AND a.min_amount < b.max_amount AND b.min_amount < a.max_amount; ``` An empty result means the bands do not overlap. ## Half-open intervals beat BETWEEN `BETWEEN x AND y` is shorthand for `>= x AND <= y` — **both** endpoints inclusive. For contiguous bands that is the wrong shape: if one tier ends at 100 and the next begins at 100, a value of exactly 100 matches both and is emitted twice. Writing the band as `>= min AND < max` (half-open) makes each boundary belong to exactly one band, with no gap and no overlap. The same argument applies with extra force to timestamps, where an inclusive upper bound forces you to guess the last representable instant before the next period begins. Also watch the argument order: `BETWEEN` requires the lower bound first. `x BETWEEN hi AND lo` with the bounds swapped is not an error — it is simply never TRUE, so the join silently returns nothing. ## Missing rows are still dropped A range join is an inner join, so a row that falls into **no** band disappears entirely. An amount below the lowest tier's floor, or a timestamp before the first rate's start, is not an error — the row is just absent from the result. If the bands are supposed to cover everything, a shrinking row count is your signal that they do not. ## Other non-equi shapes Inequality predicates appear beyond banding: pairing each row with rows that came before it, joining on a tolerance (`ABS(a.v - b.v) < 0.01`), or on containment in some other sense. The mechanics are always the same — every TRUE pair is emitted — and the same duplication caution applies, more strongly, because an open-ended inequality can match a great many partners per row. ## The interview answer "When the relationship is containment rather than equal keys — tiers, validity windows, buckets. ON takes any boolean predicate. I write the bands half-open rather than with BETWEEN so the boundaries do not double-match, and I make sure the ranges do not overlap, because the join emits every match rather than picking one."