SQL Server IN operator overview

SQL Server IN operator overview

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:

1	column | expression IN ( v1, v2, v3, ...)

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:

1 2 3

column IN (v1, v2, v3)   column = v1 OR column = v2 OR column = v3

To negate the IN operator, you use the NOT IN operator as follows:

1	column | expression NOT IN ( v1, v2, v3, ...)

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:

1	column | expression IN (subquery)

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:

1 2 3 4 5 6 7 8 9

SELECT     product_name,     list_price FROM     production.products WHERE     list_price IN (89.99, 109.99, 159.99) ORDER BY     list_price;

The query above is equivalent to the following query that uses the OR operator instead:

1 2 3 4 5 6 7 8 9

SELECT     product_name,     list_price FROM     production.products WHERE     list_price = 89.99 OR list_price = 109.99 OR list_price = 159.99 ORDER BY     list_price;

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:

1 2 3 4 5 6 7 8 9

SELECT     product_name,     list_price FROM     production.products WHERE     list_price NOT IN (89.99, 109.99, 159.99) ORDER BY     list_price;

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:

1 2 3 4 5 6

SELECT     product_id FROM     production.stocks WHERE     store_id = 1 AND quantity >= 30;

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT     product_name,     list_price FROM     production.products WHERE     product_id IN (         SELECT             product_id         FROM             production.stocks         WHERE             store_id = 1 AND quantity >= 30     ) ORDER BY     product_name;

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.