MaxCompute SQL中的关联操作

当您编写MaxCompute的SQL语句，需要同时执行关联（JOIN）操作和对数据过滤时，您需要特别关注对于不同的JOIN类型，过滤条件所在位置不同，因为计算顺序的差异可能会导致查询结果不同。本文以一个具体示例介绍不同JOIN操作的过滤条件在不同位置时，对查询结果的影响。

概述

JOIN类型如下所示。

类型	说明

类型	说明
INNER JOIN	输出符合关联条件的数据。
LEFT JOIN	输出左表的所有记录，以及右表中符合关联条件的数据。右表中不符合关联条件的行，输出NULL。
RIGHT JOIN	输出右表的所有记录，以及左表中符合关联条件的数据。左表中不符合关联条件的行，输出NULL。
FULL JOIN	输出左表和右表的所有记录，对于不符合关联条件的数据，未关联的另一侧输出NULL。
LEFT SEMI JOIN	对于左表中的一条数据，如果右表存在符合关联条件的行，则输出左表。
LEFT ANTI JOIN	对于左表中的一条数据，如果右表中不存在符合关联条件的数据，则输出左表。

SQL语句中，同时存在JOIN和WHERE子句时，如下所示。

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

计算顺序如下：

子查询中的WHERE子句（即{subquery_where_condition}）。
JOIN子句中的关联条件（即{on_condition}）。
JOIN结果集中的WHERE子句（即{where_condition}）。

因此，对于不同的JOIN类型，过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果可能一致，也可能不一致。详情请参见场景说明。

示例数据

表A

建表语句如下。

CREATE TABLE A AS SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);

示例数据如下。

key	ds

key	ds
1	20180101
2	20180101
2	20180102

表B

建表语句如下。

CREATE TABLE B AS SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);

示例数据如下。

key	ds

key	ds
1	20180101
3	20180101
2	20180102

表A和表B的笛卡尔乘积

计算笛卡尔乘积SQL如下：

SET odps.sql.allow.cartesian=true;
SELECT * FROM A,B;

返回结果如下：

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 1    | 20180101 |
| 2    | 20180102 | 1    | 20180101 |
| 1    | 20180101 | 3    | 20180101 |
| 2    | 20180101 | 3    | 20180101 |
| 2    | 20180102 | 3    | 20180101 |
| 1    | 20180101 | 2    | 20180102 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

场景说明

INNER JOIN

INNER JOIN对左右表执行笛卡尔乘积，然后输出满足ON表达式的行。

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果是一致的。

情况1：过滤条件在子查询{subquery_where_condition}中。

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

结果如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

情况2：过滤条件在JOIN的关联条件{on_condition}中。

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

笛卡尔积结果为9条，满足关联条件的结果只有1条，如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

情况3：过滤条件在JOIN结果集的WHERE子句中。

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

笛卡尔积的结果为9条，满足关联条件的结果有3条，如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

对上述满足关联条件的结果执行JOIN结果集中的过滤条件A.ds='20180101' and B.ds='20180101'，结果只有1条，如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

LEFT JOIN

LEFT JOIN对左右表执行笛卡尔乘积，输出满足ON表达式的行。对于左表中不满足ON表达式的行，输出左表，右表输出NULL。

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果不一致。

左表的过滤条件在{subquery_where_condition}和{where_condition}中时，查询结果是一致的。
右表的过滤条件在{subquery_where_condition}和{on_condition}中时，查询结果是一致的。

情况1：过滤条件在子查询{subquery_where_condition}中。

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

结果如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
+------+------+------+------+

情况2：过滤条件在JOIN的关联条件{on_condition}中。

SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

笛卡尔积的结果有9条，满足关联条件的结果只有1条。左表输出剩余不满足关联条件的两条记录，右表输出NULL。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
+------+------+------+------+

情况3：过滤条件在JOIN结果集的WHERE子句中。

SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

笛卡尔积的结果为9条，满足ON条件的结果有3条。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

对上述结果执行JOIN结果集中的过滤条件A.ds='20180101' and B.ds='20180101'，结果只有1条。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

RIGHT JOIN
RIGHT JOIN和LEFT JOIN是类似的，只是左右表的区别。
- 过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}时，查询结果不一致。
- 右表的过滤条件，在{subquery_where_condition}和{where_condition}中时，查询结果一致。
- 左表的过滤条件，放在{subquery_where_condition}和{on_condition}中时，查询结果一致。

FULL JOIN

FULL JOIN对左右表执行笛卡尔乘积，然后输出满足关联条件的行。对于左右表中不满足关联条件的行，输出有数据表的行，无数据的表输出NULL。

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}时，查询结果不一致。

情况1：过滤条件在子查询{subquery_where_condition}中。

SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
FULL JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

结果如下。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

情况2：过滤条件在JOIN的关联条件{on_condition}中。

SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

笛卡尔积的结果有9条，满足关联条件的结果只有1条。对于左表不满足关联条件的两条记录输出左表数据，右表输出NULL。对于右表不满足关联条件的两条记录输出右表数据，左表输出NULL。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| NULL | NULL | 2    | 20180102 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

