Imagine you have two MySQL tables with the same structure. And your final task is to find gaps between the two. So, is there a difference? Or are they now the same? If they are the same, then you can go home. Your data migration task is complete.
To Compare Two Tables In MySQL, Use the MySQL Command Line Interface or GUI Tool For MySQL.
Whether you’re migrating data or need to compare two copies for other reasons, this article is for you. You can stop now if you’re searching for the MySQL code to compare. Your sample codes are waiting for you below. And even more, there are alternatives to MySQL code. There are GUI tools that do that. So, let’s begin.
Table of Contents
How To Compare Two Tables In MySQL
Depending on the requirements, there are different ways to compare tables.
Do you need to check for the existence of rows between a physical table and a temporary table in a script? Then, you can use IN or NOT IN in the WHERE clause. Or you can also use EXISTS or NOT EXISTS with a subquery. You may need the primary key or a combination of some columns. Another is using a LEFT or RIGHT JOIN and checking for NULL or NOT NULL. Whichever achieves the purpose more quickly is up to you.
But if you are migrating data between two MySQL databases, it’s a different story. You need to compare each column of the same table in the 2 databases. So, you need to combine the 2 and group them. With this, you’ll be using UNION ALL to combine the 2. And GROUP BY with a HAVING clause to group them. And if you want to find gaps, the HAVING clause needs to find the rows without a match.
All these will have examples later.
Using MySQL Command Line Interface (CLI) is a fast option if you know the exact SQL syntax to compare. Meanwhile, GUI tools may be your cup of tea. So, we will discuss both.
The following section will give examples of how to do it using MySQL CLI. Check this out to learn about the advantages of coding.
See Also: Compare Two Tables In MySQL: The Easy And Cool Guide
Method 1: Compare Two Tables Using the MySQL Command Line Interface
Using MySQL CLI offers a lightweight interface for running MySQL queries. Let’s have some examples to find matched and unmatched records.
Compare Two Tables Using IN and NOT IN Operators
Imagine you’re comparing two tables from different MySQL databases. You want to find out if they’re similarities and differences. You can test through their primary keys using IN and NOT IN. Use IN to find rows that match. And use NOT IN to test for rows it presents in the source but not in the target.
Below is an example:
mysql> SELECT id, last_name, first_name FROM testdb.students WHERE id NOT IN (SELECT id FROM testdb2.students);
The above code compares 2 tables with the same name but in different databases. It scans for student records in the testdb database that are not in the testdb2 database. You may use this technique if you compare live and a backup database. This is enough if you trust that the rest of the columns have the same values.
Alternatively, you may only need the count of rows, not the rows themselves. Here’s a slight variation:
mysql> SELECT COUNT(*) FROM testdb.students WHERE id NOT IN (SELECT id FROM testdb2.students);
But, if you need to know the number of rows from the source in the target, you can use IN instead.
mysql> SELECT COUNT(*) FROM students WHERE id IN (SELECT id FROM testdb2.students);
Compare Two Tables Using EXISTS and NOT EXISTS
We will use the same logic but different operators.
The EXISTS and NOT EXISTS are very straightforward. They test for the existence and non-existence of rows.
Here’s the query for the same purpose using NOT EXISTS instead of NOT IN:
mysql> SELECT COUNT(*) FROM students a
WHERE NOT EXISTS (SELECT id FROM testdb2.students WHERE id= a.id);
Aside from the operators’ differences, we added a table alias. And we used it to compare the id column of the first and second tables.
Note that the examples so far will need to scan the entire tables. So, this approach can be slow for huge tables.
Compare Two Tables Using LEFT JOIN
Let’s have the same purpose as the previous examples but using LEFT JOIN:
mysql> SELECT a.id, a.last_name, a.first_name
FROM students a
LEFT JOIN testdb2.students b on a.id = b.id
WHERE b.id IS NULL;
We are still comparing the same tables from two databases. But this time, we test for a NULL id. If there’s a result, you know that some rows in the first table are not in the second table.
After mastering the MySQL Command Line Interface for table comparison, it’s worth noting that these techniques are invaluable for comparing tables within a database and assessing data consistency across data warehouses, aiding in the validation and synchronization of large-scale data sets.
Compare Two Tables Using UNION ALL and GROUP BY
We’ve been testing existence and non-existence using primary keys only. But testing for keys only will ignore the other columns with different values. So, let’s have our next example using UNION ALL with GROUP BY and HAVING.
This will work for the newer and older versions of MySQL.
mysql> SELECT id, last_name, first_name, middle_name, age
FROM (
SELECT id, last_name, first_name, middle_name, age
FROM students a
UNION ALL
SELECT id, last_name, first_name, middle_name, age
FROM testdb2.students b) tmp_combined_tables
GROUP BY id, last_name, first_name, middle_name
HAVING COUNT(*) = 1;
First, we combine the two tables using UNION ALL. This will include duplicates. Then, the combination becomes a derived table. Then, we use GROUP BY all columns to drop the duplicates. Finally, the HAVING COUNT(*) = 1 will filter all rows without duplicates. When there’s a result, the two tables have different rows.
We won’t see a result since the 2 tables are exact copies. But to test for differences, let’s add a new row to testdb2.students. And rerun the above query. Here’s the INSERT statement:
INSERT INTO testdb2.students
(last_name, first_name, middle_name, age)
VALUES
(‘DOE’,’JOHN’,”,15);
Re-running the SELECT statement earlier will now have a 1-row result. See below using the MySQL CLI:
Compare Two Tables Using EXCEPT
The easiest way to compare with code is using EXCEPT. It has been available since MySQL 8.0.31. The concept is to return the rows from the first table that are not in the second table.
The 2 tables you’re comparing should have the same number of columns.
Here’s an example:
TABLE testdb2.students
EXCEPT
TABLE testdb.students;
The query below will work the same:
SELECT * FROM testdb2.students
EXCEPT
SELECT * FROM testdb.students;
The opposite of EXCEPT is INTERSECT which returns the common rows to both. These are very handy for comparing tables with the latest version of MySQL.
Method 2: Using GUI Tool For MySQL
Using GUI tools may offer some nice-to-have features in MySQL table comparisons. Let’s examine dbForge Studio for MySQL.
Using dbForge Studio for MySQL
Typing the commands in the dbForge Studio’s SQL window offers a better experience than MySQL CLI. It has Intellisense, syntax color coding, suggestions, and more. See a sample below:
But dbForge Studio for MySQL has a better table comparison using a wizard. Here are the steps:
- Click Comparison in the top menu.
- Click on New Data Comparison. Then the New Data Comparison window will appear. Select the source and then target the MySQL connection.
- Select the source and the target database.
- Click Next and change some options. Then select the table(s) you will compare. Click Compare.
The results will show you the
- number of identical records,
- number of different records,
- rows only in target, and rows only in the source.
After the comparison, you can also sync the source and target table(s). But this is optional.
See a sample below.
What we did here has the same purpose as what we did earlier using MySQL CLI.
Note: If you want to compare entire databases, use Schema Compare. And also see the differences in stored procedures, triggers, and more.
Using MySQL Workbench
You can compare tables in MySQL Workbench using SELECT statements. The same queries we did earlier will run in the query tab.
Queries you type here have Intellisense and suggestions too. However, the experience could be better with dbForge Studio. Check out the screenshot below:
But if you’re looking for a Data Comparison feature in the GUI, you’re out of luck. You can only compare schema or database structure using a wizard.
FAQS
How to compare two database tables?
To compare two database tables, you can use SQL queries with JOIN or UNION clauses to identify matching or differing records between the tables. Additionally, you may employ specialized tools or scripts to perform the comparison efficiently and display the results in a user-friendly format.
How to check if two tables are equal in MySQL?
In MySQL, you can check if two tables are equal by comparing their data using the 'EXCEPT' or 'NOT EXISTS' queries. If the questions return no results, the tables are identical. Another approach involves computing checksums or hash values for the tables and comparing them to confirm their equality.
How to compare the structure of two tables in SQL?
To compare the structure of two tables in SQL, you can use the 'SHOW CREATE TABLE' statement in MySQL or similar commands in other SQL databases. Compare the column names, data types, constraints, and indexes to identify differences between the tables' structures.
How to compare two tables in MySQL Workbench?
In MySQL Workbench, you can utilize the 'Table Data Compare' feature to compare two tables. Access it through the 'Database' menu, select 'Data Compare,' and choose the tables you wish to reach. The tool will display the differences, making it easy to identify discrepancies between the tables' data.
What is the MySQL table comparison tool?
The MySQL table comparison tool, is a software or utility that allows users to compare database tables, identify differences, and synchronize data. It simplifies finding discrepancies in data and structures between tables, making it useful for database administrators and developers.
What is the compare tool in MySQL?
The compare tool in MySQL is a feature provided by MySQL Workbench, a visual database design and administration tool. It allows users to compare database objects like tables, schemas, and data, identifying differences and facilitating synchronization. This feature streamlines managing database changes and maintaining consistency in MySQL databases.
Conclusion
You can compare two tables in MySQL to get matched and unmatched records using SQL code, but there are a lot of nuances to consider and check iteratively. And there are different ways to do it. Utilize operators like IN and NOT IN. Or you can use LEFT JOIN, UNION ALL with GROUP BY, and EXCEPT/INTERSECT. You can perform these comparisons using your MySQL CLI or MySQL GUI tool.
And then, we saw Data Comparison in dbForge Studio for MySQL. It’s an exceptional feature. Using a wizard, you can get the differences and similarities. Comparison in dbForge Studio is more visual, flexible, and, saves a lot of time for a developer or database administrator. This is the tip of the iceberg of the many features of this tool.
If you like it like me, why don’t you try it today? It won’t cost that much to check and see for yourself. So, download it and see a big difference.
