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)

Prabhas

Updated on 14-Feb-2020 11:09:29

• Related Questions & Answers
• How can we upload data into MySQL tables by using mysqlimport?
• How can I merge two MySQL tables?
• Compare two tables and return missing ids in MySQL?
• How can we upload data into multiple MySQL tables by using mysqlimport?
• How can we see MySQL temporary tables in the list of tables?
• How can we create a MySQL view by using data from multiple tables?
• How can we access tables through MySQL stored procedures?
• What are MySQL Temporary Tables? How can we create them?
• Can we compare numbers in a MySQL varchar field?
• MySQL join two tables?
• Compare two tables and return missing ids using MySQL LEFT OUTER JOIN
• How do we compare two arrays in Java?
• How do we compare two lists in Python?
• How do we compare two tuples in Python?
• How do we compare two dictionaries in Python?