Difference Between Two Tables in MySQL

In today’s post, we’ll learn how to find the difference between two tables in MySQL.

Difference Between Two Tables in MySQL

We frequently need to compare two tables to find records in one table that don’t have matching records in the other.

For instance, our new database has a different schema than the legacy database. It is our responsibility to transfer all data from the legacy database to the new one and to ensure that the transfer is successful.

To verify the data, we must compare two tables—one from the legacy database and one from the new database—and find any mismatched records.

Consider a situation where we need to compare the columns of two tables, table_1 and table_2. The following steps compare two tables to find the records that do not match.

1. First, join rows from both tables using the UNION statement; only include the necessary columns. The comparison is performed using the returned result set.

   SELECT table_1.primary_key, table_1.column_1, table_1.column2
   FROM table_1
   UNION ALL
   SELECT table_2.primary_key, table_2.column_1, table_2.column2
   FROM table_2;
   

2. The records should then be grouped according to the primary key and the columns that require comparison. The COUNT(*) function produces 2 if the values in the columns that need to be compared are similar; otherwise, it returns 1.

   SELECT primary_key, column_1, column_2
   FROM
    (
      SELECT table_1.primary_key, table_1.column_1
      FROM table_1
      UNION ALL
      SELECT table_2.primary_key, table_2.column_1
      FROM table_2
   )  temporary_table
   GROUP BY primary_key, column_1, column_2
   HAVING COUNT(*) = 1
   ORDER BY primary_key
   

Consider the following example to help you better understand the prior idea.

SELECT employee_id, department, email
FROM (
    SELECT employee_id, department, email FROM employee_old_table
    UNION ALL
    SELECT employee_id,department, email FROM employee_new_table
) temporary_table
GROUP BY employee_id, department, email
HAVING count(*) = 1
ORDER BY employee_id;

The example above compares the old and new employee tables, and the results are stored in a temporary_table. Following the return of the result, we are then grouping by employee_id, department name, and email id.

If the values in the columns that must be compared are identical, the COUNT(*) method returns 2; otherwise, it returns 1. Therefore, we use the HAVING clause to check for the unique value.

Finally, we print the outcome in ascending employee_id order.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

+-------------+--------------------+----------------------+
| employee_id |     department     |         email        |
+-------------+--------------------+----------------------+
| 14          | TeleCom            | john_doe@example.com |
| 15          | TeleCommunication  | johndoe@example.com  |
+-------------+--------------------+----------------------+