MySQL: Compare two similar tables to find unmatched records

Sometimes you have two tables with similar structures and want to compare them. One could be the table in an older schema on an old server and the other one the newer schema on a new server. Let’s say you move data from one server to the other and want to check the data.

If you just want to compare two tables only once, you can go for a non-generic approach. Let’s assume that you have two tables (table A and table B) with a primary key called primary_key and two other columns (column1 and column2). What you want to get are:

keys present in A but not in B

keys present in B but not in A

keys present in both A and B but where the other columns differ

The first part can be fetched like this:

SELECT A.primary_key FROM A LEFT JOIN B ON A.primary_key=B.primary_key WHERE B.primary_key IS NULL

The second part is basically the same but switching A and B:

SELECT B.primary_key FROM B LEFT JOIN A ON B.primary_key=A.primary_key WHERE A.primary_key IS NULL

The third part just involves an inner join and checking the other columns:

4 thoughts on “MySQL: Compare two similar tables to find unmatched records”

Nice post.
Of course if you have a more frequent use case a tool might prove useful. For example the company I work for, Red Gate, does schema and data comparison tools for MySQL (Windows only): http://www.red-gate.com/products/mysql/

In order to compare tables in databases across servers, you’ll need to use federated tables. Those are similar to Oracle’s link tables or Sybase’s proxy tables. They are local representation of a remote table. I’ll try to soon write an article with details on how this is done.

Hi, Thanks for the post
I want to know what is the expected time taken when the two tables to be compared each have roughly 500,000 entries and there are 70 columns in each of these 2 tables which need to be verified.
I need to know this to estimate the time taken for such testing.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.OkRead more