Normally, the comparison operator IN is used against a list of specific values as in the format of: "test_value IN (value_1, value_2, ..., value_n)". But you can also replace the list of values by a subquery as the following formats:

test_value IN (SELECT column FROM ...)

-- Returns TRUE if the test_value equals to one of

the values returned from the subquery

test_value NOT IN (SELECT column FROM ...)

-- Returns TRUE if the test_value does not equal to any of

the values returned from the subquery

The following tutorial exercise shows you two examples of IN operators. The sample database AdventureWorksLT provided by Microsoft is used.

USE adventureWorksLT

GO

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE c.CustomerID IN (

SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s

)

GO

32

SELECT COUNT(*) FROM SalesLT.Customer c

WHERE c.CustomerID NOT IN (

SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s

)

GO

408