Determines whether a specified value matches any value in a subquery or a list.
Is any valid expression.
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
- expression[ ,... n ]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.
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.
Using NOT IN negates the subquery value or expression.
|Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.|
A. Comparing OR and IN
The following example selects a list of the names of employees who are design engineers, tool designers, or marketing assistants.
USE AdventureWorks; GO SELECT FirstName, LastName, e.Title FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.Title = 'Design Engineer' OR e.Title = 'Tool Designer' OR e.Title = 'Marketing Assistant'; GO
However, you retrieve the same results by using IN.
USE AdventureWorks; GO SELECT FirstName, LastName, e.Title FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.Title 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. Using IN with a subquery
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
USE AdventureWorks; GO SELECT FirstName, LastName FROM Person.Contact AS c JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT SalesPersonID 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. Using NOT IN with a subquery
The following example finds the salespersons who do not have a quota greater than $250,000.
NOT IN finds the salespersons who do not match the items in the values list.
USE AdventureWorks GO SELECT FirstName, LastName FROM Person.Contact AS c JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID WHERE EmployeeID NOT IN (SELECT SalesPersonID FROM Sales.SalesPerson WHERE SalesQuota > 250000) GO
SOME | ANY (Transact-SQL)
Other ResourcesSubquery Fundamentals
Subqueries with IN
Comparison Operators Modified by ANY, SOME, or ALL