MySQL 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 | +1 650 219 4782 | USA     |
| 2          | Boston        | +1 215 837 0825 | USA     |
| 3          | NYC           | +1 212 555 3000 | USA     |
| 4          | Paris         | +33 14 723 4404 | 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 | +1 650 219 4782 |
| 2          | Boston        | +1 215 837 0825 |
| 3          | NYC           | +1 212 555 3000 |
| 4          | Paris         | +33 14 723 4404 |
+------------+---------------+-----------------+
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 | +86 33 224 5000  |
| 6          | Sydney  | +61 2 9264 2451  |
| 7          | London  | +44 20 7877 2041 |
+------------+---------+------------------+
3 rows in set

MySQL IN與子查詢

IN運算符通常用於子查詢。子查詢不提供常量值列表，而是提供值列表。

我們來看看兩張表：orders和orderDetails表的結構以及它們之間的關係：

例如，如果要查找總金額大於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運算符來確定值是否匹配列表或子查詢中的任何值。