我们如何模拟具有WHERE子句的MySQL INTERSECT查询？
由于我们无法在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运算符和WHERE子句的查询将模拟INTERSECT以返回两个表中都存在的所有大于130的“studentid”值-
mysql> Select Student_detail.studentid FROM Student_detail WHERE student_detail.studentid >130 AND student_detail.studentid IN(SELECT Student_info.studentid FROM Student_info WHERE Student_detail.studentid > 0); +-----------+ | studentid | +-----------+ | 132 | | 133 | +-----------+ 2 rows in set (0.00 sec)
热门推荐
10 生日短袖祝福语简短英文
11 长辈体检怎么祝福语简短
12 致姐姐生日祝福语简短
13 新婚红包祝福语简短男
14 开业经典祝福语简短
15 广东新居入伙简短祝福语
16 周末语录生日祝福语简短
17 生日寿宴祝福语简短精辟
18 舰长生日祝福语简短