Compare TWO tables with same schema for data differences

Question

I am working on a typical problem where I need to compare TWO tables with exactly same schema for data differences. Assume database as MS SQL or ORACLE.

To be more precise here is what I am trying to achieve:

1. I have a table ORG with some data
2. I am creating a copy of table ORG as BACKUP
3. Now I want to update some SPECIFIC columns in some SPECIFIC rows of the table ORG.

What is the EASIEST and EFFICIENT way to find the difference between tables BACKUP and ORG?

I see few options like using UNIONS, PIVOT, UNPIVOT, etc.. But I am confused and need some guidance on best way to proceed.

Thanks,

Answer 1

You could use the SET operators MINUS/INTERSECT depending on what you want, the difference or the match between the rows in the two tables.

To get the difference, use MINUS:

SELECT <here_goes_your_column_list> FROM org
MINUS
SELECT <here_goes_your_column_list> FROM backup

To get the match, use INTERSECT:

SELECT <here_goes_your_column_list> FROM org
INTERSECT
SELECT <here_goes_your_column_list> FROM backup

See more details in documentation.