Fastest way to identify differences between two tables?

Question

I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this...

For instance, let's say my live table is made up of these columns:

• Term
• CRN
• Fee
• Level Code

My archive table would have the same columns, but also have an archive date so I can see what values the live table had at a given date.

Now... How would I write a query to ensure that the values for the live table are the same as the most recent entries in the archive table?

PS I'd prefer to handle this in SQL, but PL/SQL is also an option if it's faster.

Answer 1

SELECT term, crn, fee, level_code
FROM live_data
MINUS
SELECT term, crn, fee, level_code
FROM historical_data

Whats on live but not in historical. Can then union to a reverse of this to get whats in historical but not live.

Answer 2

Simply:

SELECT collist
  FROM TABLE A
minus 
SELECT collist
  FROM TABLE B
UNION ALL
SELECT collist
  FROM TABLE B
minus 
SELECT collist
  FROM TABLE A;

Answer 3

You didn't mention how rows are uniquely identified, so I've assumed you also have an "id" column:

SELECT *
FROM livetable
WHERE (term, crn, fee, levelcode) NOT IN (
   SELECT FIRST_VALUE(term) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(crn) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(fee) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(levelcode) OVER (ORDER BY archivedate DESC)
   FROM   archivetable
   WHERE  livetable.id = archivetable.id
);

Note: This query doesn't take NULLS into account - if any of the columns are nullable you can add suitable logic (e.g. NVL each column to some "impossible" value).