全部产品
Search

搜索本产品

    云原生大数据计算服务 MaxCompute：JOIN

    文档中心

    云原生大数据计算服务 MaxCompute：JOIN

    更新时间：Sep 12, 2023

    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条件过滤，获取的结果是两个表的交集，而不是全表。

    • odps.task.sql.outerjoin.ppd参数可以控制outer join on条件中的非关联过滤条件是否会下推到join的输入中，该参数支持Project或Session级别设置。

      • 当参数值为false时，会把写在on中的非关联条件当做关联对应子查询中的where条件，这是一个非标准的行为，建议将非关联条件移到where子句中。

      • 当参数值为false时，如下两个SQL语句等价；当参数值为true时，两者不等价。

      SELECT A.*, B.* FROM A LEFT JOIN B ON A.c1 = B.c1 and A.c2='xxx';

SELECT A.*, B.* FROM (SELECT * FROM A WHERE c2='xxx') A LEFT JOIN B ON A.c1 = B.c1;

    注意事项

    使用JOIN时，会在计算中自动加入JOIN的key is not null的过滤条件，去除关联键为NULL的值所在行。

    使用限制

    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);

查询表sale_detail和sale_detail_jt中的数据，命令示例如下：
select * from sale_detail;
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+
select * from sale_detail_jt;
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

--创建做关联的表。
SET odps.sql.allow.fullscan=true;
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表的全部记录。