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 IN operator in any SQL statement that accepts the  WHERE clause such as SELECTUPDATE or DELETE.

The following illustrates how to use the IN operator in the SELECT statement:

The IN operator returns TRUE if 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 product whose unit price is $18, $19 or $20, you can perform the following query:

SQL IN Example 1

IN vs. OR

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

When the list of values grows, the query that uses the OR operators becomes be difficult to read. Therefore, whenever you write a query that has multiple OR operators, you can use the IN operator to make your query more readable.

SQL IN operator with NOT operator

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

SQL IN example

The IN operator is also often used in SQL subquery that 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 in a set of values.