交集,并集和补集的使用方法_云原生大数据计算服务 MaxCompute(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          |
+------------+------------+