IN (Transact-SQL)

Determines whether a specified value matches any value in a subquery or a list.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

• test_expression
  Is any valid expression.

• subquery
  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.

Result Types

Boolean

Result Value

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.

Examples

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 SELECT subquery.

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

See Also

Reference

CASE (Transact-SQL)
Expressions (Transact-SQL)
Functions (Transact-SQL)
Operators (Transact-SQL)
SELECT (Transact-SQL)
WHERE (Transact-SQL)
ALL (Transact-SQL)
SOME | ANY (Transact-SQL)

Other Resources

Subquery Fundamentals
Subqueries with IN
Comparison Operators Modified by ANY, SOME, or ALL

Help and Information

Getting SQL Server 2005 Assistance

Change History