SQL WHERE Clause IN Operator

What is the IN operator?

The IN operator is a logical operator that can be used in the WHERE clause to filter based on a list of values. For example, using only the information learned in the previous lessons, if we wanted to return all rows in the Person.Person table where the FirstName is either “Mark”, “Michael”, or “Steven”:

SELECT *
FROM Person.Person
WHERE FirstName = 'Mark' OR FirstName = 'Michael' OR FirstName = 'Steven'

We are forced to repeat the OR operator along with the column name, the comparison operator and then the filtering value. However, the IN operator allows us to simplify the code:

SELECT *
FROM Person.Person
WHERE FirstName IN ('Mark', 'Michael', 'Steven')

Using the IN operator gives us the opportunity to separate the values we wish to filter on by commas enclosed by parentheses. This drastically reduces the amount of code we need to write and improves readability.

The previous examples have used text values as the filtering values. The IN operator can be used with dates as well as numeric examples. To find all rows in the Production.Product table where the ListPrice value is either $742.35, $333.42, $539.99, or $1003.91:

SELECT *
FROM Production.Product
WHERE ListPrice IN (742.35, 333.42, 539.99, 1003.91)

Similarly, we can also use the IN operator with dates. To find all rows from the Sales.SalesOrderHeader table where the OrderDate column value is either April 16, 2005, January 8, 2006 or August 20, 2006:

SELECT *
FROM Sales.SalesOrderHeader
WHERE OrderDate IN ('4/16/2005', '1/8/2006', '8/20/2006')

Practice Problems

Given the examples from this lesson, try and complete the following practice problems:

  1. Find all rows in the Sales.SalesOrderHeader table where the OrderDate value is either May 6, 2006, July 18, 2006, or August 3, 2006.
  2. Find all rows in the HumanResources.Employee table where the JobTitle value is either Senior Tool Designer, Design Engineer, or Research and Development Manager.
  3. Return all rows from the Person.StateProvince table where the CountryRegionCode is either US, CA, DE.