SQL NOT IN 运算符：初学者完整指南

为何使用 NOT IN？

之所以聚焦 NOT IN 运算符，是因为它在数据排除场景中非常实用。无论您是在清洗数据、编写报告，还是进行复杂分析，掌握如何有效排除特定数据点都极具价值。该运算符语法直观，但也伴随一些可能影响性能与准确性的细微差别。

我们决定深入探讨 NOT IN，是为了帮助您全面理解并能在 SQL 查询中高效使用它。

理解 SQL NOT IN

NOT IN 的基础

NOT IN 运算符用于 WHERE 子句中，排除某列的值与给定值列表任一项匹配的行。其基本语法如下：

SELECT column_names
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);技术支持

该语法突出了运算符在数据筛选中的作用——这是数据分析与数据库管理中的关键一步。

NOT IN 的实际应用

设想一个包含客户信息的数据库表。如果您希望选出不来自特定城市的客户，NOT IN 就能发挥作用，轻松将这些城市从结果集中排除。

循序渐进的 NOT IN 示例

示例 1：排除特定值

假设有一张名为 Customers 的表，包含 CustomerID、CustomerName 和 City 三列。要查找不位于 “New York” 或 “Los Angeles” 的客户，查询如下：

Customers 表

SELECT CustomerName, City
FROM Customers
WHERE City NOT IN ('New York', 'Los Angeles');技术支持

示例 2：结合子查询使用 NOT IN

NOT IN 也可与子查询配合使用。例如，查找尚未被下单的产品：

Products 表

Orders 表

SELECT ProductName
FROM Products
WHERE ProductID NOT IN (
  SELECT ProductID
  FROM Orders
);
技术支持

常见陷阱与规避方法

处理 NULL 值

当值列表包含 NULL 时，NOT IN 常会出问题。由于 NULL 表示未知值，与 NULL 的任何比较在使用 NOT IN 时都不会返回任何行，即便逻辑上应当排除的行也会被影响。

为避免此问题，应确保值列表不包含 NULL，或使用 NOT EXISTS 等替代方案。

示例：

假设有一张 Orders 表，包含 CustomerID 列，其中部分为 NULL 代表游客订单。如果您运行查询以查找不由某些客户下的订单，并将 NULL 包含在列表中，将导致无结果返回。

Orders 表

-- Assume we want to exclude CustomerID 1, 2, and unknown (NULL) customers
SELECT OrderID
FROM Orders
WHERE CustomerID NOT IN (1, 2, NULL);技术支持

此查询将不返回任何行，这很可能并非预期结果。

解决方案： 为确保结果准确，请勿在 NOT IN 的列表中包含 NULL。或者，将已知值的 NOT IN 与 IS NOT NULL 结合使用，以妥善处理 NULL。

SELECT OrderID
FROM Orders
WHERE CustomerID NOT IN (1, 2) AND CustomerID IS NOT NULL;技术支持

性能考量

对于大型数据集或子查询，NOT IN 可能不如 NOT EXISTS 或 LEFT JOIN /IS NULL 等替代方式高效。低效之处在于 NOT IN 需要将表中每一行与列表中的每个值进行比较，这在大数据量时可能显著拖慢性能。

SQL NOT IN 的替代方案

使用 NOT EXISTS

在处理可能返回 NULL 值的子查询时，通常推荐使用 NOT EXISTS 替代 NOT IN。它通常更高效，因为一旦找到匹配就会停止。

示例：

查找尚未被下单的产品，使用 NOT EXISTS：

SELECT ProductName
FROM Products p
WHERE NOT EXISTS (
  SELECT 1
  FROM Orders o
  WHERE o.ProductID = p.ProductID
);
技术支持

该查询检查订单表中是否不存在某产品记录，相比用 NOT IN 对着可能很长的 ID 列表比较，往往更高效。

LEFT JOIN / IS NULL

另一种排除行的高效方式是将 LEFT JOIN 与 WHERE ... IS NULL 结合使用。此方法对大型数据集尤其有效。

示例：

实现同样的目标——查找尚未被下单的产品：

SELECT p.ProductName
FROM Products p
LEFT JOIN Orders o ON p.ProductID = o.ProductID
WHERE o.ProductID IS NULL;技术支持

该方法利用 LEFT JOIN 保留所有产品及其匹配的订单，再通过 WHERE o.ProductID IS NULL 过滤掉有订单的产品，从而实现排除。

使用 NOT IN 的最佳实践

• 检查列表中的 NULL 值：确保用于 NOT IN 的列表不包含 NULL，以避免意外结果。
• 考虑数据集大小：对于大型数据集或子查询，评估 NOT EXISTS 等替代方案是否能提供更佳性能。
• 索引：尽可能为 NOT IN 子句涉及的列创建索引，以提升查询性能。

在真实数据项目中使用 NOT IN

在数据分析中，SQL 的 “Not IN” 命令常用于检索特定数据。本节我们使用一家书店的数据库，跟踪其库存（店内可售图书）与销售（已售出图书）。我们的目标是识别哪些书从未售出。

我们有两张表：

• Inventory：存储书店内图书的信息。
• Sales：存储已售出图书的记录。

Inventory 表

Sales 表

现在，我们要找出哪些书从未售出。

SELECT Title, Author 
FROM Inventory 
WHERE BookID NOT IN (
    SELECT BookID 
    FROM Sales
);
技术支持

接下来，我们希望识别库存中在上个月未售出的图书，假设今天是 2024 年 2 月 7 日。这需要将 Sales 表中的 SaleDate 与当前日期进行比对。

SELECT Title, Author 
FROM Inventory 
WHERE BookID NOT IN (
    SELECT BookID 
    FROM Sales
    WHERE SaleDate >= date('now', '-1 month')
);
技术支持

结论

NOT IN 运算符是 SQL 中用于从查询结果中排除特定值的多面手。通过理解其语法、应用场景与潜在陷阱，您就能在数据处理任务中有效地使用它。

请务必考虑 NULL 值与数据集规模，以优化查询的性能与准确性。随着您对 NOT IN 的熟悉，它将成为您 SQL 工具箱中的得力助手，助您更精准高效地开展数据分析与管理。

您可以在我们的 Improving Query Performance in SQL Server 课程中进一步学习 NOT IN 与 NOT EXISTS，并在我们的 SQL Basics Cheat Sheet 与 SQL Fundamentals skill track 中探索更多 SQL 运算符与相关内容。