I have historical data stored in a historical table, and current table in different tables. When I get similar data from each table, how do I join the data, but remove items from the historical data, if we have more recent, current data, when I join the data?

example:

Historical found:

ID   LastReboot1  LastReboot2
4622 11-09-2022   11-09-2022
0462 21-09-2021   21-09-2021
0099 19-10-2021   19-10-2021

Current found:
4622 18-09-2022   18-09-2022
0462 20-09-2022   20-09-2022

I figure I can do a join between the 2 queries, but wouldn't that keep the duplicated data? So I'd wind up with this:

ID   LastReboot1  LastReboot2
4622 11-09-2022   11-09-2022
4622 18-09-2022   18-09-2022
0462 20-09-2022   20-09-2022
0462 21-09-2021   21-09-2021
0099 19-10-2021   19-10-2021

I want it to wind up with this (keeping current id's with newer data, but bringing historical data where there isn't current data):

ID   LastReboot1  LastReboot2
4622 18-09-2022   18-09-2022
0462 20-09-2022   20-09-2022
0099 19-10-2021   19-10-2021

I don't think that would be a join. I appreciate help on this. I know it's probably basic knowledge for some of you experts out there. I tried googling it, but I'm not finding the anser to what I need.

CodePudding user response：

You can use UNION ALL to combine the two tables and use NOT EXISTS to filter out the historical rows that are also current rows:

SELECT *
FROM   current
UNION ALL
SELECT *
FROM   historical h
WHERE  NOT EXISTS (SELECT 1 FROM current c WHERE h.id = c.id)

Which, for the sample data:

CREATE TABLE Historical (ID, LastReboot1, LastReboot2) AS
SELECT '4622', DATE '2022-09-11', DATE '2022-09-11' FROM DUAL UNION ALL
SELECT '0462', DATE '2021-09-21', DATE '2021-09-21' FROM DUAL UNION ALL
SELECT '0099', DATE '2021-10-19', DATE '2021-10-19' FROM DUAL;

CREATE TABLE "CURRENT" (ID, LastReboot1, LastReboot2) AS
SELECT '4622', DATE '2022-09-18', DATE '2022-09-18' FROM DUAL UNION ALL
SELECT '0462', DATE '2022-09-20', DATE '2022-09-20' FROM DUAL;

Outputs:

fiddle