{"id":489,"date":"2009-12-12T12:47:55","date_gmt":"2009-12-12T12:47:55","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=489"},"modified":"2023-12-29T05:30:09","modified_gmt":"2023-12-29T12:30:09","slug":"mysql-compare-two-tables","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-compare-two-tables\/","title":{"rendered":"MySQL Compare Two Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to compare two tables to find the unmatched records.<\/p>\n\n\n\n

In data migration, it is common to compare two tables to identify a record in one table that has no corresponding entries in another table.<\/p>\n\n\n\n

For example, consider a scenario where a new database has a schema that is different from the legacy database. The objective is to migrate all data from the legacy database to the new one while ensuring the accuracy of the migration.<\/p>\n\n\n\n

To validate the data, we have to compare two tables, one in the new database and one in the legacy database, and identify the records that do not have a match.<\/p>\n\n\n\n

Let’s take two tables t1<\/code> and t2<\/code> as an example. <\/p>\n\n\n\n

The following steps outline the process of comparing two tables and identifying unmatched records:<\/p>\n\n\n\n

First, use the UNION <\/a>statement to combine rows in both tables; include only the columns that you want to compare. <\/p>\n\n\n

SELECT<\/span> \n  t1.pk, \n  t1.c1 \nFROM<\/span> \n  t1 \nUNION<\/span> ALL<\/span> \nSELECT<\/span> \n  t2.pk, \n  t2.c1 \nFROM<\/span> \n  t2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, t1.pk and t2.pk are the primary key columns of the tables, and t1.c1 and t2.c2 are the columns that you want to compare.<\/p>\n\n\n\n
If the values in the columns that you want to compare are identical, the HAVING COUNT(*)<\/code> returns 2 otherwise it returns 1:<\/p>\n\n\n
SELECT<\/span> pk, c1\nFROM<\/span>\n (\n   SELECT<\/span> t1.pk, t1.c1\n   FROM<\/span> t1\n   UNION<\/span> ALL<\/span>\n   SELECT<\/span> t2.pk, t2.c1\n   FROM<\/span> t2\n)  t\nGROUP<\/span> BY<\/span> pk, c1\nHAVING<\/span> COUNT<\/span>(*) = 1<\/span>\nORDER<\/span> BY<\/span> pk<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If values in the columns involved in the comparison are identical,  the query returns no row.<\/p>\n\n\n\n
Comparing two tables example<\/h2>\n\n\n\n
First, create two new tables called t1<\/code> and t2<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> t1(\n  id<\/span> int<\/span> auto_increment primary key<\/span>, \n  title varchar<\/span>(255<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> t2(\n  id<\/span> int<\/span> auto_increment primary key<\/span>, \n  title varchar<\/span>(255<\/span>), \n  note varchar<\/span>(255<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert data into t1<\/code> and t2<\/code> tables:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> t1(title) \nVALUES<\/span> \n  ('row 1'<\/span>), \n  ('row 2'<\/span>), \n  ('row 3'<\/span>);\n  \nINSERT<\/span> INTO<\/span> t2(title, note) \nSELECT<\/span> \n  title, \n  'data migration'<\/span> \nFROM<\/span> \n  t1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, compare the values of the id<\/code> and title<\/code> column of both tables:<\/p>\n\n\n
SELECT<\/span> \n  id<\/span>, \n  title \nFROM<\/span> \n  (\n    SELECT<\/span> \n      id<\/span>, \n      title \n    FROM<\/span> \n      t1 \n    UNION<\/span> ALL<\/span> \n    SELECT<\/span> \n      id<\/span>, \n      title \n    FROM<\/span> \n      t2\n  ) tbl \nGROUP<\/span> BY<\/span> \n  id<\/span>, \n  title \nHAVING<\/span> \n  count<\/span>(*) = 1<\/span> \nORDER<\/span> BY<\/span> \n  id<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Empty set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns no row because there are no unmatched records.<\/p>\n\n\n\n
Fourth, insert a new row into the t2 table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> t2(title, note) \nVALUES<\/span> \n  ('new row 4'<\/span>, 'new'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, execute the query to compare the values of the title<\/code> column in both tables again: <\/p>\n\n\n
SELECT \n  id, \n  title \nFROM \n  (\n    SELECT \n      id, \n      title \n    FROM \n      t1 \n    UNION ALL \n    SELECT \n      id, \n      title \n    FROM \n      t2\n  ) tbl \nGROUP BY \n  id, \n  title \nHAVING \n  count(*) = 1 \nORDER BY \n  id;\n<\/code><\/span><\/pre>\n\n\n
The new row, which is the unmatched row, should return:<\/p>\n\n\n
+----+-----------+\r\n| id | title     |\r\n+----+-----------+\r\n|  4<\/span> | new<\/span> row 4<\/span> |\r\n+----+-----------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the GROUP BY<\/code> and HAVING COUNT<\/code> clauses to compare the contents of two tables to find the unmatched records.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, group the rows based on the primary key<\/a> and columns that you want to compare. <\/p>\n\n\n\n