Inequality joins with BETWEEN

Concepts covered: Inequality Joins

Every join so far has paired rows on equality: a key on the left equals a key on the right. The ON clause accepts any condition that returns true, false or NULL for a pair of rows, and a range condition pairs a row with the band its value falls inside. Tax rates by income, shipping prices by weight, surge multipliers by hour and discounts by order size are all bands with a lower bound and an upper bound, stored as rows.

SELECT e.name, e.salary, t.rate AS tax_rate
FROM employee_metrics e
JOIN cost_allocs t
  ON e.salary >= t.min_income
  AND e.salary < t.max_income

The cost_allocs table holds the tax bands: each row is a min_income, a max_income and the rate for that band. Play it and watch Alice's 75,000 meet all 3 bands. Against the first band the condition is false because 75,000 is above 40,000. Against the second it is true on both halves. Against the third it is false because 75,000 is below 85,000. One pair survives per employee, which is what a well built band table guarantees: the bands cover the whole range and never overlap, so each value lands in exactly 1 of them.

BETWEEN closes both ends

x BETWEEN a AND b means x >= a AND x <= b, inclusive on both sides. That matters at the seams. If 2 bands touch at 40,000, so that one ends at 40,000 and the next starts at 40,000, an income of exactly 40,000 satisfies both, and that employee comes back twice. Switch between the 3 queries below to see the duplicate appear and then disappear.

BETWEEN

Closed bands that end 1 below the next band's start, joined with BETWEEN.

SELECT
  e.name,
  e.salary,
  t.rate AS tax_rate
FROM employee_metrics e
JOIN tax_bands t
  ON e.salary BETWEEN t.min_income AND t.max_income
ORDER BY e.name

Touching

Bands that touch at 40,000 and 85,000, joined with BETWEEN: Dave's 40,000 matches 2 of them.

SELECT
  e.name,
  e.salary,
  t.rate AS tax_rate
FROM employee_metrics e
JOIN tax_bands_touching t
  ON e.salary BETWEEN t.min_income AND t.max_income
ORDER BY e.name

Half-open

The same touching bands with >= on the lower bound and < on the upper: each value matches 1 band.

SELECT
  e.name,
  e.salary,
  t.rate AS tax_rate
FROM employee_metrics e
JOIN tax_bands_touching t
  ON e.salary >= t.min_income
  AND e.salary < t.max_income
ORDER BY e.name

The first tab returns 4 rows, 1 per employee, because the bands end at 39,999 and 84,999 and leave no value inside 2 bands at once. The second tab returns 5 rows: Dave's 40,000 matches the band ending at 40,000 and the band starting at 40,000, so he is taxed at 12% on one row and 22% on the next. The third tab returns 4 rows again without touching the data. A lower bound tested with >= and an upper bound tested with < make each band half open, and a value on a seam belongs to exactly 1 band. Half open bands also survive decimals: a closed band ending at 39,999 leaves a salary of 39,999.50 matching nothing, while a half open band ending below 40,000 catches it.

A parcel weighs exactly 5.0 kg. The rate table has a band from 0 to 5 and a band from 5 to 10, and the join condition is weight BETWEEN min_kg AND max_kg. How many rows does this parcel produce?

• 1 row, because the engine assigns a value on a seam to the lower band only
• 0 rows, because a value equal to a bound falls outside a BETWEEN range
• 2 rows, because BETWEEN includes both ends and 5.0 satisfies both bands
• 1 row, because the second band's lower bound is tested with > when 2 bands touch

What a range join costs

A hash join is fast because equality lets the engine put each key in a bucket and look up its partner in one step. A range condition has no single key to hash, so the engine falls back to comparing every left row against every right row. One million employees against 300 bands is 300,000,000 comparisons. The fix is to add an equality condition whenever one exists. If bands differ by country, join on country as well: the engine hashes on country, then tests the range only against each country's few bands.

SELECT
  e.name,
  e.salary,
  t.rate
FROM employees e
JOIN tax_bands t
  ON e.country = t.country
  AND e.salary >= t.min_income
  AND e.salary < t.max_income

About this lesson

This section is part of the Joins: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.