The
IN
operator is a logical operator that allows you to test whether a specified value matches any value in a list.
The following shows the syntax of the SQL Server
IN
operator:
In this syntax:
- First, specify the column or expression to test.
- Second, specify a list of values to test. All the values must have the same type as the type of the column or expression.
If a value in the column or the expression is equal to any value in the list, the result of the
IN
operator is TRUE
.
The
IN
operator is equivalent to multiple OR
operators, therefore, the following predicates are equivalent:
To negate the
IN
operator, you use the NOT IN
operator as follows:
The result the
NOT IN
operator is TRUE
if the column or expression does not equal to any value in the list.
In addition to a list of values, you can use a subquery that returns a list of values with the
IN
operator as shown below:
In this syntax, the subquery is a
SELECT
statement that returns a list of values of a single column.
Note that if a list contains
NULL
, the result of IN
or NOT IN
will be UNKNOWN
.
SQL Server IN
operator examples
See the following
production.roducts
table from the sample database.
A) Using SQL Server IN
with a list of values example
The following statement finds the products whose list price is one of the following values: 89.99, 109.99, and 159.99:

The query above is equivalent to the following query that uses the
OR
operator instead:
To find the products whose list prices are not one of the prices above, you use the
NOT IN
operator as shown in the following query:
B) Using SQL Server IN
operator with a subquery example
The following query returns a list of product identification numbers of the products located in the store id one and has the quantity greater than or equal to 30:

You can use the query above as a subquery in as shown in the following query:

In this example:
- First, the subquery returned a list of product id.
- Second, the outer query retrieved the product names and list prices of the products whose product id matches any value returned by the subquery.
For more information on the subquery, check it out the subquery tutorial.
In this tutorial, you have learned how to use the SQL Server
IN
operator to check whether a value matches any value in a list or returned by a subquery.
0 Comments