SQL IN 运算符

SQL Server IN 运算符有助于限制 SELECT 语句返回的行数（或记录数）。IN 运算符允许使用多个值并检查给定的表达式或列名是否存在于这些值中的任何一个，而不是编写多个 OR 条件。如果行与值集之间存在匹配，则 SELECT 语句将返回记录。它会从结果集中排除所有不匹配的记录。

我们可以称此 SQL Server IN 运算符为过滤工具，它有助于显示所需的记录而不是所有记录。当您想要选择匹配两个以上条件的行时，这非常有用。它更具可读性、更清晰，并且执行速度更快。本文将探讨 IN 运算符的重要性以及其语法，并提供数字、日期、字符串和子查询的示例。

SQL IN 运算符的一个实际场景是在 SSRS 报表中使用多值参数。为了允许用户选择多个值来过滤图表，您必须使用 IN 运算符作为过滤条件。另一种方法是使用多个 OR 条件与 = 的组合。例如，下面的查询返回属于牛仔裤和衬衫的所有产品。

SELECT * FROM products WHERE subcategory IN('Jean', 'Shirts')

有关更多参考，请参阅多值参数和SQL Server 报表服务文章。

SQL Server IN 运算符语法

通常，我们使用 WHERE 子句来检查单个条件，而要针对条件检查多个值，则使用 IN 运算符。IN 运算符接受多个值作为过滤条件的语法如下。

SELECT Column(s)
FROM Table(s)
WHERE [Column_Name] IN (Value1, Value2, ...., ValueN)

如果需要针对表达式或 column_value 检查多个值，请始终优先选择 SQL IN 而不是 OR 运算符。让我解释一下上述语法的各个组成部分，以便更好地理解它们。

  • 列：请在表中提及所有您希望在服务器结果集中显示的列名。您在此处提及的任何内容都将只显示在最终结果集中。它可以是一个或多个。
  • 表：指定源表名。如果从数据库中的多个表中选择列，请使用JOINS来连接多个表。
  • Column_Name：它表示您要与 Values 进行比较的表列。它可以是列或有效的表达式。
  • 值1,...值N：在这里，我们必须在 IN 运算符中提供要与表 Column_Name 进行检查的值或表达式。如果 column_name 匹配任何值，则 IN 运算符将返回布尔 TRUE。因此，将显示匹配的行或记录。该过程将对表中的所有剩余行继续进行。

在某些情况下，我们使用 IN 运算符来检查 SUBQUERY 返回的值。这意味着将 (Value1,…, ValueN) 替换为子查询。在下一个 SQL IN 运算符部分中，我们将向您展示多个示例，包括字符串、数字、子查询、联接等，以帮助您理解此概念。

对于此 SQL IN 运算符示例，我们将使用在DELETE 语句文章中提及的 Employee 和 Department 表。尽管我们大多数示例都使用了SELECT 语句，但您可以使用相同的技术来使用 DELETE 和UPDATE 命令实现 IN 运算符。

SQL Server IN 运算符在数字上的示例

我们可以在数值列或数字上使用 IN 运算符来过滤选定的数据。例如，如果您想要那些完成了某些里程碑订单的客户，例如 10、25、50、75、100、125 和 150。在这种情况下，您可以将所有这些数字放在括号内，以对照客户表中的订单列进行检查。以下是使用 IN 运算符在整数列上的一些常见场景：

  • 查找特定薪资范围内的员工。
  • 查找不同价格（如 10、50、100、200 和 500）的产品。
  • 每月销售额为 10,000、50,000 和 100,000 的商店。

以下 SQL Server IN 运算符查询使用 YearlyIncome 数字或数值列来过滤选定的数据。它将查找 Employee 表中所有 YearlyIncome 为 70000 或 80000 的员工。

SELECT * FROM [Employee]

WHERE YearlyIncome IN (70000, 80000)

为了更好地理解数字列上的 SQL IN 运算符，让我向您展示另一个示例。以下查询返回 Yearly Income 为 50000、70000、80000 或 90000 的员工。

SELECT * FROM [Employee]

WHERE YearlyIncome IN (50000, 70000, 80000, 90000)

同样，您可以尝试以下查询来查找销售额为 24.99、59.53、2320.49 或 3078.27 的员工。

SELECT * FROM [Employee]

