The SQL IN Operator is used in the WHERE clause to test whether column value is a member of one of the more specified values.

The following statement uses the IN Operator to find people with variations of the name Tom:

SELECT FirstName, LastName FROM Person.Person WHERE FirstName in ('Tom', 'Tomas', 'Tommy')
/* Answer */
SELECT FirstName, LastName
FROM   Person.Person
WHERE  FirstName in ('Tom', 'Tomas', 'Tommy')

When you run the query, you’ll see only those people whose first name is either Tom, Tomas, or Tommy are returned.

SQL In Operator with Subquery

You can use a subquery with the IN operator.  Here, the column in the WHERE clause is compared to values returned from the subquery. 

Here’s a super simple example showing how to select every Person that is an employee.

SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityID IN (SELECT BusinessEntityID From HumanResources.Employee)
/* Answer */
SELECT BusinessEntityID, FirstName, LastName
FROM   Person.Person
WHERE  BusinessEntityID IN (SELECT BusinessEntityID From HumanResources.Employee)

In summary use the SQL IN operator when you need to compare a column value to a know list of values.  If the list is large considering generating it using a subquery.

Additional Resources

To learn more about the IN Operator, check out these useful resources:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>