Compare Data in Two MySQL Tables

dbForge Studio for MySQL delivers the easiest way of comparing and synchronizing data between two databases. If you need to pinpoint and examine differences in data between source and target environments, deploy modified data to production, or simply migrate your data to a new database, Data Compare will be your best assistant.

Compare live MySQL/MariaDB databases and scripts folders
Inspect conveniently grouped and highlighted differences
Document differences in detailed comparison reports
Create comparison reports from the command line
Autogenerate error-free synchronization scripts
Migrate data to an empty database with a few clicks
Schedule and automate recurring comparison and sync operations

Introduction to Data Compare

This little handy tool will be helpful to everyone who needs to manage MySQL & MariaDB data with ease, including developers, data architects, DBAs, analysts, and DevOps engineers. Users of both tech and non-tech backgrounds will find the entire wizard-aided process fast and intuitive. Want proof? Watch the video and see Data Compare in action!

Compare MySQL data with an intuitive wizard

The Studio's smart New Data Comparison wizard will make your experience smooth from the very beginning. After you select a source and a target database, you have plenty of possible comparison options to configure. To turn them on, simply make sure that the corresponding checkboxes are selected. Moreover, once you get the options ready, you can save them to a comparison template for recurring operations.

Compare data between two databases - MySQL comparison wizard

Fine-tune your comparisons with custom mapping

On the next page, the wizard automatically maps tables and views having the same owner and name. In some situations, database objects can not be mapped automatically and that's where custom mapping becomes indispensable.

Mapping tables and views
You can include those tables and views that have not been mapped automatically by mapping them manually on the Mapping tab of the New Data Comparison wizard.

Mapping columns
It is also possible to map columns that have different names as well as columns of different data types.

You can unmap tables, views, and/or columns, if required.

MySQL compare data in two tables - Custom Mapping

View and manage comparison results

The comparison results are presented in a detailed document, with all the differences highlighted for easy examination.

Filter and sort objects in the results grid
Search data
Hide identical columns
Exclude objects from synchronization

MySQL synchronization tool - view comparison results

Export comparison reports

After running a comparison, you can export its results to a report in one of the following formats:

CSV
XLS
HTML

Comparison reports contain detailed descriptions of all differences in your data. Note that you can set up regular reporting by including the autogeneration of comparison reports in your command-line scripts.

MySQL compare data in two databases - Export the comparison report

Sync source MySQL data to a target database

One you click the green arrow, the Data Synchronization Wizard will open to walk you step by step through the deployment of changes in your data to the specified target database. The wizard's versatile options will make the entire process fast, smooth, and error-free.

Autogenerate a synchronization script
Add pre- and post-execution scripts to the script
Review the action plan
Check synchronization warnings

MySQL data diff tool - MySQL data sync

Automate comparison and synchronization tasks

Finally, it is worth noting that dbForge Studio for MySQL supports the automation of recurring data comparison and synchronization operations using the command-line interface.

Save MySQL data comparison and/or synchronization settings to a .bat file
Use Command Prompt or PowerShell to automate comparison and synchronization operations
Schedule the execution of .bat files with Windows Task Scheduler or similar scheduler applications

MySQL compare table data - Automate synchronization tasks

How to compare two tables
in MySQL

Data consistency is the key to database management. Comparing two tables allows you to find discrepancies between them. dbForge Studio will help you do it easily.

For example, imagine you want to compare a table called customer that exists in two databases, sakila and sakila_prod. For this, you need to do the following:

Go to Comparison > New Data Comparison.
Specify the required type, connection, and database in the Source and Target sections.
On the Mapping page, select the customer checkbox.
Finally, click Compare.

MySQL data diff tool - MySQL data sync

Find matching records

When you have several databases with similar data, the identification of matching records can help you find differences and, thus, validate the integrity of your data.

Let's consider a scenario where we need to retrieve data from the sakila_prod.customer table, but include only those records where customer_id exists in the sakila.customer table, and there is a matching record in terms of both customer_id and first_name. To do that, run the following query:

SELECT
  *
FROM sakila_prod.customer AS c
WHERE c.customer_id IN (SELECT
    c2.customer_id
  FROM sakila.customer AS c2)
AND EXISTS (SELECT
    *
  FROM sakila.customer AS c2
  WHERE c.customer_id = c2.customer_id
  AND c.first_name = c2.first_name);

As a result, the duplicate rows will be returned.

MySQL compare table data - Schedule and automate synchronization tasks

Find unmatching records

