MySQL :: MySQL 8.4 參考手冊 :: 15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合運算

版本 8.4

9.0
8.0

MySQL 8.4 參考手冊 / ... / 使用 UNION、INTERSECT 和 EXCEPT 的集合運算

15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合運算

SQL 集合運算將多個查詢區塊的結果合併為單一結果。查詢區塊，有時也稱為簡單表格，是指任何傳回結果集的 SQL 陳述式，例如 SELECT。MySQL 8.4 也支援 TABLE 和 VALUES 陳述式。如需其他資訊，請參閱本章中這些陳述式的個別說明。

SQL 標準定義了以下三種集合運算：

UNION：將兩個查詢區塊的所有結果合併為單一結果，並省略任何重複項。
INTERSECT：僅合併兩個查詢區塊結果中共同存在的列，並省略任何重複項。
EXCEPT：對於兩個查詢區塊 A 和 B，傳回 A 中所有未出現在 B 中的結果，並省略任何重複項。
（某些資料庫系統，例如 Oracle，使用 MINUS 作為此運算子的名稱。MySQL 不支援此用法。）

MySQL 支援 UNION、INTERSECT 和 EXCEPT。

這些集合運算子都支援 ALL 修飾詞。當 ALL 關鍵字跟在集合運算子之後時，這會導致結果中包含重複項。如需更多資訊和範例，請參閱以下涵蓋個別運算子的章節。

所有三個集合運算子也支援 DISTINCT 關鍵字，這會抑制結果中的重複項。由於這是集合運算子的預設行為，因此通常不需要明確指定 DISTINCT。

一般而言，查詢區塊和集合運算可以以任意數量和順序組合。此處顯示一個大幅簡化的表示法：

query_block [set_op query_block] [set_op query_block] ...

query_block:
    SELECT | TABLE | VALUES

set_op:
    UNION | INTERSECT | EXCEPT

可以更準確、更詳細地表示如下：

query_expression:
  [with_clause] /* WITH clause */ 
  query_expression_body
  [order_by_clause] [limit_clause] [into_clause]

query_expression_body:
    query_term
 |  query_expression_body UNION [ALL | DISTINCT] query_term
 |  query_expression_body EXCEPT [ALL | DISTINCT] query_term

query_term:
    query_primary
 |  query_term INTERSECT [ALL | DISTINCT] query_primary

query_primary:
    query_block
 |  '(' query_expression_body [order_by_clause] [limit_clause] [into_clause] ')'

query_block:   /* also known as a simple table */
    query_specification                     /* SELECT statement */
 |  table_value_constructor                 /* VALUES statement */
 |  explicit_table                          /* TABLE statement  */

您應該知道 INTERSECT 的評估優先於 UNION 或 EXCEPT。這表示，例如，TABLE x UNION TABLE y INTERSECT TABLE z 一律會評估為 TABLE x UNION (TABLE y INTERSECT TABLE z)。如需更多資訊，請參閱第 15.2.8 節「INTERSECT 子句」。

此外，您應該記住，雖然 UNION 和 INTERSECT 集合運算子具有交換性（順序並不重要），但 EXCEPT 則不具有交換性（運算元的順序會影響結果）。換句話說，以下所有陳述式皆成立：

TABLE x UNION TABLE y 和 TABLE y UNION TABLE x 會產生相同的結果，但列的順序可能不同。您可以使用 ORDER BY 來強制它們相同；請參閱使用 ORDER BY 和 LIMIT 的集合運算。
TABLE x INTERSECT TABLE y 和 TABLE y INTERSECT TABLE x 會傳回相同的結果。
TABLE x EXCEPT TABLE y 和 TABLE y EXCEPT TABLE x 不會產生相同的結果。如需範例，請參閱第 15.2.4 節「EXCEPT 子句」。

如需更多資訊和範例，請參閱後續章節。

結果集欄名稱和資料類型

集合運算結果的欄名稱取自第一個查詢區塊的欄名稱。範例：

