MySQL IN Operator with Examples


How to Use the MySQL IN Operator

In this tutorial, you'll learn how to use the MySQL IN operator. The IN operator is used to check if a value is within a set of values.

MySQL IN Syntax

The following is the syntax of the IN operator:

SELECT column_name1, column_name2, ..., column_nameN

FROM table_name

WHERE column_name IN (value1, value2, ...)

You also can use the IN operator with a subquery. The syntax is as follows:

SELECT column_name1, column_name2, ..., column_nameN

FROM table_name

WHERE column_name IN (SELECT statement)

Examples

Example 1

The following statement returns "0" since 30 doesn't exist in a list of 1, 2, 3, 4, and 5.

SELECT 30 IN (1, 2, 3, 4, 5);

Example 2

The following statement returns "1" because there is one Apple in a list of apple, orange, and grape:

SELECT "Apple" IN ("apple", "orange", "grape");

Example 3

Suppose you have a table named customers with the demo data, as shown below.

Example 3.1

customer_idcustomer_codecustomer_namecountry
1#CID_20001Josh WarnerJapan
2#CID_20002Lisa RamseySouth Korea
3#CID_20003Clifton ChristensenJapan
4#CID_20004Iris WilkersonUnited States
5#CID_20005Charles HarmonJapan
6#CID_20006Glen WoodUnited States
7#CID_20007Francis LewisGermany
8#CID_20008William BarnettChina

The following statement retrieves all the customers located in the United States and Germany:

SELECT * FROM customers

WHERE country IN ("Germany", "United States");

The result set returned from MySQL:

customer_idcustomer_codecustomer_namecountry
4#CID_20004Iris WilkersonUnited State
6#CID_20006Glen WoodUnited State
7#CID_20007Francis LewisGermany

Example 3.2

The following statement retrieves all customers not located in the US, JapanGermany, and South Korea:

SELECT * FROM customers

WHERE country NOT IN ("Germany", "United States", "Japan", "South Korea");

The result set returned from MySQL:

customer_idcustomer_codecustomer_namecountry
8#CID_20008William BarnettChina

Example 3.3: Using IN operator with a subquery

The following illustrates an example of using the IN operator with a subquery:

SELECT * FROM customers
WHERE country IN (SELECT country FROM customers WHERE country = "China" OR country = "South Korea");

MySQL returned result set:

customer_idcustomer_codecustomer_namecountry
2#CID_20002Lisa RamseySouth Korea
8#CID_20008William BarnettChina

In this tutorial, you have learned how to use the MySQL IN operator. 

You can use the MySQL IN operator to check if a value is within a set of values. You also can use it with a subquery.

Leave a Reply

Your email address will not be published. Required fields are marked *