情况3：过滤条件在JOIN结果集的WHERE子句中。

SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

笛卡尔积的结果有9条，满足关联条件的结果有3条。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |

对于不满足关联条件的表输出数据，另一表输出NULL。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

对上述结果执行JOIN结果集中的过滤条件A.ds='20180101' and B.ds='20180101'，结果只有1条。

+------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

LEFT SEMI JOIN

LEFT SEMI JOIN将左表的每一条记录，和右表进行匹配。如果匹配成功，则输出左表。如果匹配不成功，则跳过。由于只输出左表，所以JOIN后的WHERE条件中不涉及右表。

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果是一致的。

情况1：过滤条件在子查询{subquery_where_condition}中。

SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

结果如下。

+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

情况2：过滤条件在JOIN的关联条件{on_condition}中。

SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

结果如下。

+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

情况3：过滤条件在JOIN结果集的WHERE子句中。

SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

符合关联条件的结果如下。

+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

对上述结果执行JOIN结果集中的过滤条件A.ds='20180101'，结果如下。

+------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

LEFT ANTI JOIN
LEFT ANTI JOIN将左表的每一条记录，和右表进行匹配。如果右表中的记录不匹配，则输出左表。由于只输出左表，所以JOIN后的WHERE条件中不能涉及右表。LEFT ANTI JOIN常常用来实现NOT EXISTS语义。
结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果不一致。
- 左表的过滤条件在{subquery_where_condition}和{where_condition}中时，查询结果是一致的。
- 右表的过滤条件在{subquery_where_condition}和{on_condition}中时，查询结果是一致的。
- 情况1：过滤条件在子查询{subquery_where_condition}中。
```
SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;
```
  结果如下。
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+
```
- 情况2：过滤条件在JOIN的关联条件{on_condition}中。
```
SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
```
  结果如下。
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+
```
- 情况3：过滤条件在JOIN结果集的WHERE子句中。
```
SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';
```
  左表中符合关联条件的数据如下。
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+
```
  对上述结果执行JOIN结果集中的过滤条件A.ds='20180101'，结果如下。
```
+------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+
```

注意事项

INNER JOIN/LEFT SEMI JOIN左右表的过滤条件不受限制。
LEFT JOIN/LEFT ANTI JOIN左表的过滤条件需放在{subquery_where_condition}或{where_condition}中，右表的过滤条件需放在{subquery_where_condition}或{on_condition}中。
RIGHT JOIN和LEFT JOIN相反，右表的过滤条件需放在{subquery_where_condition}或{where_condition}中，左表的过滤条件需放在{subquery_where_condition}或{on_condition}。
FULL OUTER JOIN的过滤条件只能放在{subquery_where_condition}中。

概述

示例数据

场景说明

注意事项

相关文档

销售支持

客户支持

聆听 & 举报违规

关于阿里云

阿里云全球基础设施

快速入门

全球办事处

2024年巴黎奥运会 New

罗兰加洛斯球场 - 岁月沉淀的辉煌 New

协和广场 - “打破”障碍 New

马恩河畔韦尔水上运动体育场 - 可持续的体育运动 New

国际广播中心 - 优质图像、声音和数据触达数十亿观众 New

客户成功案例 New

阿里云信任中心

合规计划

云合规资源

合规常见问题

云产品快报 New

Cloud Forward

新闻报道

阿里云电子期刊 New

阿里云调研分析报告

公告

阿里云出海业务 New

“橙”云出海服务联盟

Asia Accelerator Hot

信息合规

China Gateway - MLPS 2.0 合规 New

China Gateway - 网络

China Gateway - 全球应用加速 New

China Gateway - 安全

China Gateway - 数据安全 New

ICP支持服务 Hot

China Gateway - 全域数据中台 New

China Gateway - 一方数据中台 New

China Gateway - 业务中台 New

China Gateway - 智能客服解决方案 New

China Gateway - 在线教育

China Gateway - 域名注册

在阿里云工作

经验丰富的专业人士

学生和毕业生

免费试用

定价

优惠中心

降价

花钱更少，部署更多

FinOps

云服务器 ECS

轻量应用服务器 SAS

GPU 云服务器

无影云电脑 EDS

对象存储 OSS

云企业网 CEN

Web应用防火墙 WAF

域名

智能编码助手通义灵码

容器计算服务 ACS

办公安全平台 SASE

智能媒体服务 IMS

边缘安全加速 ESA（原DCDN）

智能媒体管理 IMM

专属钉钉

宜搭

大模型服务平台（百炼）

Apsara Prime - 轻松选择云产品

阿里云 ECS-满足您所有云主机需求

1TB CDN — 立即获取免费的 1 TB 出站流量套餐

安全 — 受到攻击？ 获取免费安全支持

短信服务 - 现已免费开放测试

云服务器 ECS Hot

云盒

计算巢服务

专有宿主机 Hot

弹性裸金属服务器（神龙）

GPU 云服务器 Featured

轻量应用服务器 SAS Hot

弹性伸缩

无影云手机 Beta

无影云电脑 EDS Featured

批量计算

弹性高性能计算 E-HPC

超级计算集群 SCC

安全 — 受到攻击？获取免费安全支持