A Range JOIN matches rows when a value falls within a defined range. Unlike an equality JOIN, the condition commonly uses operators such as BETWEEN, >=, or <=.
Many business rules depend on ranges, such as salary bands, tax slabs, pricing tiers, interest rates, or date-effective periods.
Assign each employee to a salary grade based on the employee's salary range.
SELECT
e.EmployeeName,
e.Salary,
g.GradeName
FROM Employees e
INNER JOIN SalaryGrades g
ON e.Salary BETWEEN g.MinSalary AND g.MaxSalary;Used in payroll, taxation, pricing, financial analysis, risk scoring, and classification systems.
- Range JOINs use inequality conditions.
- BETWEEN is commonly used for inclusive ranges.
- Ranges should not unintentionally overlap.
- Boundary values must be tested.
- Range JOINs can be more expensive than equality JOINs.
What is a Range JOIN?
A Range JOIN matches a record when a value falls within a defined range instead of requiring exact equality.
Where are Range JOINs used?
They are used for salary bands, pricing tiers, tax slabs, date-effective rules, and other range-based business classifications.