Beginner

446. DATE RANGE JOIN

Learn how to join records based on whether a date falls within a defined date range.

📘 Lesson 446 of 728 ⏱ 12 min 🎯 Intermediate

A DATE RANGE JOIN matches records when a date from one table falls between a start date and an end date in another table.

Business rules often change over time, so analysts need to match transactions or events to the correct period.

Match a transaction date to the commission rate that was active on that date.

SQL 
SELECT
    t.TransactionID,
    t.TransactionDate,
    r.Rate
FROM Transactions t
JOIN CommissionRates r
    ON t.TransactionDate BETWEEN r.StartDate AND r.EndDate;

Date range joins are common in pricing, employee history, contracts, exchange rates, insurance policies, and financial reporting.

  • Date range joins compare dates with periods.
  • Start and end dates define the valid period.
  • BETWEEN can express inclusive date ranges.
  • Historical data commonly uses this pattern.
  • Overlapping periods should be avoided.
What is a DATE RANGE JOIN?

It joins records when a date falls within a start and end date range.

Where are DATE RANGE JOINs commonly used?

They are commonly used for historical rates, pricing, contracts, employee history, and time-based business rules.

Have you finished this lesson?

Introduction to SQL – All Lessons