Working with joins in temporal tables in MySQL involves combining the historical data from the history table with the current data in the current table based on specific conditions. To perform joins with temporal tables, you can use regular join syntax along with the FOR SYSTEM_TIME clause to specify the desired validity period.
Let's consider an example scenario where we have two temporal tables: employees and departments. The employees table stores employee data, and the departments table stores department data. Both tables have a period of validity defined using the valid_from and valid_to columns.
Here's how you can perform joins with temporal tables in MySQL:
Create the temporal tables:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
valid_from DATETIME(6) NOT NULL,
valid_to DATETIME(6) NOT NULL,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) ENGINE=InnoDB;
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100),
valid_from DATETIME(6) NOT NULL,
valid_to DATETIME(6) NOT NULL,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) ENGINE=InnoDB;
Enable system versioning for the temporal tables:
ALTER TABLE employees ADD SYSTEM VERSIONING;
ALTER TABLE departments ADD SYSTEM VERSIONING;
Insert data into the temporal tables:
-- Insert employees
INSERT INTO employees (id, name, department_id, valid_from, valid_to)
VALUES (1, 'John Doe', 1, '2023-01-01', '2023-07-05'),
(2, 'Jane Smith', 2, '2023-01-01', '2023-07-05');
-- Insert departments
INSERT INTO departments (id, name, valid_from, valid_to)
VALUES (1, 'HR', '2023-01-01', '2023-07-05'),
(2, 'Engineering', '2023-01-01', '2023-07-05');
Perform a join query with temporal tables:
SELECT e.id, e.name AS employee_name, d.name AS department_name
FROM employees FOR SYSTEM_TIME AS OF '2023-06-30' AS e
JOIN departments FOR SYSTEM_TIME AS OF '2023-06-30' AS d
ON e.department_id = d.id;
In the above example, we perform a join between the employees and departments tables using the JOIN keyword. The FOR SYSTEM_TIME AS OF clause specifies the desired point in time ('2023-06-30' in this case) to retrieve the historical versions of the tables. The alias e and d are used to differentiate between the two tables.
The result of the join query will include the employee's ID, name, and the department name as they were on June 30, 2023.
By using the appropriate validity period in the FOR SYSTEM_TIME AS OF clause, you can retrieve the historical versions of the tables at any given point in time and perform joins between them.
Note that the FOR SYSTEM_TIME AS OF clause can also be used with other join types such as LEFT JOIN, RIGHT JOIN, or FULL JOIN to handle scenarios where one table may have historical data while the other does not.
That's how you can work with joins in temporal tables in MySQL. The FOR SYSTEM_TIME AS OF clause allows you to retrieve historical versions of the tables based on specific validity periods and perform joins to obtain the desired data.