A range JOIN matches a value from one table against a minimum and maximum range stored in another table.
Business rules such as tax slabs, salary bands, pricing tiers, and risk categories often depend on value ranges.
Assign customers to a discount tier based on their total purchase amount.
SELECT
c.CustomerName,
c.TotalSales,
d.DiscountPercent
FROM Customers c
INNER JOIN DiscountTiers d
ON c.TotalSales >= d.MinSales
AND c.TotalSales < d.MaxSales;Used in taxation, pricing, commissions, risk scoring, salary bands, and customer segmentation.
- Range JOINs use comparison operators.
- Exact equality is not required.
- Ranges should be designed without unintended overlap.
- Boundary values require careful testing.
- Multiple matching ranges can create duplicate rows.
What is a range JOIN?
It matches a value against a range defined by conditions such as minimum and maximum values.
Where can range JOINs be used?
They are useful for tax slabs, discount tiers, commission rates, salary bands, and risk categories.