TL;DR In the book Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass, section 6.3.1 he demonstrates how to perform a sequenced inner join on a transaction-time state table, but does not discuss how to perform a sequenced left outer join.

Initial setup

Suppose the following events occur:

• 2025-01-01 - User 0 (John) is created. Order 0 (for User 0) is created. Order 1 (for User 1) is created.
• 2025-01-02 - User 1 (Mary) is created.
• 2025-01-03 - User 0 (Jon) is renamed.
• 2025-01-04 - Order 0 is moved from User 0 (Jon) to User 1 (Mary).

The resulting transaction-time state tables are these:

Note: I have deliberately combined the history and current table, unlike the official SQL server temporal tables feature which has them separated. This is similar to querying temporal tables with FOR SYSTEM_TIME ALL when using the official temporal table feature in SQL Server 2016

Orders

Users

DDL/DML for Copy Paste

CREATE TABLE Orders (
    Id INT,
    UserId INT ,
    ValidFrom DATE,
    ValidTo DATE,
);

INSERT INTO Orders
VALUES (0, 0, '2025-01-01', '2025-01-04'),
       (0, 1, '2025-01-04', '9999-12-31'),
       (1, 1, '2025-01-01', '9999-12-31');

CREATE TABLE Users (
    Id INT,
    Name VARCHAR(50),
    ValidFrom DATE,
    ValidTo DATE
);

INSERT INTO Users
VALUES (0, 'John', '2025-01-01', '2025-01-03'),
       (0, 'Jon', '2025-01-03', '9999-12-31'),
       (1, 'Mary', '2025-01-02', '9999-12-31');

Now, I can use the following query to get all the current orders, and the associated user name:

SELECT O.Id, U.Name 
FROM Orders O
LEFT JOIN Users U ON U.Id = O.UserId AND O.ValidTo = '9999-12-31' AND U.ValidTo = '9999-12-31'

Note: query corrected per T N's answer

Results

Question

I want to see how the results of the above query have changed over time. What query should I write to get the results shown below?

Desired Results