MySQL LEFT JOIN如何可以用来模拟MySQL MINUS查询？
由于我们无法在MySQL中使用MINUS查询，因此我们将使用LEFTJOIN模拟MINUS查询。通过以下示例可以理解：
示例
在此示例中，我们有两个表，即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)
现在，以下使用LEFTJOIN的查询将模拟MINUS，以在Student_info中返回“studentid”值，但在Student_detail表中不返回。
mysql> SELECT studentid from student_info LEFT JOIN Student_detail USING(studentid) WHERE student_detail.studentid IS NULL; +-----------+ | studentid | +-----------+ | 165 | +-----------+ 1 row in set (0.07 sec)
现在，以下查询将为我们提供与上述查询相反的结果，即它将在student_detail中返回“studentid”值，但不在Student_info表中返回。
mysql> SELECT studentid from student_detail LEFT JOIN Student_info USING(studentid) WHERE student_info.studentid IS NULL; +-----------+ | studentid | +-----------+ | 150 | | 160 | +-----------+ 2 rows in set (0.00 sec)