- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we compare data in two MySQL tables?
Sometimes we need to identify the unmatched data from two tables, especially in the case when data is migrated. It can be done by comparing the tables. Consider the example below in which we have two tables named ‘students’ and ‘student1’.
mysql> Select * from students; +--------+--------+----------+ | RollNo | Name | Subject | +--------+--------+----------+ | 100 | Gaurav | Computer | | 101 | Raman | History | | 102 | Somil | Computer | +--------+--------+----------+ 3 rows in set (0.00 sec) mysql> select * from student1; +--------+--------+----------+ | RollNo | Name | Subject | +--------+--------+----------+ | 100 | Gaurav | Computer | | 101 | Raman | History | | 102 | Somil | Computer | | 103 | Rahul | DBMS | | 104 | Aarav | History | +--------+--------+----------+ 5 rows in set (0.00 sec)
Now, with the help of query below, we can compare these tables and get the unmatched rows as the result set.
mysql> Select RollNo,Name,Subject from(select RollNo,Name,Subject from students union all select RollNo,Name,Subject from Student1)as std GROUP BY RollNo,Name,Subject HAVING Count(*) = 1 ORDER BY RollNo; +--------+-------+---------+ | RollNo | Name | Subject | +--------+-------+---------+ | 103 | Rahul | DBMS | | 104 | Aarav | History | +--------+-------+---------+ 1 rows in set (0.02 sec)
- Related Articles
- How can we upload data into MySQL tables by using mysqlimport?
- How can we upload data into multiple MySQL tables by using mysqlimport?
- Compare two tables and return missing ids in MySQL?
- How can we create a MySQL view by using data from multiple tables?
- How can we see MySQL temporary tables in the list of tables?
- How can I merge two MySQL tables?
- How can we access tables through MySQL stored procedures?
- What are MySQL Temporary Tables? How can we create them?
- Can we use stored procedure to insert records into two tables at once in MySQL?
- Can we compare numbers in a MySQL varchar field?
- When are two tables connected with MySQL FOREIGN KEY then how can we say that the integrity of data is maintained in child table?
- Compare two tables and return missing ids using MySQL LEFT OUTER JOIN
- How to move data between two tables with columns in different MySQL databases?
- How can we import CSV files into MySQL tables by using mysqlimport?
- How can you perform inner join on two tables using MySQL in Python?

Advertisements