全部產品
Search
文件中心

MaxCompute:SEMI JOIN(半串連)

更新時間:Feb 28, 2024

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中沒有出現過