How to compare two tables in SQL?

Question

Can anyone explain how to compare two tables in SQL?

Answer 1

We can use UNION ALL, MINUS, JOIN, NOT EXISTS to compare two tables in SQL. Here is the syntax to compare two tables using UNION ALL in SQL:

Select * from (

Select Id_pk, col_1, col_2...,col_n from table1, ‘Old_table’

Union all

Select Id_pk, col_1, col_2...,col_n from table2, 'New_table'

) cmpr order by Id_pk;

The above query returns all the rows of old and new tables so that we can compare the differences between two tables.

Here is the syntax to compare two tables using MINUS in SQL:

Select Id_pk, col_1, col_2...,col_n from table1

MINUS

Select Id_pk, col_1, col_2...,col_n from table2;

The above query returns all the records of table1 but not in the table to check how many mismatch records are there between the two tables.

Here is the syntax to compare two tables using JOIN in SQL:

Select

case when A.col_1 = B.col_1 then ‘Match’ else ‘Mismatch’ end as col_1_cmpr,

case when A.col_2 = B.col_2 then ‘Match’ else ‘Mismatch’ end as col_2_cmpr, …..

from table1 A

Join table2 B

on (A.id_pk = B.id_pk) ;

The above query joins the tables on primary key and case statements to check whether the columns are matching or not.

You can check out this SQL tutorial to learn using UNION ALL, MINUS, JOIN, and NOT EXISTS operators