I have two tables as follows
TABLE_1
| PERSON_ID | LDTS |
|---|---|
| 45 | 2022-03-03 15:41:05.685 |
| 72 | 2022-03-03 15:42:08.203 |
| 15 | 2022-06-08 21:57:07.909 |
| 36 | 2022-06-28 21:58:43.558 |
TABLE_2
| PERSON_ID | LDTS | CURRENCY |
|---|---|---|
| 34 | 2022-03-03 15:00:21.814 | US |
| 28 | 2022-03-03 15:02:05.963 | CA |
| 52 | 2022-03-03 15:02:05.963 | US |
| 10 | 2022-06-08 14:40:13.762 | US |
| 11 | 2022-06-08 14:40:13.762 | CA |
| 19 | 2022-06-14 16:10:19.005 | US |
I am trying to join these tables and order by timestamp in order to get a result such as
| PERSON_ID | TABLE_1.LDTS | TABLE_2.LDTS | CURRENCY |
|---|---|---|---|
| 34 | NULL | 2022-03-03 15:00:21.814 | US |
| 28 | NULL | 2022-03-03 15:02:05.963 | CA |
| 52 | NULL | 2022-03-03 15:02:05.963 | US |
| 45 | 2022-03-03 15:41:05.685 | NULL | NULL |
| 72 | 2022-03-03 15:42:08.203 | NULL | NULL |
| 10 | NULL | 2022-06-08 14:40:13.762 | US |
| 11 | NULL | 2022-06-08 14:40:13.762 | CA |
| 15 | 2022-06-08 21:57:07.909 | NULL | NULL |
| 19 | NULL | 2022-06-14 16:10:19.005 | US |
| 36 | 2022-06-28 21:58:43.558 | NULL | NULL |
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!
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)
;