How to Compare two Tables in SQL efficiently - quick and easy method

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 have same number of columns in both the tables

Compare Two Tables using LEFT JOIN

SELECT * FROM .dbo.Table1 A
LEFT JOIN dbo.Table2 S
ON A.ID =B.ID;

You can easily derive from this result that, the sixth row that exists in the first table is missed from the second table. To synchronize the rows between the tables, you need to insert the new record to the second table manually

Compare Two Tables using Co-related Subquery

The below query will return rows form Table1 that are not present in Table 2

Select id_pk, col1, col2,col,… From table1 A
Where NOT EXISTS
( select 1 from table2 B
Where A.id_pk = B.id_pk
and A.col1 = B.col1
and A.col2 = B.col2
and…
);

You can use the any of these approaches to,

• Quickly compare two tables in Teradata
• Quickly compare two tables in Bigquery
• Quickly compare two tables in Snowflake
• It should also work for other relational databases like Oracle, SQL Server, MySQL, Redshift, Postgres, Netezza etc.

nVector

posted on 23 Apr 20