mysql> CREATE TABLE t1 (x INT, y INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES ROW(4,-2), ROW(5,9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES ROW(1,2), ROW(3,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> TABLE t1 UNION TABLE t2;
+------+------+
| x    | y    |
+------+------+
|    4 |   -2 |
|    5 |    9 |
|    1 |    2 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> TABLE t2 UNION TABLE t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

對於 UNION、EXCEPT 和 INTERSECT 查詢而言，情況都是如此。

在每個查詢區塊的對應位置中列出的選取欄應具有相同的資料類型。例如，第一個陳述式選取的第一個欄應與其他陳述式選取的第一個欄具有相同的類型。如果對應的結果欄的資料類型不符，則結果中欄的類型和長度會考量所有查詢區塊擷取的值。例如，結果集中欄的長度不受限於第一個陳述式中值的長度，如下所示：

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1)        |
+----------------------+
| a                    |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+

使用 TABLE 和 VALUES 陳述式的集合運算

您也可以在使用對等 SELECT 陳述式的地方，使用 TABLE 陳述式或 VALUES 陳述式。假設建立並填入表格 t1 和 t2，如下所示：

CREATE TABLE t1 (x INT, y INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);

在上述情況下，並忽略以 VALUES 開頭的查詢輸出中的欄名稱，以下所有 UNION 查詢都會產生相同的結果：

SELECT * FROM t1 UNION SELECT * FROM t2;
TABLE t1 UNION SELECT * FROM t2;
VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
SELECT * FROM t1 UNION TABLE t2;
TABLE t1 UNION TABLE t2;
VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);

若要強制欄名稱相同，請將左側的查詢區塊包裝在 SELECT 陳述式中，並使用別名，如下所示：

mysql> SELECT * FROM (TABLE t2) AS t(x,y) UNION TABLE t1;
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

使用 DISTINCT 和 ALL 的集合運算

依預設，會從集合運算的結果中移除重複列。選用的 DISTINCT 關鍵字具有相同的效果，但會明確表示。使用選用的 ALL 關鍵字時，不會發生重複列移除，且結果會包含聯集中所有查詢的所有相符列。

您可以在同一個查詢中混合使用 ALL 和 DISTINCT。混合類型會被視為使用 DISTINCT 的集合運算會覆寫左側使用 ALL 的任何此類運算。DISTINCT 集合可以透過使用 DISTINCT 和 UNION、INTERSECT 或 EXCEPT 明確產生，或者透過使用沒有後續 DISTINCT 或 ALL 關鍵字的集合運算隱含產生。

當使用一個或多個 TABLE 陳述式、VALUES 陳述式或兩者來產生集合時，集合運算的運作方式相同。

使用 ORDER BY 和 LIMIT 的集合運算

若要將 ORDER BY 或 LIMIT 子句套用至作為聯集、交集或其他集合運算一部分使用的個別查詢區塊，請將查詢區塊加上括號，並將子句放在括號內，如下所示：

(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);

(TABLE t1 ORDER BY x LIMIT 10) 
INTERSECT 
(TABLE t2 ORDER BY a LIMIT 10);

針對個別查詢區塊或陳述式使用 ORDER BY 並不表示列在最終結果中出現的順序，因為集合運算產生的列依預設是未排序的。因此，此內容中的 ORDER BY 通常會與 LIMIT 搭配使用，以判斷要擷取的選取列子集，即使它不一定會影響這些列在最終結果中的順序。如果在查詢區塊中 ORDER BY 出現時沒有 LIMIT，則會將其最佳化掉，因為無論如何都沒有任何作用。

若要使用 ORDER BY 或 LIMIT 子句來排序或限制集合運算的整個結果，請將 ORDER BY 或 LIMIT 放在最後一個陳述式之後：

SELECT a FROM t1
EXCEPT
SELECT a FROM t2 WHERE a=11 AND b=2
ORDER BY a LIMIT 10;

TABLE t1
UNION 
TABLE t2
ORDER BY a LIMIT 10;

如果一個或多個個別陳述式使用 ORDER BY、LIMIT 或兩者，並且您還想將 ORDER BY、LIMIT 或兩者套用至整個結果，則每個此類個別陳述式都必須以括號括住。

(SELECT a FROM t1 WHERE a=10 AND b=1)
EXCEPT
(SELECT a FROM t2 WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;

(TABLE t1 ORDER BY a LIMIT 10) 
UNION 
TABLE t2 
ORDER BY a LIMIT 10;

沒有 ORDER BY 或 LIMIT 子句的陳述式不需要加上括號；在剛顯示的兩個陳述式中的第二個陳述式中，將 TABLE t2 取代為 (TABLE t2) 並不會變更 UNION 的結果。

您也可以在集合運算中將 ORDER BY 和 LIMIT 與 VALUES 陳述式搭配使用，如此範例所示，此範例使用 mysql 用戶端：

mysql> VALUES ROW(4,-2), ROW(5,9), ROW(-1,3) 
    -> UNION 
    -> VALUES ROW(1,2), ROW(3,4), ROW(-1,3) 
    -> ORDER BY column_0 DESC LIMIT 3;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        5 |        9 |
|        4 |       -2 |
|        3 |        4 |
+----------+----------+
3 rows in set (0.00 sec)

（您應該記住，TABLE 陳述式和 VALUES 陳述式都不接受 WHERE 子句。）

此類 ORDER BY 無法使用包含表格名稱的欄參照（亦即，tbl_name.col_name 格式中的名稱）。相反地，請在第一個查詢區塊中提供欄別名，並在 ORDER BY 子句中參照別名。（您也可以使用欄位置在 ORDER BY 子句中參照欄，但此類欄位置的使用方式已過時，因此在未來的 MySQL 版本中可能會遭到移除。）

如果要排序的欄有別名，則 ORDER BY 子句必須參照別名，而不是欄名稱。允許使用以下第一個陳述式，但第二個陳述式會失敗並顯示 Unknown column 'a' in 'order clause' 錯誤：

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

若要使 UNION 結果中的列包含每個查詢區塊依序擷取的列集，請在每個查詢區塊中選取一個額外的欄作為排序欄，並在最後一個查詢區塊之後新增一個依該欄排序的 ORDER BY 子句：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

若要在個別結果中維持排序順序，請將次要欄新增至 ORDER BY 子句：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

使用額外的欄還能讓您判斷每個列來自哪個查詢區塊。額外的欄也可以提供其他識別資訊，例如指示表格名稱的字串。

集合運算的限制

MySQL 中的集合運算有一些限制，以下幾個段落將說明這些限制。

包含 SELECT 陳述式的集合運算具有以下限制：

在第一個 SELECT 中使用 HIGH_PRIORITY 沒有效果。在任何後續的 SELECT 中使用 HIGH_PRIORITY 會產生語法錯誤。
只有最後一個 SELECT 語句可以使用 INTO 子句。然而，整個 UNION 的結果會被寫入 INTO 的輸出目標。

這兩種包含 INTO 的 UNION 變體已被棄用；您應該預期在未來版本的 MySQL 中會移除對它們的支援。

在查詢表達式的尾隨查詢區塊中，在 FROM 之前使用 INTO 會產生警告。範例：
```
... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;
```
在查詢表達式的帶括號尾隨區塊中，使用 INTO（無論其相對於 FROM 的位置如何）都會產生警告。範例：
```
... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);
```
這些變體被棄用是因為它們容易造成混淆，彷彿它們是從指定的資料表而不是整個查詢表達式（即 UNION）收集資訊。

在 ORDER BY 子句中使用彙總函數的集合運算會被拒絕，並產生 ER_AGGREGATE_ORDER_FOR_UNION 錯誤。儘管錯誤名稱可能暗示這僅適用於 UNION 查詢，但如這裡所示，上述情況對於 EXCEPT 和 INTERSECT 查詢也是如此。

mysql> TABLE t1 INTERSECT TABLE t2 ORDER BY MAX(x);
ERROR 3028 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT

鎖定子句（例如 FOR UPDATE 或 LOCK IN SHARE MODE）適用於它所跟隨的查詢區塊。這表示，在使用集合運算的 SELECT 語句中，只有在將查詢區塊和鎖定子句包在括號中時，才能使用鎖定子句。

上一步首頁上一層下一步