如何使用MySQL LEFT JOIN 来模拟MySQL MINUS 查询？

由于我们无法在 MySQL 中使用 MINUS 查询，因此我们将使用 LEFT JOIN 来模拟 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)登录后复制

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)登录后复制

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)登录后复制