A temporal JOIN connects records according to their validity over time, often using effective-from and effective-to dates.
Business attributes such as prices, employee assignments, exchange rates, and account statuses can change over time.
Find the account status that was valid when a transaction occurred.
SELECT
t.TransactionID,
t.TransactionDate,
s.Status
FROM Transactions t
INNER JOIN AccountStatusHistory s
ON t.AccountID = s.AccountID
AND t.TransactionDate >= s.ValidFrom
AND t.TransactionDate < s.ValidTo;Used in banking, finance, HR history, pricing, compliance, and slowly changing dimensions.
- Temporal JOINs use time-aware relationships.
- Validity periods define when a record applies.
- Boundary rules must be consistent.
- Overlapping periods can cause multiple matches.
- Useful for historical reporting.
What is a temporal JOIN?
It is a JOIN that determines matching records based on whether a date or time falls within a record's validity period.
Where are temporal JOINs used?
They are common in historical pricing, account status, employee history, exchange rates, and effective-dated data.