Sql query to compare two tables and find records with matches

You have two tables in same database or server that you wish to compare, and check if any changes in the column values or see if any row is missing in either of tables.

Here are few ways to do it

• Compare Two Tables using UNION ALL Clause
• Compare Two Tables using MINUS Clause
• Compare Two Tables using LEFT JOIN
• Compare Two Tables using Co-related Subquery

Compare Two Tables using UNION ALL Clause

UNION ALL lets you quickly check what are the data missing or changed in either table.

With easy visual examination you can find out the differences

Select * from (
Select pk_col, col1, col2...,coln from table1, ‘Old_table’
Union all
Select pk_col, col1, col2...,coln from table2, 'New_table'
) Temp order by pk_col;

Compare Two Tables using MINUS Clause

The MINUS Clause returns all rows in table 1 that do not exist or changed in the other table

Select Id_pk, col1, col2...,coln from table1
MINUS
Select Id_pk, col1, col2...,coln from table2;

NOTE: While using MINUS Clause / UNION ALL Clause you will need to h

how to check table size in sql server

how to check table structure in sql