您可以使用JOIN操作连接两张表的查询结果。MaxCompute的JOIN操作分为左连接、右连接、全连接、内连接。支持多路连接,但不支持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:左连接。返回左表中的所有记录,即使右表中没有与之匹配的记录。
      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:右连接。返回右表中的所有记录,即使左表中没有与之匹配的记录。
      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:全连接。返回左右表中的所有记录。
      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;
    • Implicit Join:隐式连接,即不指定Join关键字执行连接。
      SELECT * FROM table1, table2 WHERE table1.id = table2.id;
      --执行的效果相当于以下语句。
      SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
  • 连接条件:只允许and连接的等值条件。只有在MAPJOIN中,可以使用不等值连接或者使用or连接多个条件。
    --支持多路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;