一、基本定义
SQL NOT IN 是 SQL 中一个常见的操作符,用于在 WHERE 子句中指定一个测试值集合,该测试值集合由用逗号分隔的值列表表示。 NOT IN 运算符与 IN 相反。它返回不在指定列表中的所有行。
二、基本语法
SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2,...);
在上面的语法中:
- column_name(s) 指定要返回的列。
- table_name 指定要从中检索数据的表。
- column_name 指定要测试值的列。
- value1、value2 等指定不允许存在于表中的值。
三、实例演示
例如,我们有一个用户表 user,存储了所有已注册用户的个人信息。现在我们要查询所有没有注册国际短信业务的用户信息:
SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM international_sms);
在这个示例中,我们使用了子查询来指定业务列表,然后列出了在 user 表中不匹配此列表的所有用户。
四、应用案例
1. 删除指定子查询结果
假设我们有一个商品 sales 表和一个库存 inventory 表。现在我们想在 inventory 表中删除所有商品已售完的记录。
DELETE FROM inventory WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);
这里,我们使用了 NOT IN 运算符和子查询来指定应该从 inventory 表中删除的记录。
2. 查询两个表之间缺失的数据
假设我们有两个表 t1 和 t2,每个表都包含一个 id 列。如果我们想知道在 t1 中存在但在 t2 中不存在的所有 id 值,可以按如下方式查询:
SELECT t1.id FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2);
在这个示例中,我们使用了 NOT IN 和子查询来返回在 t1 表中存在的但在 t2 表中不存在的所有 id 值。
3. 查询满足多个条件的数据
假设我们有一组订单数据,包括订单 ID、产品 ID 和销售日期等信息。如果我们想查询在两个指定日期之间出售的所有产品,可以使用以下代码:
SELECT * FROM orders WHERE date BETWEEN '2021-09-01' AND '2021-09-15' AND product_id NOT IN (SELECT product_id FROM returns);
在这个示例中,我们使用了 BETWEEN 和 NOT IN 运算符,以查询满足两个条件的订单。
总结
SQL NOT IN 命令可以用于在 WHERE 子句中指定一个测试值集合。它与 IN 命令相反,返回不在指定列表中的所有行。在实际的 SQL 应用中,NOT IN 通常与子查询搭配使用,可以提高查询的灵活性和效率,在数据处理中具有不可替代的作用。