Compare Two Tables in MySQL

Many times you may have faced the situation where you wanted to compare two tables and get unmatched records out of them. This is a common practice when database migration is done. So in this tutorial, we will learn how to compare two tables to find unmatched records. So, let’s get started!

Prerequisites

In MySQL, as stated earlier, two tables are compared when data migration is done to find data mismatches. For example, the new database may have a different schema than the old/legacy database. So, we must cross-check if the migrated data is exactly the same as the previous data.

In this case, we can compare two tables to check the matching data and unmatching data from the older database and the new database. For this, we can make use of the MySQL terminologies and functions such as joins, unions, aggregate functions, etc.

Let’s see now how we can compare two tables and find unmatched data.

To compare two tables, we can use nested queries or the MySQL joins as well. However, we will use the UNION operator to find the unmatched values from the two tables.

Our approach will be as follows –

First, find the union of two tables.
Second, we will use the GROUP BY clause to group the rows together.
Third, the groups which have a count 1 will be selected using the HAVING clause.
Finally, we will order the rows by using the ORDER BY clause and display them.

This is our approach to solving the problem. Let’s now start writing code.

First, we will create two tables of the same description. Note that, we have added a third column in the second table so that the newly added row can be easily distinguished.

CREATE TABLE table1(
id int auto_increment primary key,
title varchar(255)	
);

CREATE TABLE table2(
id int auto_increment primary key,
title varchar(255),
entry varchar(255)
);Code language: SQL (Structured Query Language) (sql)

Tables are created successfully. Let’s insert exactly the same data into them.

INSERT INTO table1(title)
VALUES('row 1'),('row 2'),('row 3');

INSERT INTO table2(title,entry)
SELECT title, 'data migration'
FROM table1;Code language: SQL (Structured Query Language) (sql)

Let’s now write a SQL query to find the unmatched data by comparing them.

SELECT id,title
FROM (
SELECT id, title FROM table1
UNION ALL
SELECT id,title FROM table2
)newTable
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;Code language: SQL (Structured Query Language) (sql)

First, have a look inside the rounded brackets.

We have combined the data of two tables and given an alias “newTable” to the newly derived table.
Now, the first SELECT statement will select the id and the title from the derived table that we mentioned above. We have used the GROUP BY clause to group the rows by the id and title.
Next, we have used the HAVING clause where we will check if the groups created by GROUP BY are the set of only single instruction. This way, we will find the unmatched records because all the matched records are groups of two and the HAVING clause will filter the records which are groups of only one.
Using the ORDER BY clause, we sort the records by the id. Let’s now check the output of the above query.

Note that, both tables contain exactly the same data as of now in the id and title column. So, we should get an empty result.

As you can see, we have received an empty result set.

Let’s add a new row in the second table. So that, when we execute the above query, we will get a non-empty result.

INSERT INTO table2(title,entry)
VALUES('new row 4','new row');Code language: SQL (Structured Query Language) (sql)

Let’s now execute the query that we created above to compare and find unmatched records.

As you can see, the new record is not in the first table so we received that new record.

This way, we can find the unmatched records in MySQL by comparing two tables.

Conclusion

In this tutorial, we have learned how to compare two tables on specific columns to find unmatched records. We have seen only one method. However, you may try different methods using JOINS and other aggregate functions to find the unmatched records. That’s all in this tutorial. See you in the next tutorial!