Join Temporal Tables

N tables are to be joined together. These tables each contain their own attributes, but all belong to the same entity. When joining temporal tables, not only the key that identifies the entity must be considered, but also the temporal validity of the rows. This page shows how the different time intervals of the source tables are considered during the join.

Example with N=2

The table product_join is created from the table product_1 and the table product_2 for the entity product. The table product_join contains all information from product_1 and product_2 assigned to the appropriate valid time periods.

The table product_1 contains the price of the products in dollars, the table product_2 the VAT in percent. The table product_join contains all attributes from product_1 and product_2, in this case price and vat.

table product_1
product_id	valid_from	valid_to	price
1002	2020-01-01	2020-01-09	40
1002	2020-01-09	2020-01-11	30
1002	2021-01-01	2021-02-01	30
1002	2021-03-15	2022-01-01	35
1002	2022-01-01	2022-06-01	30
1002	2022-06-01	2022-07-01	50

table product_2
product_id	valid_from	valid_to	vat
1002	2020-01-05	2020-01-06	16
1002	2020-01-09	2020-01-11	19
1002	2020-07-01	2021-03-01	15
1002	2021-03-01	2023-01-01	12

table product_join
product_id	valid_from	valid_to	price	vat
1002	2020-01-01	2020-01-05	40	null
1002	2020-01-05	2020-01-06	40	16
1002	2020-01-06	2020-01-09	40	null
1002	2020-01-09	2020-01-11	30	19
1002	2020-07-01	2021-01-01	null	15
1002	2021-01-01	2021-02-01	30	15
1002	2021-02-01	2021-03-01	null	15
1002	2021-03-01	2021-03-15	null	12
1002	2021-03-15	2022-01-01	35	12
1002	2022-01-01	2022-06-01	30	12
1002	2022-06-01	2022-07-01	50	12
1002	2022-07-01	2023-01-01	null	12

The creation of the table product_join is done in three steps. The SQL code complies with the PostgreSQL syntax.

Step 1: Extract all keys with their times valid_from and valid_to from the tables to be joined.

create table times as  
select product_id,  valid_from  as valid  from product_1
union
select product_id,  valid_to    as valid  from product_1
union
select product_id,  valid_from  as valid  from product_2
union
select product_id,  valid_to    as valid  from product_2;

table times
product_id	valid
1002	2020-01-01
1002	2020-01-05
1002	2020-01-06
1002	2020-01-09
1002	2020-01-11
1002	2020-07-01
1002	2021-01-01
1002	2021-02-01
1002	2021-03-01
1002	2021-03-15
1002	2022-01-01
1002	2022-06-01
1002	2022-07-01
1002	2023-01-01

Step 2: Linking the time points to time slices

create table time_slices as
select * from
(
    select product_id, 
        valid as valid_from,
        lead(valid) over(partition by product_id order by valid) 
              as valid_to
    from times
) foo
where valid_to is not null;

table times_slices
product_id	valid_from	valid_to
1002	2020-01-01	2020-01-05
1002	2020-01-05	2020-01-06
1002	2020-01-06	2020-01-09
1002	2020-01-09	2020-01-11
1002	2020-01-11	2020-07-01
1002	2020-07-01	2021-01-01
1002	2021-01-01	2021-02-01
1002	2021-02-01	2021-03-01
1002	2021-03-01	2021-03-15
1002	2021-03-15	2022-01-01
1002	2022-01-01	2022-06-01
1002	2022-06-01	2022-07-01
1002	2022-07-01	2023-01-01

Step 3: Joining the time_slices table with the source tables.

create table product_join as
select a.product_id, a.valid_from, a.valid_to, b1.price, b2.vat
from time_slices a  
    left outer join product_1 b1 on a.product_id = b1.product_id 
         and (a.valid_from, a.valid_to)     overlaps 
             (b1.valid_from, b1.valid_to)
    left outer join product_2 b2 on a.product_id = b2.product_id
         and (a.valid_from, a.valid_to)     overlaps 
             (b2.valid_from, b2.valid_to)
where b1.price is not null or b2.vat is not null;

table product_join
product_id	valid_from	valid_to	price	vat
1002	2020-01-01	2020-01-05	40	null
1002	2020-01-05	2020-01-06	40	16
1002	2020-01-06	2020-01-09	40	null
1002	2020-01-09	2020-01-11	30	19
1002	2020-07-01	2021-01-01	null	15
1002	2021-01-01	2021-02-01	30	15
1002	2021-02-01	2021-03-01	null	15
1002	2021-03-01	2021-03-15	null	12
1002	2021-03-15	2022-01-01	35	12
1002	2022-01-01	2022-06-01	30	12
1002	2022-06-01	2022-07-01	50	12
1002	2022-07-01	2023-01-01	null	12

Extension to any number N of tables

The example for N=2 can be extended to any N with the following SQL code.
Here attr_1, attr_2, attr_3, … for any number of attributes, which can be different attributes for each of the N tables.

Use at your own responsibility!

create table times as  
select product_id,   valid_from   as valid from product_1
union
select product_id,   valid_to     as valid from product_1
union
select product_id,   valid_from   as valid from product_2
union
select product_id,   valid_to     as valid from product_2
…
union 
select product_id,   valid_from   as valid from product_N
union
select product_id,   valid_to     as valid from product_N;

create table time_slices as
select * from
(
    select product_id, 
        valid as valid_from,
        lead(valid) over(partition by product_id order by valid) 
              as valid_to
    from times
) foo
where valid_to is not null;

create table product_join as
select a.product_id, a.valid_from, a.valid_to, 
    b1.attr_1, b1.attr_2, b1.attr_3, …
    b2.attr_1, b2.attr_2, b2.attr_3, …
    …
    bN.attr_1, bN.attr_2, bN.attr_3, …
from time_slices a 
    left outer join product_1 b1 on a.product_id = b1.product_id 
        and (a.valid_from, a.valid_to)       overlaps
            (b1.valid_from, b1.valid_to)
    left outer join product_2 b2 on a.product_id = b2.product_id
        and (a.valid_from, a.valid_to)       overlaps 
            (b2.valid_from, b2.valid_to)        
    …
   left outer join product_N bN on a.product_id = bN.product_id
       and (a.valid_from, a.valid_to)       overlaps 
           (bN.valid_from, bN.valid_to)
where coalesce(b1.product_id, b2.product_id, … bN.product_id) 
    is not null;