Collapsing versions in 2 tables?

Question

PostgreSQL

There is a table ft:

| id | param1 | param2 |          dfrom        |           dto         |
|----|--------|--------|-----------------------|-----------------------|
|  1 |  'SS'  |  'DD'  | '01.04.2010 12:30:20' | '17.04.2010 13:10:14' |
|  1 |  'ZZ'  |  null  | '17.04.2010 13:10:15' | '18.01.2010 04:13:15' |
|  1 |  'ZZ'  |  'GG'  | '18.01.2010 04:13:16' | '12.12.2010 00:00:00' |

Second table st:

| id | param3 |          dfrom        |           dto         |
|----|--------|-----------------------|-----------------------|
|  1 |  'KK'  | '01.03.2010 12:30:20' | '02.04.2010 13:10:14' |
|  1 |  'QQ'  | '02.04.2010 13:10:15' | '12.12.2010 00:00:00' |

The result should be:

| id | param1 | param2 | param3 |          dfrom        |            dto        |
|  1 |  'ZZ'  |  'DD'  |  'QQ'  | '18.01.2010 04:13:16' | '12.12.2010 00:00:00' |
|  1 |  null  |  null  |  'KK'  | '01.03.2010 12:30:20' | '01.04.2010 12:30:19' |
|  1 |  'SS'  |  'DD'  |  'KK'  | '01.04.2010 12:30:20' | '02.04.2010 13:10:14' |
|  1 |  'SS'  |  'DD'  |  'QQ'  | '02.04.2010 13:10:15' | '17.04.2010 13:10:14' |
|  1 |  'ZZ'  |  null  |  'QQ'  | '17.04.2010 13:10:15' | '18.01.2010 04:13:15' |

I have a problem, I can't get the second entry (1, null, null, 'KK' ...). It does not output a time span in which a record did not overlap with other records in another table.

My request:

select 
 a.id, param1, param2, param3,
 case when b.dfrom < a.dfrom then b.dfrom else a.dfrom end as dfrom,
 case when b.dto < a.dto then b.dto else a.dto end as dto
from
 ft a cross join st b
where
 a.id = b.id and (a.dfrom between b.dfrom and b.dto or a.dto between b.dfrom and b.dto)
Или надо union использовать?

select id, param1, param2, null as param3, dfrom, dto from ft
union all
select id, null as param1, null as param2, param3, dfrom, dto from st

Thanks in advance for your reply)

Answer 1

With the linking condition, you generally nullify the meaning of cross join.
Actually, because of this condition, records that are not in ft are eliminated.
Provide in the condition that ft.id or st.id can be NULL, i.e. the current condition should only be true when ft.id and st.id are not NULL.
I would make the main request with left join and join it from st through union, what did not get from st in the first request.