mysql in 和 exist not in 和 not exist 使用区别及示例

短命女 2023-10-10 16:48 42阅读 0赞

在MySQL中，IN和EXISTS都是用于子查询的关键字，它们的作用是判断一个值是否存在于子查询中，不同之处在于IN关键字会将子查询的结果集作为一个整体，而EXISTS关键字则只关心子查询是否返回了结果。  
NOT IN和NOT EXISTS则是这两个关键字的否定形式，它们的作用是判断一个值是否不存在于子查询中。  
下面是IN和EXISTS、NOT IN和NOT EXISTS的使用示例：

IN和NOT IN  
假设我们有两个表，一个是orders表，包含订单号和订单金额等信息；另一个是order\_items表，包含订单号、商品ID和商品数量等信息。我们想要删除订单金额小于100的订单以及相关的商品记录，可以使用如下的SQL语句：

DELETE FROM orders WHERE order_no IN (
      SELECT order_no FROM orders WHERE order_amount < 100
    )

这个SQL语句中，子查询SELECT order\_no FROM orders WHERE order\_amount < 100返回了所有订单金额小于100的订单编号，主查询DELETE FROM orders WHERE order\_no IN (…)会将这些订单及其相关的商品记录从orders和order\_items表中删除。  
如果我们想要删除订单金额小于100的订单及其相关的商品记录中商品数量小于10的记录，可以使用如下的SQL语句：

DELETE FROM order_items WHERE order_no IN (
      SELECT order_no FROM orders WHERE order_amount < 100
    ) AND product_id NOT IN (
      SELECT product_id FROM order_items WHERE quantity >= 10
    )

这个SQL语句中，子查询SELECT order\_no FROM orders WHERE order\_amount < 100返回了所有订单金额小于100的订单编号，子查询SELECT product\_id FROM order\_items WHERE quantity >= 10返回了所有商品数量大于等于10的商品ID，主查询DELETE FROM order\_items WHERE order\_no IN (…) AND product\_id NOT IN (…)会将所有订单金额小于100且商品数量小于10的商品记录从order\_items表中删除。  
2. EXISTS和NOT EXISTS  
假设我们有两个表，一个是employees表，包含员工编号、姓名、部门ID等信息；另一个是departments表，包含部门ID和部门名称等信息。我们想要查询所有有所属部门的员工信息，可以使用如下的SQL语句：

SELECT employee_no, employee_name
    FROM employees e
    WHERE EXISTS (
      SELECT * FROM departments d WHERE d.department_id = e.department_id
    )

这个SQL语句中，子查询SELECT \* FROM departments d WHERE d.department\_id = e.department\_id会返回所有和员工e所属部门ID相同的部门记录，主查询SELECT employee\_no, employee\_name FROM employees e WHERE EXISTS (…)会筛选出所有存在相关部门记录的员工信息。  
如果我们想要查询所有没有所属部门的员工信息，可以使用如下的SQL语句：

SELECT employee_no, employee_name
    FROM employees e
    WHERE NOT EXISTS (
      SELECT * FROM departments d WHERE d.department_id = e.department_id
    )

这个SQL语句中，子查询SELECT \* FROM departments d WHERE d.department\_id = e.department\_id会返回所有和员工e所属部门ID相同的部门记录，主查询SELECT employee\_no, employee\_name FROM employees e WHERE NOT EXISTS (…)会筛选出所有不存在相关部门记录的员工信息。