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
Back