MySQL IN Operator

Overview

In this tutorial, we will understand MySQL IN Operator. We can use this operator in the SELECT
WHERE clause to search and find the rows in the specified set of values.

Syntax

The general syntax for the operator usage is as follows:

mysql> SELECT column_list
FROM table_name
WHERE search_column IN (list of values to search for);

The set of values in the IN operator is specified as a comma-delimited list.

Example

In this example, we will use the City database table from the world MySQL database. To know the columns and the data types of the columns of the table; Connect to the world MySQL database and issue the following command.

mysql> DESC City;

MySQL World City Table Describe

The query returns the rows of the City table and the selected columns where the city name in the list provided using the IN operator.

mysql> SELECT Id, Name, District
-> FROM City
-> WHERE Name IN (‘El Paso’,’New York’,’Santa Monica’);
+——+————–+————+
| Id | Name | District |
+——+————–+————+
| 3793 | New York | New York |
| 3815 | El Paso | Texas |
| 4060 | Santa Monica | California |
+——+————–+————+
3 rows in set (0.00 sec)

MySQL IN Operator

IN Operator Subquery

mysql> SELECT column_list
FROM table_name
WHERE search_column IN (Sub_Query);

The IN operator when used with a subquery, the condition is true for rows in the outer query that match any row returned by the subquery. The NOT IN construct is the exact opposite of IN, and is true for rows in the outer query that match no rows returned by the subquery.

The following example uses the IN keyword for a subquery that returns the Country codes for African countries, which are then used in the outer query to return the Population of each of these countries.

mysql> SELECT Name, Population FROM City
-> WHERE CountryCode IN
-> (SELECT Code FROM Country WHERE Continent = ‘Africa’)
-> LIMIT 10;
+————+————+
| Name | Population |
+————+————+
| Luanda | 2022000 |
| Huambo | 163100 |
| Lobito | 130000 |
| Benguela | 128300 |
| Namibe | 118200 |
| Bujumbura | 300000 |
| Cotonou | 536827 |
| Porto-Novo | 194000 |
| Djougou | 134099 |
| Parakou | 103577 |
+————+————+
10 rows in set (0.00 sec)