IN Operator in SQL Server

In this SQL Server tutorial, you’ll learn how to use the IN operator to match the values in a list and reduce the need of multiple OR conditions in a T-SQL query.

What is IN Operator in SQL Server?

The IN operator in SQL Server is one of the logical operator that allows the developers to check if a value matches with any value from the list. The IN operator reduces the multiple OR conditions to be used in a SQL query and lets you write a more readable SQL query.

Syntax

expression/column IN (value1, value2, .. value_n);

Parameters

• expression/column
  • This indicates the value to test
• value1, value2, valuen
  • These are the values to test against the specified value, column or expression.

Note:

1. We have first specify the column or expression to test and then specify the list of values to test this against.
2. If a value in the expression/column equals any of the values in the specified list, the result evaluates to TRUE.
3. The IN operator is an alternative to use the multiple OR operator.
4. If you want to negate the IN operator, you can use the NOT IN operator in SQL Server. The example of this is shown in the end of this tutorial. The result of the NOT IN evaluates to TRUE only if the specified expression or column value does not exist in the list.

Example 1 : IN Operator with String Values

Let’s have a look at the usage of the IN operator using string values on the Person table in the AdventureWorks database. The below SQL Query uses the IN condition to compare string values and return the result set.

SELECT * 
FROM [Person].[Person] 
WHERE PersonType IN ('SP','SC''EM')

The above SQL query returns all the records from the Person table where the PersonType contains either AP,SC,EM. Note that the same query can be rewritten using multiple OR conditions which was covered in one of the examples in the previous tutorials.

Example 2 : NOT IN Operator with String Values

The previous example returned all the Person records which matched one of the values from the list. How about negating the IN operator. In this example, let’s look at the usage of the NOT IN operator in SQL Server. The following SQL query returns all the Person records which doesn’t contain any of these PersonType values – SP, SC, EM

SELECT * 
FROM [Person].[Person] 
WHERE PersonType NOT IN ('SP','SC''EM')

The above one is equivalent of using multiple AND conditions in SQL server as shown below

SELECT * 
FROM [Person].[Person] 
WHERE PersonType <> 'SP'
AND PersonType <> 'SC'
AND PersonType <> 'EM'

Example 3 : IN Operator with Numeric Values

Checking for the numeric values in a list is almost same as the usage of the IN operator with string values. The only difference is that the  string value list has single quotes.

The following SQL query will return all the Person records from the Person table from AdventureWorks table where the EmailPromotion is either 1 or 2.

SELECT * 
FROM [Person].[Person] 
WHERE EmailPromotion IN (1,2)