IN

The IN operator allows you to determine if a specified value matches any value in a set of values or returned by a subquery.

The following illustrates the syntax of the IN operator:

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
	(expr|column_1) IN ('value1','value2',...);

Let’s examine the query in more detail:

• Use a column or an expression ( expr ) with the IN operator in the WHERE clause.

• Separate the values in the list by commas (,).

The IN operator returns 1 if the value of the column_1 or the result of the expr expression is equal to any value in the list, otherwise, it returns 0.

When the values in the list are all constants, MySQL performs the following steps:

• First, evaluate the values based on the type of the column_1 or result of the expr expression.

• Second, sort the values.

• Third, search for the value using the binary search algorithm. Therefore, a query that uses the IN operator with a list of constants performs very fast.

Note that if the expr or any value in the list is NULL, the IN operator returns NULL.

You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery. And you can also use the IN operator in the WHERE clause of other statements such as UPDATE, and DELETE.

If you want to find the offices that locate in the U.S. and France, you can use the IN operator as the following query:

SELECT 
    officeCode, 
    city, 
    phone, 
    country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

MySQL IN country list example

You can achieve the same result with the OR operator as the following query:

SELECT 
    officeCode, 
    city, 
    phone
FROM
    offices
WHERE
    country = 'USA' OR country = 'France';