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

版本 9.0

8.4 目前版本
8.0

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

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

SQL 集合運算將多個查詢區塊的結果合併為單一結果。 查詢區塊，有時也稱為簡單表格，是任何傳回結果集的 SQL 敘述，例如 SELECT。MySQL 9.0 也支援 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 陳述式中，只有在查詢區塊和鎖定子句都包含在括號中時，才能使用鎖定子句。