全部产品
Search
文档中心

云原生大数据计算服务 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操作,请参见JOINSEMI JOIN(半连接)

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

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

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