Summary: in this tutorial, you will learn how to use SQL IN operator to select rows whose column values are in a set of values.
Introduction to SQL IN operator
SQL IN operator is one of the SQL logical operators. The SQL IN operator is used in the WHERE clause to select rows whose values are in a set of values. You can use SQL IN operator in any SQL statement that accepts the WHERE clause such as SELECT, INSERT, UPDATE or DELETE.
The syntax of the SQL IN operator is as follows:
1 2 3 | SELECT column_list FROM table_name WHERE column IN (value1, value2, value3…) |
SQL IN operator examples
For example, to select product whose unit price is $18, $19 and $20, you can perform the following query:
1 2 3 | SELECT productName, unitPrice FROM products WHERE unitPrice IN (18, 19, 20) |
SQL IN vs. OR operators
The SQL IN helps us avoid using multiple OR operators in the WHERE clause . It is easy to rewrite the query above using multiple OR operators as follows:
1 2 3 | SELECT productName, unitPrice FROM products WHERE unitPrice = 18 OR unitPrice = 19 OR unitPrice = 20 |
When the value list grows, the query that uses OR operators will be more complicated and difficult to read. So whenever you write a query that has multiple OR operators, use SQL IN operator instead to make your query more readable.
SQL IN operator with NOT operator
The SQL 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 is not 18, 19 and 20 by using the following query:
1 2 3 | SELECT productName, unitPrice FROM products WHERE unitPrice NOT IN (18, 19, 20) |
The SQL 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 value in a set of values.