The following statement uses the IN Operator to find people with variations of the name Tom:
/* 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.
/* 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.
To learn more about the IN Operator, check out these useful resources:
- In-depth article on How to use the SQL IN Operator with an Expression List
- Blog post I wrote detailing how to use the In Operator with a Subquery
- Recommend book SQL Practice Problems…