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 SELECT, UPDATE or DELETE.
The following illustrates how to use the IN
operator in the SELECT
statement:
SELECT
column1, column2,...
FROM
table_name
WHERE
(expr | column) IN (value1 , value2, ...);
Code language: SQL (Structured Query Language) (sql)
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 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 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:
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 OR
operators becomes 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 the 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:
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 in a set of values.