A date-range JOIN matches rows when a date from one table falls within a valid period defined by another table.
Many business relationships are valid only during specific time periods.
Match an employee's transaction to the salary band that was active on the transaction date.
SELECT
t.TransactionID,
t.TransactionDate,
r.Rate
FROM Transactions t
INNER JOIN ExchangeRates r
ON t.CurrencyCode = r.CurrencyCode
AND t.TransactionDate >= r.StartDate
AND t.TransactionDate < r.EndDate;Used in pricing, salary history, exchange rates, contracts, promotions, and effective-dated reporting.
- Date-range JOINs use inequalities.
- Start and end boundaries must be defined carefully.
- Half-open ranges can prevent overlapping periods.
- Indexes and date filtering can affect performance.
- Overlapping date ranges can create duplicate matches.
What is a date-range JOIN?
It matches a record when its date falls within a valid start and end period in another table.
What problem can overlapping date ranges cause?
A single record may match multiple periods and create duplicate result rows.