极客教程MySQL 如何模拟返回多个表达式的MySQL INTERSECT查询？
由于我们无法在MySQL中使用INTERSECT查询，因此我们将使用EXIST运算符来模拟INTERSECT查询。 可以通过以下示例来理解 –
阅读更多：MySQL 教程
示例
在此示例中，我们有两个名称为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)
现在，使用带有WHERE子句的EXIST运算符的以下查询将模拟INTERSECT以返回在两个表中都存在但名称不为“Yashpal”的“studentid”，Name，Address –
mysql>Select Student_detail.studentid,Student_detail.name, student_detail.address FROM student_detail WHERE Student_detail.studentid >100 AND EXISTS (SELECT * FROM Student_info WHERE Student_info.Name <> 'Yashpal' AND Student_info.studentid = Student_detail.studentid AND Student_info.name = Student_detail.name);
+-----------+--------+------------+
| studentid | name | address |
+-----------+--------+------------+
| 105 | Gaurav | Chandigarh |
| 130 | Ram | Jhansi |
| 132 | Shyam | Chandigarh |
| 133 | Mohan | Delhi |
+-----------+--------+------------+
4 rows in set (0.00 sec)