Fastest way to identify differences between two tables?
SELECT term, crn, fee, level_codeFROM live_dataMINUSSELECT term, crn, fee, level_codeFROM historical_dataWhats on live but not in historical. Can then union to a reverse of this to get whats in historical but not live.
Simply:
SELECT collist FROM TABLE Aminus SELECT collist FROM TABLE BUNION ALLSELECT collist FROM TABLE Bminus SELECT collist FROM TABLE A;
You didn't mention how rows are uniquely identified, so I've assumed you also have an "id" column:
SELECT *FROM livetableWHERE (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).