JIYIK CN >

In this article, we will learn how to find the difference between two tables in MySQL.

The difference between two tables in MySQL

We often need to compare two tables to find records in one table that have no matching records in the other table.

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

To validate the data, we have to compare two tables—one from the old database and one from the new database—and find any records that don't match.

Consider a situation where we need to compare the columns of two tables table_1 and table_2. The following steps compare the two tables to find the mismatching records.

First, use the UNION statement to join the rows from the two tables; include only the necessary columns. Use the returned result set to perform the comparison.

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;

The records should then be grouped based on the primary key and the columns that need to be compared. If the values ​​in the columns that need to be compared are similar, COUNT(*)the function produces 2; 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 previous 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;

In the above example, the old and new employee tables are compared and the results are stored in a temporary table. After the results are returned, we will group them by employee_id, department name and email id.

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

Finally, we print the results in ascending order by employee_id.

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

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