AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 实践教程 最佳实践 SQL MaxCompute SQL中的关联操作

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}

计算顺序如下：

  1. 子查询中的WHERE子句（即{subquery_where_condition}）。

  2. JOIN子句中的关联条件（即{on_condition}）。

  3. 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

    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

    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}中。

相关文档

  • MaxCompute支持的基础JOIN操作，请参见JOIN、SEMI JOIN（半连接）。

  • 大表JOIN小表场景显式指定mapjoinHint提示以提升查询性能，请参见MAPJOIN HINT。

  • 大表Join中表场景使用DISTRIBUTED MAPJOIN以提升查询性能，请参见DISTRIBUTED MAPJOIN。

  • 两张表JOIN存在热点，导致出现长尾问题时，SkewJoin Hint可以获取两张表的热点Key，以加快JOIN的执行速度，请参见SKEWJOIN HINT。

上一篇：行转列及列转行最佳实践下一篇：Hash Clustering
文档推荐