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

Published on 22-Feb-2018 12:24:02