List Search Conditions
The IN keyword allows you to select rows that match any one of a list of values. For example, without IN, if you want a list of the products in the Frames subcategory, you would need this query:
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
WHERE ProductSubcategoryID = 12 OR ProductSubcategoryID = 14
OR ProductSubcategoryID = 16
However, you can get the same results with less typing if you use IN:
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
WHERE ProductSubCategoryID IN (12, 14, 16)
The items following the IN keyword must be separated by commas and be enclosed in parentheses.
Perhaps the most important use for the IN keyword is in nested queries, also referred to as subqueries. For more information about subqueries, see Subquery Fundamentals.
The following query finds all products which are in the long sleeve logo jersey product model:
USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
The following query finds the names of products that are not in the long sleeve logo jersey product model:
USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID NOT IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
See Also
Other Resources
WHERE (Transact-SQL)
Data Types (Transact-SQL)