SQL NOT IN 运算符

SQL Server 的 NOT IN 运算符的功能与 IN 功能在筛选记录方面正好相反。它限制了 SELECT 语句返回的记录（或行）的数量。本文将结合数字、字符串、日期和子查询的实际示例，解释 NOT IN 运算符的语法。

SQL NOT IN 运算符会检查给定的表达式或列名与圆括号内的值是否匹配。如果存在匹配项，SELECT 语句将不会返回该记录。您可以将此运算符称为不等于 <> 运算符、AND 和 OR 运算符的替代项。

有些人可能需要区分 SQL Server NOT IN 运算符和 NOT EXISTS 运算符。NOT EXISTS 运算符检查子查询返回的完整结果集。NOT IN 运算符根据给定的值筛选行。

SQL Server NOT IN 运算符语法

用于从结果集中排除行的 NOT IN 运算符的基本语法如下。

SELECT [Column Names]
FROM [Source]
WHERE [Column Name] NOT IN (Value1, Value2, ...., ValueN)

在上述语法中

  • 列名：在此处写下您想要显示为结果集的表列。NOT IN 运算符将仅筛选此数据。
  • 源：请指定表名。如果存在多个表，请使用 JOIN。
  • 值（1 到 N）：我们必须提供要与列名进行检查的值或表达式。如果列名中的记录与任何值（Value1、Value2、……、ValueN）匹配，则该记录不会显示在结果集中。

对于此 NOT IN 运算符演示，我们将使用下面显示的数据。

SQL Server NOT IN 运算符用于数字

在接下来的部分，我们将使用上面的 Employee 表，通过 NOT IN 运算符筛选数据。下面的查询将查找 Employee 表中年收入既不是 45000 也不是 50000 的所有员工。

  • SELECT 语句从 Employee 表中选择所有列。
  • WHERE 子句有助于筛选 SELECT 语句返回的结果集。但是，它需要一个条件来检查年收入，而 NOT IN 运算符充当该条件。
  • NOT IN (45000, 50000) 表示排除收入为 45000 和 50000 的员工。
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE [YearlyIncome] NOT IN (45000, 50000)

您也可以更改最后一行，如下所示，它会产生相同的结果集。

WHERE NOT [YearlyIncome] IN (45000, 50000)

提示：如果您想在结果集中包含与表达式匹配的值，请使用 IN 查询。

SQL Server NOT IN Operator on Numbers Column

SQL NOT IN 运算符用于字符串列示例

为了更好地理解，我们再展示一个 Server 示例，由于在第一个示例中已进行说明，因此我们不再详细介绍步骤。

下面的 NOT IN 运算符查询将查找 Employee 表中所有教育程度不是“Education”、“High School”或“Partial High School”的员工。

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School')
SQL NOT IN Operator on String Columns

在字符串和数字列上使用 NOT IN

您也可以在单个 WHERE 子句中使用多个运算符。在此示例中，我们将结合使用多个 SQL NOT IN 运算符和 AND（逻辑运算符）在一个 SELECT 语句中。

下面的查询查找教育程度不等于“Education”、“High School”或“Partial High School”，并且年收入不等于 45000 或 50000 的员工。

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School')
	AND [YearlyIncome] NOT IN (45000, 50000)

SQL NOT IN 运算符在日期列上的工作原理

在某些情况下，您可能不想显示属于特定日期的销售额。在这种情况下，您可以使用 NOT IN 运算符从结果集中排除这些日期。

以下查询显示所有入职日期不是 2006-01-28 13:10:02.047、2009-07-06 12:09:14.237、2006-10-02 05:03:10.223 和 2013-01-14 05:03:10.367 的员工。

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] 
NOT IN ('2006-01-28 13:10:02.047',
	'2009-07-06 12:09:14.237',
	'2006-10-02 05:03:10.223',
	'2013-01-14 05:03:10.367')
SQL NOT IN Operator ON Dates Column

如果您想排除特定年份的销售额该怎么办？在这种情况下，您可以使用 YEAR 函数和 NOT IN 运算符来检查年份而不是日期。例如，下面的查询返回所有入职年份不是 2006、2008 和 2013 的员工。

如果您观察下面的截图，所有属于上述年份的入职日期都已从最终的网格结果中排除。

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE YEAR([HireDate]) NOT IN ('2006', '2008', '2013')
NOT IN Operator ON YEAR Dates

SQL NOT IN 运算符与子查询

在以上所有示例中，我们都操作了单个表。然而，NOT IN 运算符允许您使用子查询来排除结果。例如，检索在本财年、本月等尚未下过订单的客户。

为了演示这个示例，我们使用了 Adventure Works DW 2019 数据库。下面的查询返回在其生命周期内没有任何销售额的产品。

  1. 主查询中的 SELECT 语句从 DimProduct 表中选择所有必需的列。
  2. 子查询从 Fact Reseller Sales 表中选择所有 Product Key。
  3. WHERE 子句和 NOT IN 运算符会筛选出主查询中 Product Key 不在子查询中的产品。最终结果集是没有转售商销售额的产品。
SELECT [ProductKey], [EnglishProductName],[SafetyStockLevel]
	,[Color],[Size],[DealerPrice],[EnglishDescription]
FROM [DimProduct]
WHERE [ProductKey] NOT IN (SELECT [ProductKey] FROM [FactResellerSales])
NOT IN Operator with Subqueries