MaxCompute支援半串連操作,通過右表過濾左表的資料,使右表的資料不出現在結果集中,可以提高查詢效能。本文為您介紹半串連中left semi join
和left 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 join
和left 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中沒有出現過。