Beginner

591. Date Range JOIN

Learn how to JOIN records when a date falls within an effective date range.

📘 Lesson 601 of 728 ⏱ 14 min 🎯 Advanced

A Date Range JOIN matches a record when its date falls between a start date and an end date stored in another table.

Many business rules change over time, such as employee assignments, prices, exchange rates, and account statuses.

Find the price that was effective on each order date.

SQL 
SELECT
    o.OrderID,
    o.OrderDate,
    o.ProductID,
    p.Price
FROM Orders o
INNER JOIN ProductPrices p
    ON o.ProductID = p.ProductID
   AND o.OrderDate >= p.StartDate
   AND o.OrderDate < p.EndDate;

Used in pricing, financial analysis, historical reporting, employee assignments, and effective-dated business rules.

  • Date ranges can be used as JOIN conditions.
  • Start and end boundaries must be defined carefully.
  • Half-open ranges can prevent overlapping periods.
  • Historical data often requires effective dates.
  • Overlapping ranges can create duplicate matches.
What is a Date Range JOIN?

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

Why can overlapping date ranges be a problem?

A single record may match multiple ranges and produce duplicate results.

Have you finished this lesson?

Introduction to SQL – All Lessons