交集（INTERSECT）、並集（UNION）和補集（EXCEPT） - MaxCompute

您可以通過MaxCompute對查詢結果資料集執行取交集、並集或補集操作。本文為您介紹交集（INTERSECT、INTERSECT ALL、INTERSECT DISTINCT）、並集（UNION、UNION ALL、UNION DISTINCT）和補集（EXCEPT、EXCEPT ALL、EXCEPT DISTINCT、MINUS、MINUS ALL、MINUS DISTINCT）的使用方法。

功能介紹

MaxCompute支援如下三種操作：

交集：求兩個資料集的交集，即輸出兩個資料集均包含的記錄。
並集：求兩個資料集的並集，即將兩個資料集合并成一個資料集。
補集：求第二個資料集在第一個資料集中的補集，即輸出第一個資料集包含而第二個資料集不包含的記錄。

使用限制

對資料集取交集、並集或補集的使用限制如下：

MaxCompute最多允許同時對256個資料集進行操作，超出256個將報錯。
左右兩個資料集的列數必須保持一致。

注意事項

對資料集取交集、並集或補集的注意事項如下：

對資料集進行操作的結果不一定會按序排列。
如果資料集的資料類型不一致，系統會進行隱式轉換。由於相容性原因，STRING類型和非STRING類型資料在集合操作中的隱式轉換已被禁用。

交集

命令格式

--取交集不去重。
<select_statement1> INTERSECT ALL <select_statement2>;
--取交集並去重。intersect效果等同於intersect distinct。
<select_statement1> INTERSECT [DISTINCT] <select_statement2>;

參數說明
- select_statement1、select_statement2：必填。select語句，格式請參見SELECT文法。
- distinct：可選。對兩個資料集取交集的結果去重。

使用樣本

樣本1：對兩個資料集取交集，不去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
INTERSECT ALL
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 1          | 2          |
| 3          | 4          |
+------------+------------+

樣本2：對兩個查詢結果取交集並去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
intersect distinct 
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);
--等效於如下語句。
SELECT DISTINCT * FROM 
(SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
INTERSECT ALL 
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (5, 7) t(a, b)) t;

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 3          | 4          |
+------------+------------+

並集

命令格式

--取並集不去重。
<select_statement1> UNION ALL <select_statement2>;
--取並集並去重。
<select_statement1> UNION [DISTINCT] <select_statement2>;

注意事項
- 存在多個UNION ALL時，支援通過括弧指定UNION ALL的優先順序。
- UNION後如果有CLUSTER BY、DISTRIBUTE BY、SORT BY、ORDER BY或LIMIT子句時，如果設定SET odps.sql.type.system.odps2=false;，其作用於UNION的最後一個select_statement；如果設定SET odps.sql.type.system.odps2=true;時，作用於前面所有UNION的結果。
參數說明
- select_statement1、select_statement2：必填。select語句，格式請參見SELECT文法。
- distinct：可選。對兩個資料集取並集的結果去重。

使用樣本

樣本1：對兩個資料集取並集，不去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b) 
UNION ALL 
SELECT * FROM VALUES (1, 2), (1, 4) t(a, b);

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 1          | 2          |
| 3          | 4          |
| 1          | 2          |
| 1          | 4          |
+------------+------------+

樣本2：對兩個資料集取並集並去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b)
UNION DISTINCT 
SELECT * FROM VALUES (1, 2), (1, 4) t(a, b);
--等效於如下語句。
SELECT DISTINCT * FROM (
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b) 
UNION ALL 
SELECT * FROM VALUES (1, 2), (1, 4) t(a, b));

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 1          | 4          |
| 3          | 4          |
+------------+------------+

樣本3：通過括弧指定UNION ALL的優先順序。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (5, 6) t(a, b)
UNION ALL 
(SELECT * FROM VALUES (1, 2), (1, 2), (3, 4) t(a, b)
UNION ALL 
SELECT * FROM VALUES (1, 2), (1, 4) t(a, b));

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 1          | 2          |
| 5          | 6          |
| 1          | 2          |
| 1          | 2          |
| 3          | 4          |
| 1          | 2          |
| 1          | 4          |
+------------+------------+

樣本4：UNION後有cluster by、distribute by、sort by、order by或limit子句，設定set odps.sql.type.system.odps2=true;屬性。命令樣本如下：

SET odps.sql.type.system.odps2=true;
SELECT explode(ARRAY(3, 1)) AS (a) UNION ALL SELECT explode(ARRAY(0, 4, 2)) AS (a) ORDER BY a limit 3;

返回結果如下：

+------------+
| a          |
+------------+
| 0          |
| 1          |
| 2          |
+------------+

樣本5：UNION後有cluster by、distribute by、sort by、order by或limit子句，設定set odps.sql.type.system.odps2=false;屬性。命令樣本如下：

SET odps.sql.type.system.odps2=false;
SELECT explode(ARRAY(3, 1)) AS (a) UNION ALL SELECT explode(ARRAY(0, 4, 2)) AS (a) ORDER BY a limit 3;

返回結果如下：

+------------+
| a          |
+------------+
| 3          |
| 1          |
| 0          |
| 2          |
| 4          |
+------------+

補集

命令格式

--取補集不去重。
<select_statement1> EXCEPT ALL <select_statement2>;
<select_statement1> minus ALL <select_statement2>;
--取補集並去重。
<select_statement1> EXCEPT [DISTINCT] <select_statement2>;
<select_statement1> minus [DISTINCT] <select_statement2>;

說明

except和minus等效。

參數說明
- select_statement1、select_statement2：必填。select語句，格式請參見SELECT文法。
- distinct：可選。對取補集的結果去重。

使用樣本

樣本1：求資料集的補集，不去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b)
EXCEPT ALL 
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);
--等效於如下語句。
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b)
MINUS ALL 
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);

返回結果如下。

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 1          | 2          |
| 3          | 4          |
| 7          | 8          |
+------------+------------+

樣本2：求資料集的補集並去重。命令樣本如下：

SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b)
EXCEPT DISTINCT 
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);
--等效於如下語句。
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b)
MINUS DISTINCT 
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);
--等效於如下語句。
SELECT DISTINCT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) except all select * from values (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 2          |
| 7          | 8          |
+------------+------------+