Beginner

487. JOIN on Date Range

Learn how JOINs can match records based on dates or date ranges instead of exact key values.

📘 Lesson 487 of 728 ⏱ 13 min 🎯 Advanced

A range JOIN matches rows when a value falls within a defined range, such as a transaction date falling between an employee's assignment start and end dates.

Many business relationships are time-based rather than based on a single exact key.

Find the employee assignment that was active on each transaction date.

SQL 
SELECT
    t.TransactionID,
    t.TransactionDate,
    e.EmployeeName
FROM Transactions t
INNER JOIN EmployeeAssignments e
    ON t.EmployeeID = e.EmployeeID
   AND t.TransactionDate BETWEEN e.StartDate AND e.EndDate;

Common in payroll, banking, pricing, contracts, employee history, taxation, and financial reporting.

  • Range JOINs use conditions such as BETWEEN.
  • They can match records based on effective dates.
  • Date ranges should be designed carefully.
  • Overlapping ranges can create multiple matches.
  • Range JOINs are common in historical analysis.
What is a date-range JOIN?

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

What problem can overlapping date ranges cause?

A single record may match multiple ranges and create multiple result rows.

Have you finished this lesson?

Introduction to SQL – All Lessons