MaxCompute支持通过join操作连接表并返回符合连接条件和查询条件的数据。本文为您介绍左连接、右连接、全连接、内连接、自然连接、隐式连接和多路连接的使用方法。

功能介绍

MaxCompute支持如下join操作:
  • 左连接(left outer join

    可简写为left join。返回左表中的所有记录,即使右表中没有与之匹配的记录。

    说明 通常,join操作的左边为大表,右表为小表,如果右表值不唯一,建议不要连续使用过多left join,以免在join过程中产生数据膨胀,导致作业停滞。
  • 右连接(right outer join

    可简写为right join。返回右表中的所有记录,即使左表中没有与之匹配的记录。

  • 全连接(full outer join

    可简写为full join。返回左右表中的所有记录。

  • 内连接(inner join

    关键字inner可以省略。左右表中至少存在一个匹配行时,inner join返回数据行。

  • 自然连接(natural join

    参与join的两张表根据字段名称自动决定连接字段。支持outer natural join,支持使用using子句执行join,输出字段中公共字段只出现一次。

  • 隐式连接

    即不指定join关键字执行连接。

  • 多路连接

    多路join连接。支持通过括号指定join的优先级,括号内的join优先级较高。

说明 如果SQL语句中包含where过滤条件,且joinwhere条件之前,先进行join操作,然后对join的结果执行where条件过滤,获取的结果是两个表的交集,而不是全表。

使用限制

join操作的使用限制如下:
  • MaxCompute不支持cross join,即无on条件的连接。
  • 只允许出现and连接的等值条件。您可以通过mapjoin操作使用不等值连接或or连接多个条件,详情请参见MAPJOIN

命令格式

<table_reference> join <table_factor> [<join_condition>]
| <table_reference> {left outer|right outer|full outer|inner|natural} join <table_reference> <join_condition>
  • table_reference:必填。待执行join操作的左表查询语句。格式为table_name [alias] | table_query [alias] |...
  • table_factor:必填。待执行join操作的右表或表查询语句。格式为table_name [alias] | table_subquery [alias] |...
  • join_condition:可选。join连接条件,是一个或多个等式表达式组合。格式为on equality_expression [and equality_expression]...equality_expression为等式表达式。
说明 如果分区裁剪条件置于where子句中,分区裁剪会生效;如果置于on子句中,从表的分区裁剪会生效,主表的分区剪裁不会生效即会全表扫描。详情请参见分区剪裁合理性评估

示例数据

为便于理解,本文为您提供源数据,基于源数据提供相关示例。创建表sale_detail和sale_detail_jt,并添加数据,命令示例如下:
--创建分区表sale_detail和sale_detail_jt。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);
create table if not exists sale_detail_jt
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

--向源表增加分区。
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');
alter table sale_detail_jt add partition (sale_date='2013', region='china');

--向源表追加数据。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
insert into sale_detail_jt partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

使用示例

  • 示例1:左连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --由于表sale_detail_jt及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
    select a.shop_name as ashop, b.shop_name as bshop from sale_detail_jt a 
           left outer join sale_detail b on a.shop_name=b.shop_name;
    返回结果如下:
    +------------+------------+
    | ashop      | bshop      |
    +------------+------------+
    | s2         | s2         |
    | s1         | s1         |
    | s5         | NULL       |
    +------------+------------+
  • 示例2:右连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --由于表sale_detail_jt及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
    select a.shop_name as ashop, b.shop_name as bshop from sale_detail_jt a 
           right outer join sale_detail b on a.shop_name=b.shop_name;
    返回结果如下:
    +------------+------------+
    | ashop      | bshop      |
    +------------+------------+
    | NULL       | s3         |
    | NULL       | s6         |
    | NULL       | null       |
    | s2         | s2         |
    | NULL       | s7         |
    | s1         | s1         |
    +------------+------------+
  • 示例3:全连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --由于表sale_detail_jt及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
    select a.shop_name as ashop, b.shop_name as bshop from sale_detail_jt a 
           full outer join sale_detail b on a.shop_name=b.shop_name;
    返回结果如下:
    +------------+------------+
    | ashop      | bshop      |
    +------------+------------+
    | NULL       | s3         |
    | NULL       | s6         |
    | s2         | s2         |
    | NULL       | null       |
    | NULL       | s7         |
    | s1         | s1         |
    | s5         | NULL       |
    +------------+------------+
  • 示例4:内连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --由于表sale_detail_jt及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
    select a.shop_name as ashop, b.shop_name as bshop from sale_detail_jt a 
           inner join sale_detail b on a.shop_name=b.shop_name;
    返回结果如下:
    +------------+------------+
    | ashop      | bshop      |
    +------------+------------+
    | s2         | s2         |
    | s1         | s1         |
    +------------+------------+
  • 示例5:自然连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --自然连接。
    select * from sale_detail_jt natural join sale_detail;
    --等效于如下语句。
    select sale_detail_jt.shop_name as shop_name, sale_detail_jt.customer_id as customer_id, sale_detail_jt.total_price as total_price, sale_detail_jt.sale_date as sale_date, sale_detail_jt.region as region from sale_detail_jt 
    inner join sale_detail 
    on sale_detail_jt.shop_name=sale_detail.shop_name and sale_detail_jt.customer_id=sale_detail.customer_id and sale_detail_jt.total_price=sale_detail.total_price and sale_detail_jt.sale_date=sale_detail.sale_date and sale_detail_jt.region=sale_detail.region;
    返回结果如下:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+
  • 示例6:隐式连接。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --隐式连接。
    select * from sale_detail_jt, sale_detail where sale_detail_jt.shop_name = sale_detail.shop_name;
    --等效于如下语句。
    select * from sale_detail_jt join sale_detail on sale_detail_jt.shop_name = sale_detail.shop_name;
    返回结果如下:
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    |
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
    | s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
    | s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
  • 示例7:多路连接,不指定优先级。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --由于表sale_detail_jt及sale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。
    select a.* from sale_detail_jt 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;
    返回结果如下:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s5         | c2          | 100.2       | 2013       | china      |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | s1         | c1          | 100.1       | 2013       | china      |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    | s2         | c2          | 100.2       | 2013       | china      |
    | NULL       | NULL        | NULL        | NULL       | NULL       |
    +------------+-------------+-------------+------------+------------+
  • 示例7:多路连接,通过括号指定优先级。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --多路连接,通过括号指定优先级。
    select * from shop join (sale_detail_jt join sale_detail on sale_detail_jt.shop_name = sale_detail.shop_name) on shop.shop_name=sale_detail_jt.shop_name;
    返回结果如下:
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    | shop_name3 | customer_id3 | total_price3 | sale_date3 | region3    |
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
    | s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
    | s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
    +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
  • 示例8:joinwhere相结合,查询两表中region为china且shop_name一致的记录数,保留sale_detail表的全部记录。命令示例如下:
    --分区表需要开启全表扫描功能,否则join操作会执行失败。
    set odps.sql.allow.fullscan=true;
    --执行SQL语句。
    select a.shop_name
            ,a.customer_id
            ,a.total_price
            ,b.total_price
    from    (select * from sale_detail where region = "china") a
    left join  (select * from sale_detail_jt where region = "china") b
    on      a.shop_name = b.shop_name;
    返回结果如下:
    +------------+-------------+-------------+--------------+
    | shop_name  | customer_id | total_price | total_price2 |
    +------------+-------------+-------------+--------------+
    | s1         | c1          | 100.1       | 100.1        |
    | s2         | c2          | 100.2       | 100.2        |
    | s3         | c3          | 100.3       | NULL         |
    +------------+-------------+-------------+--------------+
    错误命令示例如下:
    select a.shop_name
            ,a.customer_id
            ,a.total_price
            ,b.total_price
    from    sale_detail a
    left join  sale_detail_jt b
    on      a.shop_name = b.shop_name
    where   a.region = "china" and b.region = "china";
    返回结果如下:
    +------------+-------------+-------------+--------------+
    | shop_name  | customer_id | total_price | total_price2 |
    +------------+-------------+-------------+--------------+
    | s1         | c1          | 100.1       | 100.1        |
    | s2         | c2          | 100.2       | 100.2        |
    +------------+-------------+-------------+--------------+

    从返回结果可看到,获取的结果是两个表的交集,非sale_detail表的全部记录。