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过滤条件，且join在where条件之前，先进行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);

--创建做关联的表。
create table shop as select shop_name, customer_id, total_price from sale_detail;

使用示例

  • 示例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：join与where相结合，查询两表中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表的全部记录。