mysql in语法介绍-原创手记-慕课网

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

Introduction to the MySQL IN Operator

The IN operator allows you to determine if a specified value matches any one of a list or a subquery. The following illustrates the syntax of the IN operator.

SELECT column_list FROM table_name WHERE (expr|column) IN ('value1','value2',...)

In the query above:

You can use a column or an expression ( expr) with the IN operator in the WHERE clause of the SELECT statement.
The values in the list must be separated by a comma (,)
The IN operator can also be used in the WHERE clause of other statements such as INSERT, UPDATE, DELETE, etc.

The IN operator returns 1 if the value of the column orthe 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:

First, MySQL evaluates the values based on the type of the column or result of the expr.
Second, MySQL sorts the values.
Third, MySQL searches for values using binary search algorithm which is very fast.

Therefore a query that uses the IN operator with a list of constants will perform very fast.

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.

Let’s practice with some examples of using the IN operator.

MySQL IN examples

If you want to find out all offices which locates in the U.S. and France, you can use the IN operator as the following query:

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

mysql in example 1
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 have to construct a very long statement with multiple OR operators. Hence the IN operator allows you to shorten the query and make the query more readable.

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

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

mysql in example 2

MySQL IN with subquery

The IN operator is often used with a subquery. For example, if you want to find order whose total amount is greater than $60K, you can use the IN operator as 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)

MySQL IN operator with subquery

In this tutorial, we have shown you how to use MySQL IN operator to determine if a value matches any value in a list or a subquery.