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为等式表达式。
示例数据
为便于理解，本文为您提供源数据，基于源数据提供相关示例。创建表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表的全部记录。