本文为您介绍如何使用JOIN操作连接表并返回符合连接条件和查询条件的数据等信息。MaxCompute的JOIN操作分为左连接、右连接、全连接、内连接、自然连接、隐式连接和多路连接。MaxCompute不支持CROSS JOIN操作,即无ON条件的连接。

语法格式

命令格式如下。
join_table:
        table_reference JOIN table_factor [join_condition]
        | table_reference {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN table_reference join_condition
    table_reference:
        table_factor
        | join_table
    table_factor:
        tbl_name [alias]
        | table_subquery alias
        | ( table_references )
    join_condition:
        ON equality_expression ( AND equality_expression )
说明
  • equality_expression是一个等式表达式。
  • 如果分区剪裁条件置于WHERE语句中,分区剪裁会生效;如果置于ON语句中,从表的分区剪裁会生效,主表的分区剪裁不会生效即会全表扫描。详情请参见分区剪裁失效的场景分析

语法说明

  • JOIN操作类型如下:
    • LEFT OUTER JOIN:左连接,可简写为LEFT JOIN。返回左表中的所有记录,即使右表中没有与之匹配的记录。
      SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM shop a
              LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;--由于表shop及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
      说明 如果右表值不唯一,建议不要连续使用过多LEFT JOIN,以免在JOIN过程中产生数据膨胀,导致作业停滞。
    • RIGHT OUTER JOIN:右连接,可简写为RIGHT JOIN。返回右表中的所有记录,即使左表中没有与之匹配的记录。
      SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM shop a
              RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;
    • FULL OUTER JOIN:全连接,可简写为FULL JOIN。返回左右表中的所有记录。
      SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM shop a
              FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;
    • INNER JOIN:内连接。关键字INNER可以省略。表中至少存在一个匹配值时,INNER JOIN返回数据行。
      SELECT a.shop_name FROM shop a INNER JOIN sale_detail b ON a.shop_name=b.shop_name;
      SELECT a.shop_name FROM shop a JOIN sale_detail b ON a.shop_name=b.shop_name;
    • NATURAL JOIN:自然连接,即参与JOIN的两张表根据字段名称自动决定连接字段。支持OUTER NATURAL JOIN,支持使用Using子句执行JOIN,输出字段中公共字段只出现一次。假设存在表src(key1, key2, a1, a2),表src2(key1, key2, b1, b2)。
      SELECT * FROM src NATURAL JOIN src2;
      --由于src和src2有两个同名字段(key1, key2),所以上面的JOIN操作相当于:
      SELECT src.key1 AS key1, src.key2 AS key2, src.a1, src.a2, src2.b1, src2.b2 FROM src INNER JOIN src2 ON src.key1 = src2.key1 AND src.key2 = src2.key2;
    • 隐式连接,即不指定JOIN关键字执行连接。
      SELECT * FROM table1, table2 WHERE table1.id = table2.id;
      --执行的效果相当于以下语句。
      SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
  • 连接条件:只允许AND连接的等值条件。您可以通过MAPJOIN操作使用不等值连接或OR连接多个条件,详情请参见MAPJOIN
    --支持多路JOIN连接示例。
    SELECT a.* FROM shop a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name
            FULL OUTER JOIN sale_detail c ON a.shop_name=c.shop_name;
    --不支持不等值JOIN连接条件,返回报错。    
    SELECT a.* FROM shop a JOIN sale_detail b ON a.shop_name != b.shop_name;  
  • JOIN的优先级:支持通过括号指定JOIN的优先级,括号内的JOIN优先级较高。

    如下JOIN语句中,先执行src2 JOIN src3,基于结果再与src执行JOIN操作。

    SELECT * FROM src JOIN (src2 JOIN src3 ON xxx) ON yyy;

示例

假设表A为test_table_a,表B为test_table_b。查询两表中分区大于20180101且originid一致的记录数。使用LEFT JOIN可以保留左表test_table_a的全表记录。

错误示例如下。
SELECT s.id
        ,s.name
        ,s.origin
        ,d.value
FROM    test_table_a s
LEFT JOIN   test_table_b d
ON      s.origin = d.id
WHERE   s.ds > "20180101" AND d.ds>"20180101";

如果JOINWHERE条件之前,先进行JOIN操作,然后对JOIN的结果执行WHERE条件过滤,获取的结果是两个表的交集,而不是全表。

您可以通过如下修正后的SQL语句显示全表。
SELECT s.id
        ,s.name
        ,s.origin
        ,d.value
FROM  (SELECT * FROM  test_table_a WHERE ds > "20180101" ) s
LEFT JOIN (SELECT * FROM  test_table_b WHERE ds > "20180101") d
ON s.origin = d.id;