compare two tables mysql

How to Compare Two Tables in MySQL

Sometimes you may need to compare two tables in MySQL to find matched records or unmatched records. Here’s the SQL query to compare two tables in MySQL.

 

How to Compare Two Tables in MySQL

Here are the steps to compare two tables in MySQL. There are different use cases to compare two tables in SQL. We will look at each of them one by one. Let’s say you have the following 2 tables orders(id, order_date, amount) and orders2(id, order_date, amount) that have 2 identical records.

mysql> create table orders(id int, order_date date, amount int);

mysql> insert into orders(id, order_date, amount)
     values(1,'2020-07-25',250),
     (2,'2020-07-26',350),
     (3,'2020-07-27',200),
     (4,'2020-07-28',150);

mysql> select * from orders;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
+------+------------+--------+

mysql> create table orders2(id int, order_date date, amount int);

mysql> insert into orders2(id, order_date, amount)
     values(3,'2020-07-27',200),
     (4,'2020-07-28',150),
     (5,'2020-07-29',250),
     (6,'2020-07-30',300);

mysql> select * from orders2;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
|    5 | 2020-07-29 |    250 |
|    6 | 2020-07-30 |    300 |
+------+------------+--------+

Bonus Read : How to Get Last One Month Data in MySQL

 

MySQL Compare Two Columns from different tables

Let’s say you only want to compare two columns (e.g id) from two different tables orders and orders2. Here’s the SQL query to compare two columns from different tables and select records that match.

mysql> select * from orders
       where id in
       (select id from orders2);
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
+------+------------+--------+

In the above query, we select records from orders whose id column value is present in the list of id column values obtained from orders2 using a subquery.

Similarly, if you want to compare two columns and select records that do not match then update the query above by adding a NOT keyword before IN, as shown below.

 

mysql> select * from orders
            where id NOT in
            (select id from orders2);
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
+------+------------+--------+

Bonus Read : How to Copy Table in MySQL

 

MySQL Compare Two tables to Find Matched Records

If you are looking to compare two tables and find matched records based on multiple columns, then here’s SQL query. Let’s say you want to find identical records by comparing multiple columns id, order_date, amount

First we do a UNION ALL of two tables to retain duplicate rows.

mysql> select id, order_date, amount
     from orders
     union all
     select id, order_date, amount
     from orders2;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
|    5 | 2020-07-29 |    250 |
|    6 | 2020-07-30 |    300 |
+------+------------+--------+

Next, we do a GROUP BY to count records by id, order_date and amount columns to find records with count>1, that is records that occur more than once. We use the above query as subquery.

mysql> select id, order_date, amount
       from (
           select id, order_date, amount
           from orders
           union all
           select id, order_date, amount
           from orders2)
       temp
       group by id, order_date, amount
       having count(*)>1;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
+------+------------+--------+

Bonus Read : How to Add NOT NULL constraint in MySQL

 

MySQL Compare Two Tables to Find unmatched records

Similarly, here’s how to compare two tables to find unmatched columns in MySQL. In the above query, instead of using count(*)>1, we use the condition count(*)=1, that is records that occur only once.

Here’s the SQL to compare two tables and find records without matches.

mysql> select id, order_date, amount
       from (
         select id, order_date, amount
         from orders
         union all
         select id, order_date, amount
         from orders2)
       temp
       group by id, order_date, amount
      having count(*)=1;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
|    5 | 2020-07-29 |    250 |
|    6 | 2020-07-30 |    300 |
+------+------------+--------+

Bonus Read : How to Add Default Constraint in MySQL

 

MySQL compare two tables from different databases

Similarly, if you want to compare two tables orders and orders2 from different databases db1 and db2 respectively, just prefix the database names before the table names with a dot(.)

Here’s the SQL query to compare two tables from different databases and get matched records.

mysql> select id, order_date, amount
       from (
           select id, order_date, amount
           from db1.orders
           union all
           select id, order_date, amount
           from db2.orders2)
       temp
       group by id, order_date, amount
       having count(*)>1;

And here’s the SQL query to compare two tables from different databases and get unmatched records.

 

mysql> select id, order_date, amount
       from (
           select id, order_date, amount
           from db1.orders
           union all
           select id, order_date, amount
           from db2.orders2)
       temp
       group by id, order_date, amount
       having count(*)>1;

Hopefully, now you can compare two tables in MySQL

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!