WHERE Sales IN (24.99, 59.53, 2320.49, 3078.27)

注意： IN 运算符将忽略作为参数值提交的任何重复值。建议您始终检查并删除它们以提高查询性能，但请不要担心，查询不会抛出错误。

SQL IN 运算符在字符串列上的应用

Microsoft SQL Server 允许您在 IN 运算符中使用任何数据类型列，本节将介绍字符串列。以下查询使用 VARCHAR 和 NVARCHAR 列作为参数。但是，您可以使用任何具有 VARCHAR、CHAR、NVARCHAR 和 NCHAR 等数据类型的字符串列。

以下是一些我们使用 IN 运算符获取结果集的实时场景。

  • 查找手机、笔记本电脑和台式机类别中的产品。
  • 显示销售部、人力资源部和工程部的员工。
  • 查找居住在美国、加拿大和墨西哥的客户。

此 IN 运算符查询查找 Employee 表中所有职务为 Management 或 Clerical 的员工。

  • SELECT 语句从 Employee 表中选择 EmpID、FirstName、LastName、Education、Occupation、YearlyIncome 和 Sales 列。
  • WHERE 子句使用 Occupation 列来过滤上述 SELECT 语句的 Employee 表结果集。
  • 括号中包含 Management 或 Skilled Manual 单词。因此，对于每个记录或行，SQL IN 运算符会将这两个单词与 Occupation 列进行检查。如果 Occupation 匹配 Management 或 Clerical，则该列将添加到结果集中。此过程将对所有 14 行继续进行。
SELECT [EmpID] ,[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales] FROM [Employee]
WHERE [Occupation] IN ('Management', 'Skilled Manual')
SELECT * FROM [Employee] WHERE [Occupation] IN ('Management', 'Skilled Manual')

提示：使用 NOT IN 从最终结果集中排除与表达式匹配的值。

下面的查询在 WHERE 子句中的字符串列内使用 IN 运算符。它返回学历为硕士学位、部分高中或高中的员工。

SELECT * FROM [Employee] WHERE [Education] 
IN ('Masters Degree', 'Partial High School', 'High School')

SQL IN 运算符对日期的操作

IN 运算符最令人惊叹的功能之一是它允许您处理日期并使用多个日期值过滤表。例如，您想显示每个季度的开始月份，例如一月、四月、七月和十月。在这种情况下，您可以使用 IN 运算符和 MONTH 函数在结果集中显示这些日期的销售额。IN 运算符中常用日期的常见场景包括：

  • 查找每年黑色星期五促销期间走进商店的客户总数。
  • 查找圣诞前夜、黑色星期五和复活节的销售额。
  • 特定年份的销售额。

以下 SQL Server IN 运算符查询返回所有雇用日期为 2010-12-29、2008-09-22、2009-08-12、2013-09-15 和 2014-05-15 的员工。

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] IN ('2010-12-29','2008-09-22',
	'2009-08-12','2013-09-15', '2014-05-15')

提示：日期值必须用单引号括起来。否则，将引发错误。

上面的示例过滤了属于几个重要日期的记录。如果您需要特定年份雇用的员工怎么办？在这种情况下，您可以使用 IN 运算符和 YEAR 函数。YEAR 函数从雇用日期列中提取年份，IN 运算符将每个 HireDate 列的行与年份而不是日期进行检查。

例如，下面的代码返回所有雇用年份为 2013、2014、2009 和 2006 的员工。如果您观察下图，所有雇用日期在上述年份的员工都将只显示在最终结果中。

SELECT * FROM [Employee]
WHERE YEAR([HireDate]) IN ('2013', '2014', '2009', '2006')
SQL IN Operator On Numbers, Strings, and Dates

SQL IN 运算符与 OR 运算符

IN 运算符和逻辑 OR 运算符的工作功能相同，我们可以说 IN 运算符是 OR 的更短、更易读的版本。除此之外，使用 IN 运算符比 OR 运算符有一些关键优势，它们是：

  1. 在处理多个条件时，IN 运算符的性能比 OR 运算符更快。
  2. 您可以将 IN 运算符与逻辑运算符（AND、OR 和 NOT）结合使用，以增加列表项。
  3. 与 OR 运算符相比，IN 运算符代码更易读懂。

