oracle find difference between 2 tables

Question

I have 2 tables that are the same structure. One is a temp one and the other is a prod one. The entire data set gets loaded each time and sometimes this dataset will have deleted records from the prior datasets. I load the dataset into temp table first and if any records were deleted I want to deleted them from the prod table also.

So how can I find the records that exist in prod but not in temp? I tried outer join but it doesn't seem to be working. It's returning all the records from the table in the left or right depending on doing left or right outer join.

I then also want to delete those records in the prod table.

Answer 1

One way would be to use the MINUS operator

SELECT * FROM table1
MINUS
SELECT * FROM table2

will show all the rows in table1 that do not have an exact match in table2 (you can obviously specify a smaller column list if you are only interested in determining whether a particular key exists in both tables).

Another would be to use a NOT EXISTS

SELECT *
  FROM table1 t1
 WHERE NOT EXISTS( SELECT 1
                     FROM table2 t2
                    WHERE t1.some_key = t2.some_key )