您可以使用JOIN操作连接两张表的查询结果。MaxCompute的JOIN操作分为左连接、右连接、全连接、内连接。支持多路连接,但不支持CROSS JOIN笛卡尔积,即无ON条件的连接。

命令格式如下。
join_table:
        table_reference join table_factor [join_condition]
        | table_reference {left outer|right outer|full outer|inner} 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条件中,从表的分区剪裁会生效,主表则不会生效即会全表扫描。更具体说明请看 分区剪裁失效的场景分析
  • 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;

以下示例中,表A是test_table_a,表B是test_table_b,使用了join查询和where过滤查询。查询分区大于20180101的数据中originid一致的记录数,使用left join可以保留左表中test_table_a的全表记录。正常情况下,如果您使用left join ,左表会保留全表记录;如果您使用right join,右表会保留全表记录。

错误示例如下。
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;
表中存在至少一个匹配时,inner join返回行。关键字inner可省略。
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;
连接条件,只允许and连接的等值条件。只有在MAPJOIN中,可以使用不等值连接或者使用or连接多个条件。
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链接条件,报错返回。
IMPLICIT JOIN:MaxCompute支持以下join方式。
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
--执行的效果相当于以下语句。
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
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的优先级,括号内的JOIN优先级较高。如下JOIN语句中,先执行src2 JOIN src3得出结果后再执行与src的JOIN。
SELECT * FROM src JOIN (src2 JOIN src3 on xxx) ON yyy;