MySQL IN運算子
2019-10-16 22:57:35
在本教學中，您將學習如何使用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運算子來確定值是否匹配列表或子查詢中的任何值。