To keep data valid and accurate, you can also check databases for unmatching records. This is especially useful for data analysis and troubleshooting. It helps improve the overall reliability of your data management.

In order to detect non-matching records, we're going to use this statement based on specific conditions:

SELECT
  *
FROM sakila_prod.customer AS c
WHERE NOT EXISTS (SELECT
    *
  FROM sakila.customer
  AS c2
  WHERE c.customer_id = c2.customer_id
  AND c.first_name = c2.first_name);

As you can see, the query has obtained records from sakila_prod.customer where there is no matching record in terms of both customer_id and first_name in the sakila.customer table.

MySQL data diff tool - MySQL data sync

Spot missing records in a source database

Another way to ensure data consistency is to compare two identical tables in different databases and discover missing data in one of them. This helps you identify potential errors.

For instance, to get records from the sakila.customer table where there is no matching record in the sakila_prod.customer table based on the customer_id column, you can use the following statement:

SELECT
  *
FROM sakila.customer
AS c
WHERE NOT EXISTS (SELECT
    *
  FROM sakila_prod.customer AS c2
  WHERE c.customer_id = c2.customer_id);

This query returns the records from sakila.customer that do not have corresponding matches in the sakila_prod.customer table. In dbForge Studio for MySQL, there is a special tab for reviewing missing records. It's called Only in Source.

MySQL compare table data - Schedule and automate synchronization tasks

Detect absent records in a target table

We have listed enough methods for validating data in databases, but there is an additional one. You can find missing records not only in a source table but also in a target table.

To accomplish this, you can execute the following query:

SELECT
  *
FROM sakila_prod.customer
AS c
WHERE c.customer_id NOT IN (SELECT
    customer_id
  FROM sakila.customer);

With this statement, we retrieve records from the sakila_prod.customer table where the customer_id does not appear in the customer_id column of the sakila.customer table. There is a tab called Only in Target that displays such records. As the target table contains no records, you can see that this tab is empty, and the query has returned nothing.

MySQL data diff tool - MySQL data sync

Conclusion

dbForge Studio for MySQL has a bunch of powerful features for MySQL database synchronization, data export and import, code completion and formatting, test data generation, data analysis, database design, and much more. With the Studio, you get everything you might need in a single tool.

dbForge Studio for MySQL

Your ultimate IDE for MySQL development and management

Download

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Data comparison and synchronization

Automation via the command-line interface

Frequently Asked Questions

What are the common reasons for comparing two tables in MySQL?

There can be various cases. Let's briefly describe a few.

You need to quickly check whether there are any differences between two selected tables.
You need to validate changes in your data before deploying them from a development environment to production.
You need to migrate data to a target database, making sure nothing is lost in the process.

How to compare data between two tables in MySQL?

The easiest way to do that is to use Data Compare, an integrated feature of dbForge Studio for MySQL.

Go to Comparison > New Data Comparison to open the wizard.
Specify the required type, connection, and database in the Source and Target sections.
On the Options page, configure comparison options.
On the Mapping page, review the mapping.
Finally, click Compare to run the operation. The output will be presented in a detailed comparison document.

How can you find records that exist in one table but not in another?

Run a comparison and take a look at the results in the comparison document. There are tabs called Only in Source and Only in Target that display records that exist only in the source table and only in the target table, respectively.

How can you identify differences in specific columns between two tables?

You can view all differences in the columns of the source and the target side by side in the output comparison document. The differences are grouped into three tabs: Only in Source, Different, and Only in Target. One more tab, Identical, displays columns and records that contain no differences.

What MySQL tools are most efficient for large-scale table comparisons?

Regardless of the size of the databases and tables you need to compare, we suggest using the integrated Data Compare feature of dbForge Studio for MySQL. Large-scale comparisons may obviously take more time, yet nowhere else will you get such high performance. Moreover, the intuitive interface of the Studio's eventual comparison document will make reviewing easy even for inexperienced users.

How to find duplicates between two tables in MySQL?

Simply run a comparison of these two tables. Identical columns and records will be displayed on the Identical tab of the resulting comparison document.

What types of comparison reports can be generated by the tool, and in which formats?

You can generate comparison reports from the Comparison menu > Generate Comparison Report. The three available types of reports align with the three available formats - CSV, XLS, and HTML. Pick the corresponding format, configure additional options, if necessary, and click Generate. That's it.

Also note that you can automate the generation of comparison reports from the command line. You only need to specify the path and name of the report file alongside its format under the respective /report and /reportformat switches in your command-line script.