Difference Between Two Tables in MySQL

When validating a legacy-to-new data transfer, you may need to find selected row values that appear in one table but not the other. This method compares row values, not table schemas or every kind of change. Choose the columns that matter to the transfer check; the query can only detect differences in those selected values.

Find rows that differ between two MySQL tables

The approach combines the selected rows from both tables, groups identical selected tuples, then keeps groups that occur once. UNION ALL appends rows and preserves duplicate occurrences; it is not a join. COUNT(*) counts rows in each group, rather than testing similarity on its own. If a tuple appears once in each table, its group has a count of two. A tuple present in only one table has a count of one, provided the assumptions below hold.

Prepare comparable rows from both tables

Both SELECT statements must return the same number of compatible columns in the same order. Use matching data types where possible, and select the key plus every value you want to compare. For example, the query can be written as 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) AS combined GROUP BY employee_id, department, email HAVING COUNT(*) = 1 ORDER BY employee_id;. The derived table alias gives the combined result a name. Group by every selected column so that only rows equal across the entire selected tuple are counted together.

Find rows that occur in only one table

HAVING COUNT(*) = 1 filters the grouped rows after grouping. The output contains each selected tuple found exactly once in the combined input. ORDER BY employee_id makes the result easier to inspect; it does not affect which tuples match. Keep UNION ALL: plain UNION removes duplicate rows before grouping and can change the counts, hiding duplicate-related discrepancies.

The SQL query is intended for a MySQL server and is not executed by the available article example runtime. The available article example runtime executes this Python illustration for the supplied employee tuples; its output is shown below. It demonstrates the grouping idea but is not a substitute for running the SQL against your tables.

Run this illustration:

from collections import Counter

old_rows = [(12, "Sales", "maya@example.com"), (14, "TeleCom", "john_doe@example.com"), (15, "TeleCom", "jane@example.com")]
new_rows = [(12, "Sales", "maya@example.com"), (14, "TeleCom", "john_doe@example.com"), (15, "TeleCommunication", "johndoe@example.com")]
counts = Counter(old_rows + new_rows)
unmatched = sorted(row for row, count in counts.items() if count == 1)
for row in unmatched:
    print(*row, sep=" | ")

15 | TeleCom | jane@example.com
15 | TeleCommunication | johndoe@example.com

Understand the employee example

Assume employee_old_table and employee_new_table each contain at most one row per employee_id. Their selected columns are employee_id, department, and email. Employees 12 and 14 have identical selected values in both tables. Employee 15 has a changed department and email, so each version is an unmatched tuple. The grouped result therefore contains the two employee 15 tuples. Because the query groups every selected value, it does not pair old and new versions into a single change record.

Check assumptions and limits

The count-one interpretation relies on each selected tuple occurring at most once in each table. Duplicate rows can make a tuple’s group count two or more even if the same tuple is not represented once per table; counts alone do not identify which table supplied each row. In the employee example, the one-row-per-key assumption helps make the results interpretable, but duplicates or repeated keys require a more specific comparison design.

The result identifies unmatched selected tuples without labeling their source table. It also cannot find changes in columns omitted from the SELECT list, compare column definitions, or explain why a value changed. If you need to attribute a tuple to its source, include a source label in each SELECT and use a query designed to preserve that label; this changes the grouping behavior and is outside this simple count-one method. Confirm that the chosen columns and duplicate policy match the data-transfer check before treating the result as proof that two complete tables are identical.