Mysql query to compare and find out missing data or rows from 2 tables which are same nature of tables

Some time requirements are

1. You need to find missing data in 2 tables.

2. For example, you have a TABLE blog on DEV and SERVER environments.

3.And you suspect that there is some missing data on DEV or SERVER.

4. Means data are not matching from the DEV database and the SERVER database, whether some have more data and some have less data.

5. But the problem is that you can't check manually because it is time-consuming, and indeed, you have Thousands of record mismatches, so it is impossible to check manually.

6. Then what are the solutions to check correctly and automatically?

7.An example of the SOLUTION is provided.

SELECT id,title


SELECT id, title FROM blog_server


SELECT id,title FROM blog


GROUP BY id, title

HAVING count(*) = 1


8. In the above MySQL query example

9. We have 2 tables, blog_server & blog

10. So what you will do is take server blog Table & Data and put it into your development server with a new name of blog_server, so that way you have both tables on the same server.

11. Then run the query, for example, as above.

12. Now I will share some screen shots to help you understand better in a visual way.


Query screen shot 


Table1 BLOG screen shot


Table2 BLOG_SERVER screen shot


Query output screen shot for mismatch data

   Share on Facebook

Page views:7234