我们已经提到了 IN 运算符的语法，以下代码展示了编写 IN 语句的 OR 运算符版本。

SELECT Column(s) FROM Table(s)
WHERE [Column_Name] = Value1 OR
   [Column_Name] = Value2 OR
   [Column_Name] = ValueN

例如，如果您想查找属于家具、服装和鞋类的产品。我们可以使用 OR 运算符或 SQL IN 运算符。

SELECT * FROM products 
WHERE category IN ('Furniture', 'Clothing', 'Footwear')
Using the OR operator.
SELECT * FROM products 
WHERE category = 'Furniture' OR category = 'Clothing' OR category = 'Footwear'

当您使用两个或三个条件时，两者看起来都可以接受并且更具可读性。但是，如果您添加一个或多个条件（例如厨房、电子产品），OR 运算符看起来会很长且繁琐。

为了演示这一点，我们使用了数值列部分中提到的上述查询。在这里，我们使用 OR 运算符编写相同的 IN 查询以显示差异。

SELECT * FROM Employee
WHERE YearlyIncome = 50000 OR YearlyIncome = 70000 OR 
   YearlyIncome = 80000 OR YearlyIncome = 90000.

SQL 中的多个 IN 运算符

我们还可以在单个 WHERE 子句中使用多个 IN 运算符。如前所述，我们可以将 IN 运算符与任何逻辑运算符（例如 AND、OR 和 NOT）结合使用，以进一步扩展搜索范围。

在此示例中，我们将在单个语句中使用 SQL Server IN 运算符和逻辑 AND 运算符，以同时对字符串和数字列应用过滤器。

有些情况下，一个品牌可能有多种类别，在这种情况下，我们可以使用多个 IN 运算符来深入了解产品线。例如，假设 Pepe Jeans、Wangler 和 Lee 品牌生产衬衫、鞋子、T 恤、牛仔裤、运动裤等。如果我们需要牛仔裤和 T 恤的产品或销售额，您可以编写以下查询。

SELECT * FROM products

