How can we compare data from two MySQL tables?-Mysql Tutorial-php.cn

How can we compare data from two MySQL tables?

WBOY

Release： 2023-09-08 12:45:02

forward

638 people have browsed it

我们如何比较两个 MySQL 表中的数据？

Sometimes we need to find out the mismatched data in two tables, especially in the case of data migration. This can be done by comparing tables. Consider the following example where 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)

Copy after login

Now, with the help of the below query, we can compare these tables and get the non-matching rows as a 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)

Copy after login

The above is the detailed content of How can we compare data from two MySQL tables?. For more information, please follow other related articles on the PHP Chinese website!