子查詢基本定義
select column_1, column_2 … from table_name
,但查詢的對象也可以是另外一個Select操作,如下所示:
select * from (select shop_name from sale_detail) a;
create table shop as select * from sale_detail;
select a.shop_name, a.customer_id, a.total_price from
(select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;
IN SUBQUERY / NOT IN SUBQUERY
IN SUBQUERY與LEFT SEMI JOIN類似。
SELECT * from mytable1 where id in (select id from mytable2);
--等效於
SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;
目前MaxCompute不僅支援IN SUBQUERY,還支援correlated條件。
SELECT * from mytable1 where id in (select id from mytable2 where value = mytable1.value);
其中子查詢中的where value = mytable1.value
即是一個correlated條件,舊版MaxCompute對於這種既引用了子查詢中源表,又引用了外層查詢源表的運算式時,會報錯。現在MaxCompute已經支援這種用法,這樣的過濾條件事實上構成了SEMI JOIN中ON條件的一部分。
NOT IN SUBQUERY類似於LEFT ANTI JOIN,但是也有顯著不同。
SELECT * from mytable1 where id not in (select id from mytable2);
--如果mytable2中的所有id都不為NULL,則等效於
SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;
如果mytable2中有任何為Null的列,則not in運算式會為Null,導致where條件不成立,無資料返回,此時與LEFT ANTI JOIN不同。
MaxCompute 1.0版本也支援[NOT] IN SUBQUERY不作為JOIN條件,例如出現在非WHERE語句中,或者雖然在Where語句中,但無法轉換為Join條件。當前MaxCompute 2.0版本仍然支援這種用法,但是此時因為無法轉換為SEMI JOIN而必須實現啟動一個單獨的作業來運行SUBQUERY,所以不支援correlated條件。
SELECT * from mytable1 where id in (select id from mytable2) OR value > 0;
因為Where中包含了or,導致無法轉換為SEMI JOIN,會單獨啟動作業執行子查詢。
SELECT * from sales_detail where ds in (select dt from sales_date);
其中的ds如果是分區列,則select dt from sales_date
會單獨啟動作業執行子查詢,而不會轉化為SEMIJOIN,執行後的結果會逐個與ds比較,sales_detail中ds值不在返回結果中的分區不會讀取,保證分區裁剪仍然有效。
EXISTS SUBQUERY/NOT EXISTS SUBQUERY
EXISTS SUBQUERY時,當SUBQUERY中有至少一行資料時,返回true,否則false。NOT EXISTS時則相反。
目前只支援含有correlated WHERE條件的子查詢。EXISTS SUBQUERY/NOT EXISTS SUBQUERY實現的方式是轉換為LEFT SEMI JOIN或者LEFT ANTI JOIN。
SELECT * from mytable1 where exists (select * from mytable2 where id = mytable1.id);
--等效於
SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;
SELECT * from mytable1 where not exists (select * from mytable2 where id = mytable1.id);
--等效於
SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;