MySQL Compare Two Tables

Summary: in this tutorial, you will learn how to compare two tables to find the unmatched records.

In data migration, we often have to compare two tables and identify a record in one table that have no corresponding record in another table.

For example, we have a new database whose schema is different from the legacy database. Our task is to migrate all data from the legacy database to the new one and verify that the data were migrated correctly. In order to check the data, we have to compare two tables, one in the new database and one in the legacy database, and identity the unmatched records.

Suppose, we have two tables: t1 and t2. The following steps compare two tables and identify the unmatched records:

First, use the UNION statement to combine rows in both tables; include only the columns that needs to compare. The returned result set is used for the comparison.

Second, group the records based on the primary key and columns that need to compare. If the values in the columns that need to compare are identical, the COUNT (*) returns 2, otherwise the COUNT (*) returns 1. See the following query:

If values in the columns involved in the comparison are identical, no row returns.

MySQL compare two tables example

Let’s take a look at an example that simulate the steps above.

First, create 2 tables with a similar structure:

Second, insert some data into both t1 and t2 tables:

Third, compare values of id and title column of both tables:

No row returns because there is not unmatched records.

Fourth, insert a new row into the t2 table:

Fifth, execute the query to compare the values of title column in both tables again. The new row, which is the unmatched row, should return.

MySQL compare two tables example

In this tutorial, you have learned how to compare two tables based on specific columns to find the unmatched records.