3.4 IN 运算符

在本教程中，您将学习如何使用 MySQL IN运算符来确定指定列的值是否匹配列表中的值或子查询中的任何值。

MySQL IN 操作符介绍

IN运算符允许您确定指定的值是否与列表中的值或子查询中的任何值匹配。 下面说明了IN操作符的语法。

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

下面我们更详细的来看看上面的查询：

• 可以在 WHERE 子句中与IN运算符一起使用，可使用列或表达式(expr)。

• 列表中的值必须用逗号(，)分隔。

• IN操作符也可以用在其他语句(如INSERT，UPDATE，DELETE等)的 WHERE 子句中。

如果column_1的值或expr表达式的结果等于列表中的任何值，则IN运算符返回1，否则返回0。

当列表中的值都是常量时：

• 首先，MySQL 根据column_1的类型或expr表达式的结果来计算值。

• 第二步，MySQL 排序值。

• 第三步，MySQL 使用二进制搜索算法搜索值。因此，使用具有常量列表的IN运算符的查询将执行得非常快。

请注意，如果列表中的expr或任何值为NULL，则IN运算符计算结果返回NULL。

可以将IN运算符与NOT运算符组合，以确定值是否与列表或子查询中的任何值不匹配。

MySQL IN 示例

下面练习一些使用IN操作符的例子。首先来看看办事处表：offices 的结构

mysql> desc offices;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| officeCode   | varchar(10) | NO   | PRI | NULL    |       |
| city         | varchar(50) | NO   |     | NULL    |       |
| phone        | varchar(50) | NO   |     | NULL    |       |
| addressLine1 | varchar(50) | NO   |     | NULL    |       |
| addressLine2 | varchar(50) | YES  |     | NULL    |       |
| state        | varchar(50) | YES  |     | NULL    |       |
| country      | varchar(50) | NO   |     | NULL    |       |
| postalCode   | varchar(15) | NO   |     | NULL    |       |
| territory    | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
9 rows in set

如果您想查找位于美国和法国的办事处，可以使用IN运算符作为以下查询：

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

执行上面查询语句，得到以下结果

mysql> SELECT officeCode, city, phone, country FROM offices WHERE country IN ('USA' , 'France');
+------------+---------------+-----------------+---------+
| officeCode | city          | phone           | country |
+------------+---------------+-----------------+---------+
| 1          | San Francisco | [phone] | USA     |
| 2          | Boston        | [phone] | USA     |
| 3          | NYC           | [phone] | USA     |
| 4          | Paris         | [phone] | France  |
+------------+---------------+-----------------+---------+
4 rows in set

也可以使用OR运算符执行得到与上面查询相同的结果，如下所示：

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

执行上面查询语句，得到以下结果

mysql> SELECT officeCode, city, phone FROM offices WHERE country = 'USA' OR country = 'France';
+------------+---------------+-----------------+
| officeCode | city          | phone           |
+------------+---------------+-----------------+
| 1          | San Francisco | [phone] |
| 2          | Boston        | [phone] |
| 3          | NYC           | [phone] |
| 4          | Paris         | [phone] |
+------------+---------------+-----------------+
4 rows in set

如果列表中有很多值，使用多个OR运算符则会构造一个非常长的语句。 因此，使用IN运算符则会缩短查询并使查询更易读。

要获得不在美国和法国的办事处，请在WHERE子句中使用NOT IN如下：

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

执行上面查询语句，得到以下结果

mysql> SELECT officeCode, city, phone FROM offices WHERE country NOT IN( 'USA', 'France');
+------------+---------+------------------+
| officeCode | city    | phone            |
+------------+---------+------------------+
| 5          | Beijing | [phone]  |
| 6          | Sydney  | [phone]  |
| 7          | London  | [phone] |
+------------+---------+------------------+
3 rows in set

MySQL IN 与子查询

IN运算符通常用于子查询。子查询不提供常量值列表，而是提供值列表。

我们来看看两张表：orders和orderDetails表的结构以及它们之间的关系：

img

例如，如果要查找总金额大于60000的订单，则使用IN运算符查询如下所示：

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

执行上面语句，得到以下结果

mysql> SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT 
            orderNumber
        FROM
            orderDetails
        GROUP BY orderNumber
        HAVING SUM(quantityOrdered * priceEach) > 60000);
+-------------+----------------+---------+-------------+
| orderNumber | customerNumber | status  | shippedDate |
+-------------+----------------+---------+-------------+
|       10165 |            148 | Shipped | 2013-12-26  |
|       10287 |            298 | Shipped | 2014-09-01  |
|       10310 |            259 | Shipped | 2014-10-18  |
+-------------+----------------+---------+-------------+
3 rows in set

上面的整个查询可以分为2个查询。

首先，子查询使用orderDetails表中的GROUP BY和HAVING子句返回总额大于60000的订单号列表。

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

执行上面语句，得到以下结果

mysql> SELECT 
    orderNumber
FROM
    orderDetails
GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000;
+-------------+
| orderNumber |
+-------------+
|       10165 |
|       10287 |
|       10310 |
+-------------+
3 rows in set

其次，主查询从orders表中获取数据，并在WHERE子句中应用IN运算符。

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

执行上面语句，得到以下结果

mysql> SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (10165,10287,10310);
+-------------+----------------+---------+-------------+
| orderNumber | customerNumber | status  | shippedDate |
+-------------+----------------+---------+-------------+
|       10165 |            148 | Shipped | 2013-12-26  |
|       10287 |            298 | Shipped | 2014-09-01  |
|       10310 |            259 | Shipped | 2014-10-18  |
+-------------+----------------+---------+-------------+
3 rows in set

在本教程中，我们向您展示了如何使用 MySQL IN运算符来确定值是否匹配列表或子查询中的任何值。