Compare Data in Two MySQL Tables

Handy Data Comparison Wizard for MySQL data

Easily customizable Data Comparison wizard makes it possible to effortlessly perform MySQL data comparison tasks even for non-experienced users. More than that, the dbForge MySQL data compare tool allows configuring the data comparison to fit the project requirements and saving the settings for further use.

Rich comparison options

• Include or exclude views and tables by mask
• Ignore views, tables, and\or columns by mask
• Round float types
• Ignore leading and trailing spaces
• Ignore case

View and manage the comparison results

dbForge MySQL data diff tool provides a comprehensive display of data differences and boasts a set of options for effective management of the comparison results.

• Filter and sort objects in the result grid
• Exclude objects from synchronization
• Search across data
• Hide identical columns
• Display the row differences
• Show the data differences

MySQL data synchronization

The user-friendly Data Synchronization wizard will walk you through the process of configuring data diffs deployment. The wizard's options are bound to make the process of MySQL data synchronization smooth and error-free.

• Generate the sync script for deployment or review
• Limit bulk size
• Add scripts to be executed before and\or after the deployment
• Show the action plan
• Expose synchronization warnings

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. In dbForge Studio for MySQL, you can easily perform this operation.

For example, imagine that you want to compare the customer table that exists in both the sakila and sakila_prod databases. For this, you need to:

1. Click Comparison > New Data Comparison.
2. Specify the required type, connection, and database in the Source and Target sections.
3. Click Mapping and select the customer checkbox.
4. Finally, click Compare.

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 database and 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.

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 with a particular subquery:

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

With the 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 the Only in Target tab in dbForge Studio for MySQL for displaying such records. As the target table contains no records, you can see this tab empty and the query has returned nothing.