Oracle - difference or changes between two rows from two tables

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)

This SO answer provides a very efficient solution using Oracle to compare the results of 2 queries: Proving SQL query equivalency

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.

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);