Compare Two Tables

(show you how to compare two tables to find the unmatched records in MySQL)

How to Compare Two Tables in MySQL to Find Unmatched Records

When migrating data, you often need to compare two tables to ensure every record in one table has a match in the other. This ensures the migration is accurate.

For example, if you're migrating data from a legacy database to a new database with a different schema, you can compare the data in both tables to identify any unmatched records.

Let’s break this process into simple steps with examples:

Step 1: Combine Rows Using `UNION`

Use the UNION ALL statement to combine rows from both tables, selecting only the columns you want to compare.

Example:

sql
Copy code
SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2;

Here:

pk: Primary key column.
c1: Column to compare.

Step 2: Group and Compare Rows

Group the combined rows by primary key and the columns being compared. Use HAVING COUNT(*) to find unmatched records:

If the values match, the count will be 2.
If they don’t match, the count will be 1.

Example:

sql
Copy code
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 there are no unmatched records, this query returns an empty result.

Example: Comparing Two Tables

Step 1: Create Two Tables

sql
Copy code
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)
);

Step 2: Insert Data

Insert sample data into both tables:

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

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

Step 3: Compare the Tables

Compare id and title columns from both tables:

sql
Copy code
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;

If the data matches, the result will be empty.

Step 4: Add an Unmatched Record

Insert a new record into t2:

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

Step 5: Compare Again

Run the query again to find unmatched rows:

sql
Copy code
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;

Result:

id	title
4	new row 4

The unmatched record appears as expected.

Summary

Use UNION ALL to combine rows.
Group by the key and columns to compare.
Use HAVING COUNT(*) = 1 to identify unmatched records.

This method is simple and effective for comparing two tables in MySQL.