A range JOIN matches a value from one table against a range defined in another table. The condition commonly uses operators such as BETWEEN, >=, and <.
Some business rules are based on ranges rather than exact keys.
Match an employee salary to the appropriate salary band.
SELECT
e.EmployeeName,
e.Salary,
b.BandName
FROM Employees e
INNER JOIN SalaryBands b
ON e.Salary >= b.MinSalary
AND e.Salary < b.MaxSalary;Used for salary bands, tax brackets, commission rates, pricing tiers, credit scoring, and risk categories.
- Range JOINs do not require exact equality.
- Numeric ranges can be used.
- Date ranges can also be used.
- Overlapping ranges can create duplicate matches.
- Range boundaries must be designed carefully.
What is a range JOIN?
A range JOIN matches records when a value falls within a defined range in another table.
What problem can overlapping ranges cause?
A single record may match multiple ranges and produce duplicate or unexpected results.