Selecting Value in a Set of Values with SQL IN Opeartor

Summary: in this tutorial, you will learn how to use the SQL IN operator to select rows whose column values are in a set of values.

The IN operator is used in a WHERE clause to select rows whose values are in a set of values. You can use the INoperator in any SQL statement that accepts the WHERE clause such as SELECT, UPDATE or DELETE.

The following illustrates how to use the INoperator in the SELECTstatement:

SELECT 
    column1, column2,...
FROM
    table_name
WHERE
   (expr | column) IN (value1 , value2, ...);Code language: SQL (Structured Query Language) (sql)

The INoperator returns TRUEif the expression or value of the column matches one of the values in the list i.e., value1, value2, etc., otherwise, it returns FALSE.

SQL IN operator examples

For example, to select a product whose unit price is $18, $19, or $20, you can perform the following query:

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice IN (18 , 19, 20)Code language: SQL (Structured Query Language) (sql)

IN vs. OR

The INoperator helps you avoid using multiple ORoperators in the WHERE clause. You can rewrite the query above using multiple OR operators as the following query:

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice = 18 OR 
    unitPrice = 19 OR 
    unitPrice = 20;Code language: SQL (Structured Query Language) (sql)

When the list of values grows, the query that uses the ORoperators becomes difficult to read. Therefore, whenever you write a query with multiple ORoperators, you can use the INoperator to make your query more readable.

SQL IN operator with NOT operator

The IN operator can combine with the NOT operator to select rows whose column values are not in a set of values. For example, you can select products whose unit prices are not 18, 1,9 or 20 by using the following query:

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice NOT IN (18 , 19, 20);Code language: SQL (Structured Query Language) (sql)

The IN operator is also often used in SQL subquery, which you will learn later in the SQL subquery tutorial.

In this tutorial, you have learned how to use the SQL IN operator to select rows whose column values are in a set of values.

SQL IN Operator

SQL IN operator examples

IN vs. OR

SQL IN operator with NOT operator

Databases