WHERE Brand IN('Pepe Jeans', 'Wangler', 'Lee') AND category IN('Jean', ‘T-Shirt')

以下查询返回职业为 Management 或 Clerical 且年收入为 50000 或 80000 的员工。如果两个条件都匹配，则该行将被添加到最终结果集中。

SELECT * FROM [Employee]
WHERE [Occupation] IN ('Management', 'Clerical') AND
[YearlyIncome] IN (50000, 80000)

SQL Server IN 运算符与子查询

IN 运算符的主要目标是将列值与多个值进行比较。但是，它不仅限于将列名与固定值集进行比较。您可以将 SQL IN 运算符与子查询一起使用以动态检索值。

IN 运算符的工作原理是检查括号内指定列与值。它不关心它是静态值还是子查询返回的单列结果集。假设它是一个在 WHERE 子句之后括号内的子查询。在这种情况下，IN 运算符会将列名与结果集中的值列表进行比较。

提示：子查询必须返回单列作为结果集。

例如，检索在当前财年有销售额的客户。在开始这个例子之前，让我先展示一个例子。您有两个表，它们之间存在主键和外键关系。您可以使用子查询来查询或从一个表中选择记录，同时使用辅助表。如果您想查找来自某些城市商店的产品销售额。

SELECT * FROM ProductSales

WHERE store_id IN (SELECT ID FROM stores WHERE City IN ('Delhi', ‘Mumbai')

以上查询使用了多个或嵌套的 SQL IN 运算符。为了使其简单，我将使用下面的查询。它只显示 DeptID 与子查询 Department 表返回的 id 列匹配的员工。

SELECT * FROM [Employee]

WHERE [DeptID] IN (SELECT id FROM Department)

为了使其更复杂，我将在子查询中再使用一个 IN 运算符来过滤 Department 表。

SELECT * FROM [Employee]

WHERE [DeptID] IN (SELECT id FROM Department WHERE id IN (6, 7, 8))

我们又举了一个子查询的例子，为了演示，我们使用 AdventureWorksDW2022 数据库。下面的 SQL IN 运算符查询返回 FactInternetSales 表中所有至少有一次销售的产品，并且产品颜色为红色、NA 或黑色。

SELECT DISTINCT [ProductKey],[UnitPrice],[TotalProductCost]
      ,[SalesAmount],[TaxAmt] FROM [FactInternetSales]
WHERE [ProductKey] IN (
  SELECT [ProductKey] FROM [DimProduct] 
  WHERE Color IN ('Multi', 'Blue','White')
)

使用 JOIN

您还可以使用 JOIN 条件来连接 Fact 表和产品表，以显示每个产品的额外信息。

SELECT DISTINCT fact.[ProductKey],[EnglishProductName], Color,
    [TotalProductCost],[SalesAmount]
FROM [FactInternetSales] AS fact JOIN DimProduct ON
fact.ProductKey = DimProduct.ProductKey
WHERE fact.[ProductKey] IN (
  SELECT [ProductKey] FROM [DimProduct] 
  WHERE Color IN ('Multi', 'White')

结果集将是

ProductKeyEnglishProductName颜色TotalProductCostSalesAmount
225AWC 标志帽Multi6.92238.99
228长袖标志运动衫，SMulti38.492349.99
231长袖标志运动衫，MMulti38.492349.99
234长袖标志运动衫，LMulti38.492349.99
237长袖标志运动衫，XLMulti38.492349.99
481赛车袜，M白色3.36238.99
482赛车袜，L白色3.36238.99

IN 和 ORDER BY 子句的组合

将此 SQL IN 运算符与 ORDER BY 子句结合使用，以特定顺序对最终结果进行排序。此示例返回所有职业为 Professional、Skilled Manual 或收入为 60000、90000 的员工。接下来，ORDER BY 子句根据 Sales 列按升序对结果集进行排序。在 Sales 之后添加 DESC 关键字以按降序排序。

SELECT * FROM [Employee]
WHERE [Occupation] IN ('Professional', 'Skilled Manual') OR
[YearlyIncome] IN (60000, 90000)
ORDER BY Sales

使用 IN 和 BETWEEN 运算符

在 SQL Server 的 WHERE 子句中，您可以将 IN 运算符与 BETWEEN 运算符结合使用，以进一步过滤数据。例如，如果您想要在特定时间段和特定位置内的产品销售额，您可以使用 IN 和 BETWEEN 运算符的组合。

SELECT * FROM products
WHERE OrderDate BETWEEN '2024-12-01' AND '2025-05-31'
AND city IN('New York', 'Los Angeles')

以下查询使用 Employee 表来演示 BETWEEN 和 IN 运算符的组合。这里，它显示了所有在 2005 年 1 月到 2012 年 1 月之间加入，并且学历是部分大学、高中或硕士学位的员工。

SELECT * FROM Employee
WHERE HireDate BETWEEN '2005-01-01' AND '2012-01-01'
AND Education IN('Partial College', 'High School', 'Masters Degree')
SQL IN Operator With Subquery and NULL

LIKE

与上述类似，您还可以将 SQL IN 运算符和 LIKE 通配符搜索结合使用，以执行复杂的过滤。例如，以下 LIKE 通配符查询将返回在纽约和洛杉矶销售的所有产品，并且产品子类别名称以 M 开头。

SELECT * FROM products
WHERE subcategory LIKE 'M%'
AND city IN('New York', 'Los Angeles')

使用 SQL IN 运算符的 DML 操作

除了上述 SELECT 语句示例之外，您还可以使用 IN 运算符来更新和删除表中的现有记录。

更新

众所周知，UPDATE 语句使用 WHERE 子句根据条件结果修改某些记录。因此，我们可以使用 IN 运算符来过滤 UPDATE 命令修改的行。

例如，您想更新在市场部、销售部和生产部工作的员工的薪资。下面的查询使用 IN 运算符检查他们的部门是否属于这些类别，并更新他们的薪资。

UPDATE Employee
SET Salary = Salary  + Salary * 0.20
WHERE Department IN('Marketing', 'Sales', 'Production')

删除

与上述类似，您可以将 IN 运算符与 DELETE 语句一起使用，以从表中删除现有行。以下查询将从员工表中删除学历为高中和部分高中的记录。

DELETE FROM Employee
WHERE Education IN('High School', 'Partial High School')

NOT IN 示例

我们还可以将 NOT 关键字与 SQL IN 运算符一起使用。例如，以下查询查找员工表中所有职业不是管理和专业的员工。

SELECT * FROM [Employee]
WHERE [Occupation] NOT IN ('Management', 'Professional')

SQL IN 运算符处理大量值

在使用 IN 运算符时，您应该注意作为参数传递的值的总数。如果您显式传递大量以逗号分隔的值，查询执行过程将变慢并导致性能问题。有时，它可能会根据值集返回错误 8623 或 8632。

为了解决这些问题，有多种选项，所有这些选项都使用子查询。本节将展示如何在 SQL Server IN 运算符中处理大量多个值。

使用 CTE

首先，我们创建一个简单的 CTE（公共表表达式）来存储类别属于服装的产品 ID 列表。

WITH sample AS (
SELECT product_id FROM products
WHERE category = 'Clothing'

接下来，编写 SELECT 语句以从产品表中返回所需的记录，并使用 WHERE 子句进行过滤。这里，IN 运算符使用上面创建的 CTE 来检查 category 列。

SELECT * FROM products p

WHERE p.product_id IN (SELECT product_id FROM sample)

如果您知道这些值，并且想要对这些值进行硬编码而不是使用 SELECT 语句进行检索，请使用以下查询。

WITH sample AS (
SELECT 'Clothing' AS cat
UNION ALL
SELECT 'Furniture'
UNION ALL
SELECT 'Footwear'
UNION ALL
SELECT 'Electronics'

查询以获取属于上述类别的产品。

SELECT * FROM products p
WHERE p.category IN (SELECT cat FROM sample)

使用临时表

与上述类似，您可以使用临时表来存储大量多个值，并将临时表用作 SQL IN 运算符的源。首先，我们创建一个临时表来存储所有产品价格列表。

CREATE TEMPORARY TABLE sample(range INT)
INSERT INTO sample(range) VALUES(50, 100, 200, 500, 1000, 2000, 5000, 10000)

接下来，我们使用 IN 运算符来检查价格与此临时表的关系，而不是每次检查价格都命中原始表。这可以提高查询性能并避免上述错误。

SELECT * FROM products

WHERE price IN(SELECT range FROM sample)

SQL IN 运算符与 NULL 值一起使用

例如，您有一个包含销售信息的员工表，其中一些最近加入或属于桌面 (R&D) 工作的员工的销售额为 NULL。在这种情况下，如果您编写下面的查询，它将显示所有销售额为 1000、2000 和 5000 的员工。但是，在这种情况下，它不会显示 NULL（无销售额）。

SELECT * FROM Employee
WHERE sales IN(1000, 2000, 5000, NULL)

IS NULL

为了解决这种情况，您必须将 IS NULL 函数与 IN 运算符结合使用。

SELECT * FROM Employee

WHERE sales IN(1000, 2000, 5000) OR sales IS NULL;

COALESCE

或者，您可以尝试使用 COALESCE() 函数将整数 NULL 替换为零，将字符串 NULL 替换为 Unknown 或任何文本。

SELECT * FROM Employee

WHERE COALESCE(sales, 0) IN(0, 1000, 2000, 5000)

SQL IN 运算符最佳实践

  1. 避免使用大量值，在将多个值用于 IN 运算符之前，首选 CTE 或临时表来存储这些值。
  2. 始终在 IN 运算符中使用索引列以提高性能。
  3. 左侧列或表达式的数据类型应与 IN 运算符中给定的值匹配。例如，如果列值是 INT，则多个 IN 运算符值列表也应是 INT。否则，将抛出错误。
  4. 使用逻辑 AND、OR 和 NOT 运算符组合多个 IN 运算符或条件。通过这种方式，您可以编写复杂的过滤器。
  5. 组合多个条件时，请始终使用括号。
  6. 在 SQL IN 运算符中使用子查询可以帮助您根据其他查询的输出来动态过滤结果集。此外，如果列表很大，请使用子查询。
  7. 在执行字符串比较时，大小写敏感性至关重要。如果您的数据库区分大小写，并且它认为 Apple 和 apple 不同，请使用 LOWER() 或 UPPER() 函数。
  8. 使用 JOINS 检查多表中的多个值。
  9. 在处理大型数据集或复杂关系时，请使用 EXISTS 运算符而不是 IN 运算符。
  10. 不要在 IN 运算符中使用 NULL 值，而应单独处理 NULL 值。