We want to compare two tables to find all duplicates, as well as all unique records, in each table. These queries are typically used to verify that nothing was lost or changed, after a database move. There is an old, hard way to write these queries, as well as a new easy way to write them. Let's look at both! Assume two tables, with five records in each one. This is the old table

SELECT * FROM OLD_Table

This is the new table

SELECT * FROM NEW_Table

Let's start with finding all duplicates. The newer, easier to read, way of writing this query is this:

SELECT * FROM OLD_Table INTERSECT SELECT * FROM NEW_Table

The older, harder to read, way of writing this query is this:

SELECT * FROM OLD_Table t1 WHERE EXISTS (SELECT * FROM NEW_Table t2 WHERE t2.firstname = t1.firstname)

Next, we want to find all the records that are ONLY in the OLD table. New-style form of the query:

SELECT * FROM OLD_Table EXCEPT SELECT * FROM NEW_Table

And this is the old way of writing the same query:

SELECT * FROM OLD_Table t1 WHERE NOT EXISTS (SELECT * FROM NEW_Table t2 WHERE t2.firstname = t1.firstname)

Finally, we want to find all the records that are ONLY in the NEW table. New style query:

SELECT * FROM NEW_Table EXCEPT SELECT * FROM OLD_Table

...and the old-style form of the same query:

SELECT * FROM NEW_Table t1 WHERE NOT EXISTS (SELECT * FROM OLD_Table t2 WHERE t2.firstname = t1.firstname)