SQL Server IN operator

Summary: in this post, you will learn how to use the SQL Server IN operator to check whether a value matches any value in a record.

SQL Server IN operator overview

In this article, we will describe an IN operator is a logical operator that allows you to test whether a detailed value matches any value in a list.

The following shows the syntax of the SQL Server IN operator:

 

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

In this syntax:

  • Initial, specify the column or expression to test.
  • Next, specify a list of values to test. All the values must have the same type as the type of the column or phrase.

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 operatives, therefore, the following predicates are similar:

 

column IN (v1, v2, v3,v4)

 

column = v1 OR column = v2 OR column = v3

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

 

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

The result the NOT IN operator is TRUE if the column or expression does not like 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:

 

column | look 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 Anonymous.

SQL Server IN operator examples

See the following production.roducts table from the sample database.

A) Managing SQL Server IN with a list of values example

The following statement finds the products whose list price is one of the following values: 80.99, 10.99, and 15.99:

 

SELECT

    product_name,

    list_price

FROM

    production.products

WHERE

    list_price IN (80.99, 10.99, 15.99)

ORDER BY

    list_price;

The query above is similar to the following query that uses the OR operator alternatively:




SELECT

    product_name,

    list_price

FROM

    production.products

WHERE

    list_price = 80.99 OR list_price = 10.99 OR list_price = 15.99

ORDER BY

    list_price;

To find the products whose list prices are not one of the prices raised, you use the NOT IN operator as shown in the following query:

 

SELECT

    product_name,

    list_price

FROM

    production.products

WHERE

    list_price NOT IN (80.99, 10.99, 15.99)

ORDER BY

    list_price;

B) Handling 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 10:

 

SELECT

    product_id

FROM

    production.stocks

WHERE

    store_id = 1 AND quantity >= 10;

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

 

SELECT

    product_name,

    list_price

FROM

    production.products

WHERE

    product_id IN (

        SELECT

            product_id

        FROM

            production.stocks

        WHERE

            store_id = 1 AND quantity >= 10

    )

ORDER BY

    product_name;

Example:

  • First, the outer query retrieved the product names and list prices of the products whose product id matches any value returned by the subquery.
  • Second, the subquery returned a list of product ids.

For more learning 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.

2020-04-26