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

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

Answer 2

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