我们如何模拟 MySQL INTERSECT 查询?
mysqlmysqli database
由于我们无法在 MySQL 中使用 INTERSECT 查询，我们将使用 IN 运算符来模拟 INTERSECT 查询。可以借助以下示例 − 来理解它
示例
在此示例中，我们有两个表，即 Student_detail 和 Student_info，它们具有以下数据 −
mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 150 | Rajesh | Jaipur | Yoga | | 160 | Pradeep | Kochi | Hindi | +-----------+---------+------------+------------+ 7 rows in set (0.00 sec) mysql> Select * from Student_info; +-----------+-----------+------------+-------------+ | studentid | Name | Address | Subject | +-----------+-----------+------------+-------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 165 | Abhimanyu | Calcutta | Electronics | +-----------+-----------+------------+-------------+ 6 rows in set (0.00 sec)
现在，以下使用 IN 运算符的查询将模拟 INTERSECT 以返回两个表中都存在的所有 ‘studentid’ 值 −
mysql> Select Student_detail.studentid FROM Student_detail WHERE student_detail.studentid IN(SELECT Student_info.studentid FROM Student_info); +-----------+ | studentid | +-----------+ | 101 | | 105 | | 130 | | 132 | | 133 | +-----------+ 5 rows in set (0.06 sec)
