compare the rows in two Tables and show difference

compare the rows in two Tables and show difference

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.

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2

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 pk

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) 
);

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

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

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

INSERT INTO t2(title,note)
SELECT title, 'data migration'
FROM t1;

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;

No row returns because there is not unmatched records.

Fourth, insert a new row into the t2 table:

INSERT INTO t2(title,note)
VALUES('new row 4','new');

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.

Reference:

http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx