IN (Transact-SQL)IN (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

确定指定的值是否与子查询或列表中的值相匹配。Determines whether a specified value matches any value in a subquery or a list.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

test_expression [ NOT ] IN   
    ( subquery | expression [ ,...n ]  
    )   

参数Arguments

test_expressiontest_expression
为任意有效的表达式Is any valid expression.

subquerysubquery
包含某列结果集的子查询。Is a subquery that has a result set of one column. 该列必须与 test_expression 具有相同的数据类型。This column must have the same data type as test_expression.

expression[ ,... n ]expression[ ,... n ]
一个表达式列表,用来测试是否匹配。Is a list of expressions to test for a match. 所有的表达式必须与 test_expression 具有相同的类型。All expressions must be of the same type as test_expression.

结果类型Result Types

BooleanBoolean

结果值Result Value

如果 test_expression 的值与 subquery 所返回的任何值相等,或与逗号分隔的列表中的任何 expression 相等,则结果值为 TRUE;否则,结果值为 FALSE。If the value of test_expression is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE.

使用 NOT IN 对 subquery 值或 expression 求反。Using NOT IN negates the subquery value or expression.

注意

subquery 或 expression 使用 IN 或 NOT IN 与 test_expression 比较后返回的所有空值都将返回 UNKNOWN。Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. 将空值与 IN 或 NOT IN 一起使用会产生意外结果。Using null values in together with IN or NOT IN can produce unexpected results.

RemarksRemarks

在 IN 子句的括号中显式包括数量非常多的值(数以千计,以逗号分隔)可能会消耗资源并返回错误 8623 或 8632。Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. 若要解决这一问题,请将这些项存储于某个表的 IN 列表中,并在 IN 子句中使用 SELECT 嵌套查询。To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

错误 8623:Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

错误 8632:Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

示例Examples

A.A. 比较 OR 和 INComparing OR and IN

以下示例选择设计工程师、工具设计人员或销售助理等雇员的姓名列表。The following example selects a list of the names of employees who are design engineers, tool designers, or marketing assistants.

-- Uses AdventureWorks  
  
SELECT p.FirstName, p.LastName, e.JobTitle  
FROM Person.Person AS p  
JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle = 'Design Engineer'   
   OR e.JobTitle = 'Tool Designer'   
   OR e.JobTitle = 'Marketing Assistant';  
GO  

但是,也可以使用 IN 检索相同的结果:However, you retrieve the same results by using IN.

-- Uses AdventureWorks  
  
SELECT p.FirstName, p.LastName, e.JobTitle  
FROM Person.Person AS p  
JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');  
GO  

以下是上面任一查询的结果集。Here is the result set from either query.

FirstName   LastName      Title  
---------   ---------   ---------------------  
Sharon      Salavaria   Design Engineer                                     
Gail        Erickson    Design Engineer                                     
Jossef      Goldberg    Design Engineer                                     
Janice      Galvin      Tool Designer                                       
Thierry     D'Hers      Tool Designer                                       
Wanida      Benshoof    Marketing Assistant                                 
Kevin       Brown       Marketing Assistant                                 
Mary        Dempsey     Marketing Assistant                                 
  
(8 row(s) affected)  

B.B. 带子查询使用 INUsing IN with a subquery

以下示例查找 SalesPerson 表中所有销售人员的 ID 以获得当年销售额超过 $250,000 的雇员,然后从 Employee 表中选择 EmployeeIDSELECT 子查询的结果相匹配的所有雇员的姓名。The following example finds all IDs for the salespeople in the SalesPerson table for employees who have a sales quota greater than $250,000 for the year, and then selects from the Employee table the names of all employees where EmployeeID that match the results from the SELECT subquery.

-- Uses AdventureWorks  
  
SELECT p.FirstName, p.LastName  
FROM Person.Person AS p  
    JOIN Sales.SalesPerson AS sp  
    ON p.BusinessEntityID = sp.BusinessEntityID  
WHERE p.BusinessEntityID IN  
   (SELECT BusinessEntityID  
   FROM Sales.SalesPerson  
   WHERE SalesQuota > 250000);  
GO  

下面是结果集:Here is the result set.

FirstName   LastName                                             
---------   --------   
Tsvi         Reiter                                              
Michael      Blythe                                              
Tete         Mensa-Annan                                         
  
(3 row(s) affected)  

C.C. 带子查询使用 NOT INUsing NOT IN with a subquery

以下示例查找销售额不超过 $250,000 的销售人员。The following example finds the salespersons who do not have a quota greater than $250,000. NOT IN 查找与值列表中的项不匹配的销售人员。NOT IN finds the salespersons who do not match the items in the values list.

-- Uses AdventureWorks  
  
SELECT p.FirstName, p.LastName  
FROM Person.Person AS p  
    JOIN Sales.SalesPerson AS sp  
    ON p.BusinessEntityID = sp.BusinessEntityID  
WHERE p.BusinessEntityID NOT IN  
   (SELECT BusinessEntityID  
   FROM Sales.SalesPerson  
   WHERE SalesQuota > 250000);  
GO  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

D.D. 使用 IN 和 NOT INUsing IN and NOT IN

下面的示例查找 FactInternetSales 表中与 DimSalesReason 表中的 SalesReasonKey 值相匹配的所有条目。The following example finds all entries in the FactInternetSales table that match SalesReasonKey values in the DimSalesReason table.

-- Uses AdventureWorks  
  
SELECT * FROM FactInternetSalesReason   
WHERE SalesReasonKey   
IN (SELECT SalesReasonKey FROM DimSalesReason);   

下面的示例查找 FactInternetSalesReason 表中与 DimSalesReason 表中的 SalesReasonKey 值不匹配的所有条目。The following example finds all entries in the FactInternetSalesReason table that do not match SalesReasonKey values in the DimSalesReason table.

-- Uses AdventureWorks  
  
SELECT * FROM FactInternetSalesReason   
WHERE SalesReasonKey   
NOT IN (SELECT SalesReasonKey FROM DimSalesReason);  

E.E. 将 IN 与表达式列表结合使用Using IN with an expression list

下面的示例查找 DimEmployee 表中销售人员的所有 ID,该表中员工的名字(不含姓氏)均为 MikeMichaelThe following example finds all IDs for the salespeople in the DimEmployee table for employees who have a first name that is either Mike or Michael.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName  
FROM DimEmployee  
WHERE FirstName IN ('Mike', 'Michael');  

另请参阅See Also

CASE (Transact-SQL) CASE (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
运算符 (Transact-SQL) Operators (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
WHERE (Transact-SQL) WHERE (Transact-SQL)
ALL (Transact-SQL) ALL (Transact-SQL)
SOME | ANY (Transact-SQL)SOME | ANY (Transact-SQL)