全部产品
Search
文档中心

云原生大数据计算服务 MaxCompute:SEMI JOIN(半连接)

更新时间:Jul 19, 2023

MaxCompute支持半连接操作,通过右表过滤左表的数据,使右表的数据不出现在结果集中,可以提高查询性能。本文为您介绍半连接中left semi joinleft anti join两种语法的使用方法。

功能介绍

MaxCompute支持如下semi join操作:

  • left semi join

    join条件成立时,返回左表中的数据。如果左表中满足指定条件的某行数据在右表中出现过,则此行保留在结果集中。

    在MaxCompute中,与left semi join类似的操作为in subquery,请参见IN SUBQUERY。您可以自行选择其中一种方式。

  • left anti join

    join条件不成立时,返回左表中的数据。如果左表中满足指定条件的某行数据没有在右表中出现过,则此行保留在结果集中。

    在MaxCompute中,与left anti join类似的操作为not in subquery,但并不完全相同,请参见NOT IN SUBQUERY

semi join支持mapjoin hint,可以提高left semi joinleft anti join的性能。更多mapjoin hint信息,请参见MAPJOIN HINT

示例数据

为便于理解,本文为您提供源数据,基于源数据提供相关示例。创建表sale_detail和sale_detail_sj,并添加数据,命令示例如下:

--创建一张分区表sale_detail。
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_sj
(
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');
alter table sale_detail_sj 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_sj partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2),('s2','c2',100.2);

查询表sale_detail和sale_detail_sj中的数据,命令示例如下:

set odps.sql.allow.fullscan=true;
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      |
+------------+-------------+-------------+------------+------------+
select * from sale_detail_sj;
--返回结果。
+------------+-------------+-------------+------------+------------+
| 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      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

使用示例

  • 示例1:查询sale_detail表中,total_price出现在sale_detail_sj表中的数据集。命令示例如下:

    select * from sale_detail a left semi join sale_detail_sj b on a.total_price=b.total_price;

    返回结果如下:

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s2         | c2          | 100.2       | 2013       | china      |
    | s1         | c1          | 100.1       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+

    只会返回sale_detail中的数据,只要sale_detail的total_price在sale_detail_sj的total_price中出现过

  • 示例2:查询sale_detail表中,total_price没有出现在sale_detail_sj表中的数据集。命令示例如下:

    select * from sale_detail a left anti join sale_detail_sj b on a.total_price=b.total_price;

    返回结果如下:

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s3         | c3          | 100.3       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+

    只会返回sale_detail中的数据,只要sale_detail的total_price在sale_detail_sj的total_price中没有出现过