如何在SQL Server中按Valid_from/Valid_to日期关联数据表（含图示场景）

Alright, let's tackle this date range join problem in SQL Server—this is super common in systems that track historical state (like employee records, pricing, or inventory). I'll start with the core logic, then walk through a concrete implementation based on typical scenarios you'd see in diagrams.

Core Logic for Date Range Joins #

When working with tables that use Valid_From and Valid_To to track record validity, the key is to match records where their date ranges overlap in any way (unless your business rules require a stricter match). The universal condition for overlapping ranges is:

TableA.Valid_From <= ISNULL(TableB.Valid_To, GETDATE()) 
AND ISNULL(TableA.Valid_To, GETDATE()) >= TableB.Valid_From

What this does: #

• The ISNULL handles cases where Valid_To is NULL (a common pattern to mark "currently active" records, since there's no end date yet). We replace NULL with GETDATE() (current date) to treat active records as valid up to today.
• This condition catches all overlapping scenarios: full containment, partial overlap, or even one range starting exactly when the other ends.

Practical Implementation for Typical Scenarios #

Let's assume your diagram shows two tables:

• Employees: Tracks employee details with EmployeeID, Name, Valid_From, Valid_To
• Salaries: Tracks salary changes for employees with EmployeeID, SalaryAmount, Valid_From, Valid_To

Case 1: Match All Overlapping Records (Most Common) #

This query returns every employee-salary pair where their valid periods overlap, and even calculates the exact overlapping window:

SELECT
    e.EmployeeID,
    e.Name,
    s.SalaryAmount,
    -- Calculate the start of the overlapping period
    CASE 
        WHEN e.Valid_From > s.Valid_From THEN e.Valid_From 
        ELSE s.Valid_From 
    END AS Overlap_Start,
    -- Calculate the end of the overlapping period (handle active records)
    CASE 
        WHEN e.Valid_To < s.Valid_To THEN e.Valid_To 
        ELSE ISNULL(s.Valid_To, GETDATE()) 
    END AS Overlap_End
FROM
    Employees e
JOIN
    Salaries s 
    ON e.EmployeeID = s.EmployeeID
    -- Core overlap condition
    AND e.Valid_From <= ISNULL(s.Valid_To, GETDATE())
    AND ISNULL(e.Valid_To, GETDATE()) >= s.Valid_From
ORDER BY
    e.EmployeeID, Overlap_Start;

Case 2: Strict Match (Employee Period Fully Contains Salary Period) #

If your business rule requires that the employee's active period completely covers the salary's valid period, adjust the join condition to:

JOIN
    Salaries s 
    ON e.EmployeeID = s.EmployeeID
    AND s.Valid_From >= e.Valid_From
    AND ISNULL(s.Valid_To, GETDATE()) <= ISNULL(e.Valid_To, GETDATE())

Handling "Currently Active" Records Cleanly #

Instead of using NULL for Valid_To, many teams use a far-future date like '9999-12-31' to mark active records. This eliminates the need for ISNULL in queries, making them faster (since functions on indexed columns can break index usage):

-- If Valid_To uses '9999-12-31' for active records
SELECT *
FROM Employees e
JOIN Salaries s
    ON e.EmployeeID = s.EmployeeID
    AND e.Valid_From <= s.Valid_To
    AND e.Valid_To >= s.Valid_From;

Performance Tips #

• Index Strategically: Create composite indexes on the ID column plus the date fields to speed up joins. For example:

  CREATE NONCLUSTERED INDEX IX_Employees_ValidRange ON Employees(EmployeeID, Valid_From, Valid_To);
  CREATE NONCLUSTERED INDEX IX_Salaries_ValidRange ON Salaries(EmployeeID, Valid_From, Valid_To);
  

• Filter First, Join Later: If you only need records valid on a specific date (e.g., today), use a CTE to filter active records before joining:

  WITH ActiveEmployees AS (
      SELECT * FROM Employees
      WHERE Valid_From <= GETDATE() AND Valid_To >= GETDATE()
  ),
  ActiveSalaries AS (
      SELECT * FROM Salaries
      WHERE Valid_From <= GETDATE() AND Valid_To >= GETDATE()
  )
  SELECT * FROM ActiveEmployees e JOIN ActiveSalaries s ON e.EmployeeID = s.EmployeeID;
  

内容的提问来源于stack exchange，提问作者Chaiyanun Bootnumpech