Oracle - difference or changes between two rows from two tables

Question

I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column.

How is this possible in pl/sql, oracle? (I did code something similar using checksum in T-SQL but not sure how to do in pl/sql)

Answer 1

You want an exclusive-difference query, which is is essentially an (A-B) U (B-A) query:

(SELECT * FROM TODAY_TABLE
MINUS
SELECT * FROM YESTERDAY_TABLE)
UNION ALL
(SELECT * FROM YESTERDAY_TABLE
MINUS
SELECT * FROM TODAY_TABLE)

This query can also be easily improved to also show which records are inserts, deletes, and changes using an additional calculated column.

Answer 2

This SO answer provides a very efficient solution using Oracle to compare the results of 2 queries: http://stackoverflow.com/questions/56895/proving-sql-query-equivalency/93489#93489

Answer 3

Hi Rahuls,

As Dougman posted one of the most efficient way to compare two data sets is to GROUP them. I usually use a query like this to compare two tables:

SELECT MIN(which), COUNT(*), pk, col1, col2, col3, col4
  FROM (SELECT 'old data' which, pk, col1, col2, col3, col4
           FROM t
         UNION ALL
         SELECT 'new data' which, pk, col1, col2, col3, col4 FROM t_new)
 GROUP BY pk, col1, col2, col3, col4
HAVING COUNT(*) != 2
 ORDER BY pk, MIN(which);