How to Use the MySQL IN Operator
In this tutorial, you will learn how to use the MySQL IN operator.
MySQL IN operator is used to check whether a value is within a set of values.
Other tutorials:
MySQL IN Syntax
The syntax of the IN operator is shown below:
SELECT column_name1, column_name2, ..., column_nameN
FROM table_name
WHERE column_name IN (value1, value2, ...)
The IN operator can also be used in subqueries. Its syntax is as follows:
SELECT column_name1, column_name2, ..., column_nameN
FROM table_name
WHERE column_name IN (SELECT statement)
MySQL IN operator Examples
Example 1
The following statement returns "0" because 30 does not exist in the list of 1, 2, 3, 4, and 5.
SELECT 30 IN (1, 2, 3, 4, 5);
Example 2
Since there is one "apple" in the list of items: the "apple," "orange," and "grape," the following statement returns "1".
SELECT "Apple" IN ("apple", "orange", "grape");
Example 3
Suppose you have a table named "customers" in the demo data, as shown in the figure below.
customer_id | customer_code | customer_name | country |
---|---|---|---|
1 | #CID_20001 | Josh Warner | Japan |
2 | #CID_20002 | Lisa Ramsey | South Korea |
3 | #CID_20003 | Clifton Christensen | Japan |
4 | #CID_20004 | Iris Wilkerson | United States |
5 | #CID_20005 | Charles Harmon | Japan |
6 | #CID_20006 | Glen Wood | United States |
7 | #CID_20007 | Francis Lewis | Germany |
8 | #CID_20008 | William Barnett | China |
Example 3.1
The following SQL query retrieves all customers located in the United States and Germany:
SELECT * FROM customers
WHERE country IN ("Germany", "United States");
Result set returned from MySQL:
customer_id | customer_code | customer_name | country |
---|---|---|---|
4 | #CID_20004 | Iris Wilkerson | United State |
6 | #CID_20006 | Glen Wood | United State |
7 | #CID_20007 | Francis Lewis | Germany |
Example 3.2
The following SQL query retrieves all customers not located in the United States, Japan, Germany, and Korea.
SELECT * FROM customers
WHERE country NOT IN ("Germany", "United States", "Japan", "South Korea");
The result set returned from MySQL:
customer_id | customer_code | customer_name | country |
---|---|---|---|
8 | #CID_20008 | William Barnett | China |
Example 3.3: Using the IN operator in a subquery
The following is an example of using the IN operator in a subquery.
SELECT * FROM customers
WHERE country IN (SELECT country FROM customers WHERE country = "China" OR country = "South Korea");
MySQL returned the following result set:
customer_id | customer_code | customer_name | country |
---|---|---|---|
2 | #CID_20002 | Lisa Ramsey | South Korea |
8 | #CID_20008 | William Barnett | China |
Summary
In this MySQL tutorial, you learned how to use the MySQL IN operator.
The MySQL IN operator allows you to check whether a value is within a sequence of values. It can also be used in subqueries.