Understanding Temporal Table JOIN in SQL and Apache Flink

Temporal Table JOIN is a feature introduced in ANSI‑SQL 2011 that records the full history of rows and allows queries as of a specific point in time. Major DBMSs such as Oracle, SQL Server and DB2 implement this standard.

The article first shows a generic DDL for a system‑versioned table, followed by INSERT, UPDATE and DELETE examples that illustrate how the system‑generated start and end timestamps define the validity interval of each row.

It then presents a concrete SQL Server implementation, including the CREATE TABLE statement with PERIOD FOR SYSTEM_TIME and the corresponding DML statements, and demonstrates how a FOR SYSTEM_TIME AS OF query retrieves the snapshot of data at a given timestamp.

Next, the article explains Apache Flink’s support for Temporal Tables. Flink follows the ANSI‑SQL semantics but uses a different syntax (LATERAL TABLE(TemporalTableFunction)). The need for Temporal Tables is illustrated with a rates‑history example, and the Flink Scala code that defines sources, registers a TemporalTableFunction, and performs a temporal join is shown.

Several code snippets are provided, for example:

CREATE TABLE Emp (ENo INTEGER, Sys_Start TIMESTAMP(12) GENERATED ALWAYS AS ROW START, Sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END, EName VARCHAR(30), PERIOD FOR SYSTEM_TIME (Sys_Start, Sys_end)) WITH SYSTEM VERSIONING;

INSERT INTO Emp (ENo, EName) VALUES (22217, 'Joe');

UPDATE Emp SET EName = 'Tom' WHERE ENo = 22217;

DELETE FROM Emp WHERE ENo = 22217;

SELECT ENo, EName, Sys_Start, Sys_End FROM Emp FOR SYSTEM_TIME AS OF TIMESTAMP '2011-01-02 00:00:00';

val temporalTableFunction = tab.createTemporalTableFunction('rowtime, 'currency)

SELECT o.currency, o.amount, r.rate, o.amount * r.rate AS yen_amount FROM Orders AS o, LATERAL TABLE (Rates(o.rowtime)) AS r WHERE r.currency = o.currency;

The article also compares Temporal Table JOIN with traditional stream‑stream joins and LATERAL joins, highlighting differences in state management and driving direction.

Finally, it proposes an improvement to align Flink’s syntax with the ANSI‑SQL “FOR SYSTEM_TIME AS OF” clause and shows a conceptual diagram of the enhanced processing flow.

In summary, the piece provides a comprehensive guide to Temporal Table JOIN, covering theory, SQL Server usage, Flink implementation, code examples, and practical considerations.