Export (0) Print
Expand All

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:

USE AdventureWorks2008R2;
GO
SELECT ProductID, Name
FROM AdventureWorks2008R2.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 AdventureWorks2008R2.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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID NOT IN
(SELECT ProductModelID 
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO

Community Additions

ADD
Show:
© 2014 Microsoft