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 to 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.
To check the data, we have to compare two tables, one in the new database and one in the legacy database and identify 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 need to compare. The returned result set is used for the comparison.
SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2Code language: SQL (Structured Query Language) (sql)
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:
SELECT pk, c1
FROM
(
SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2
) t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pkCode language: SQL (Structured Query Language) (sql)
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:
CREATE TABLE t1(
id int auto_increment primary key,
title varchar(255)
);id int auto_increment primary key,
title varchar(255),
note varchar(255)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert some data into both t1 and t2 tables:
INSERT INTO t1(title)
VALUES('row 1'),('row 2'),('row 3');SELECT title, ‘data migration’
FROM t1;
Code language: SQL (Structured Query Language) (sql)
Third, compare values of id and title column of both tables:
SELECT id,title
FROM (
SELECT id, title FROM t1
UNION ALL
SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;Code language: SQL (Structured Query Language) (sql)
No row returns because there are not unmatched records.
Fourth, insert a new row into the t2 table:
INSERT INTO t2(title,note)
VALUES('new row 4','new');Code language: SQL (Structured Query Language) (sql)
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.
In this tutorial, you have learned how to compare two tables based on specific columns to find unmatched records.