MySQL IN

 MySQL IN

Summary: in this tutorial, you will learn how to use MySQL IN operator to determine if a specified value matches any value in a list or a subquery.

Introduction to the MySQL IN Operator

The IN  the operator allows you to determine if a specified value matches any value in a set of values or is 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 the operator returns 1 if the value of the column_1 or the result of the expr  the 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 column_1 or the 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  an 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 the clause of other statements such as UPDATE, and DELETE.

MySQL IN operator examples

Let’s practice with some examples of using the IN operator. See the following offices table from the sample database:

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');

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';

In case the list has many values, you need to construct a very long statement with multiple OR operators. Hence, the IN operator allows you to shorten the query and make it more readable.

To get offices that do not locate in the USA and France, you use NOT IN  in the WHERE clause as follows:

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

Using MySQL IN with a subquery

The IN the operator is often used with a subquery. Instead of providing a list of literal values, the subquery gets a list of values from one or more tables and uses them as the input values of the IN operator.

Let’s take a look at the orders and orderDetails tables from the sample database:

For example, if you want to find the orders whose total values are greater than 60,000, you use the IN operator as shown in the following query:

SELECT    
	orderNumber, 
	customerNumber, 
	status, 
	shippedDate
FROM    
	orders
WHERE orderNumber IN
(
	 SELECT 
		 orderNumber
	 FROM 
		 orderDetails
	 GROUP BY 
		 orderNumber
	 HAVING SUM(quantityOrdered * priceEach) > 60000
);

The whole query above can be broken down into two separate queries.

First, the subquery returns a list of order numbers whose values are greater than 60,000 using the GROUP BY and HAVING clauses:

SELECT 
    orderNumber
FROM
    orderDetails
GROUP BY 
    orderNumber
HAVING 
    SUM(quantityOrdered * priceEach) > 60000;

Second, the outer query uses the IN operator in the WHERE clause to get data from the orders table:

SELECT 
    orderNumber, 
    customerNumber, 
    status, 
    shippedDate
FROM
    orders
WHERE
    orderNumber IN (10165,10287,10310);

In this tutorial, you have learned how to use MySQL IN operator to determine if a value matches any value in a list of values.