I have two tables as follows

TABLE_1

TABLE_2

I am trying to join these tables and order by timestamp in order to get a result such as

Would this just be a left join on LDTS? I am not sure how to get the resulting table such that the timestamps are ordered in this way and the columns that are not shared contain nulls if their values are not in the other table. When I try to do a full outer join, it looks like rows are duplicated for LDTS and LDTS becomes a singular column while the values for the other columns are all null. Thanks!

CodePudding user response：

Another one - similar to @Greg -

with cte(person_id, ldts) as
 (select person_id,ldts from table_1
 union all
 select person_id,ldts from table_2)
select t3.person_id, t1.ldts, t2.ldts, t2.currency from
 cte t3 left join table_1 t1
 on t3.person_id = t1.person_id
left join table_2 t2
 on t3.person_id = t2.person_id
order by t3.ldts;

CodePudding user response：

Getting the rows where the key in the other table is null reciprocally could be handled as a set-based issue:

select T1.PERSON_ID, T1.LDTS as T1_LDTS, T2.LDTS as T2_LDTS, CURRENCY  from TABLE_1 T1 left join TABLE_2 T2 on T1.LDTS = T2.LDTS
union
select T2.PERSON_ID, T1.LDTS as T1_LDTS, T2.LDTS as T2_LDTS, CURRENCY from TABLE_2 T2 left join TABLE_1 T1 on T1.LDTS = T2.LDTS
order by nvl(T1_LDTS, T2_LDTS)
;

In response to the question in the comments, if TABLE_2 does not have a PERSON_ID column, then simply specify that it's null:

select T1.PERSON_ID, T1.LDTS as T1_LDTS, T2.LDTS as T2_LDTS, CURRENCY  from TABLE_1 T1 left join TABLE_2 T2 on T1.LDTS = T2.LDTS
union
select NULL as PERSON_ID, T1.LDTS as T1_LDTS, T2.LDTS as T2_LDTS, CURRENCY from TABLE_2 T2 left join TABLE_1 T1 on T1.LDTS = T2.LDTS
order by nvl(T1_LDTS, T2_LDTS)
;