MySQL IN Operator with Examples

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.

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.