全部产品
Search

搜索本产品

    云原生大数据计算服务 MaxCompute：MaxCompute SQL中的关联操作

    文档中心

    云原生大数据计算服务 MaxCompute：MaxCompute SQL中的关联操作

    更新时间：Dec 19, 2023

    当您编写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。