當您需要在某個查詢的執行結果基礎上進一步執行查詢操作時，可以通過子查詢操作實現。本文為您介紹MaxCompute支援的子查詢的定義與使用方法。
功能介紹
子查詢指在一個完整的查詢語句之中，嵌套若干個不同功能的小查詢，從而一起完成複雜查詢的一種編寫形式。MaxCompute支援的子查詢包含如下幾種：
樣本資料
為便於理解，本文為您提供來源資料，基於來源資料提供相關樣本。建立表sale_detail，並添加資料，命令樣本如下：
--建立一張分區表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);
--向源表增加分區。
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');
--向源表追加資料。
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);查詢分區表sale_detail中的資料，命令樣本如下：
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 |
| null | c5 | NULL | 2014 | shanghai |
| s6 | c6 | 100.4 | 2014 | shanghai |
| s7 | c7 | 100.5 | 2014 | shanghai |
+------------+-------------+-------------+------------+------------+基礎子查詢
普通查詢操作的對象是目標表，但是查詢的對象也可以是另一個select語句，這種查詢為子查詢。在from子句中，子查詢可以被當作一張表，與其他表或子查詢進行join操作。join詳情請參見JOIN。
命令格式
格式1
select <select_expr> from (<select_statement>) [<sq_alias_name>];格式2
select (<select_statement>) from <table_name>;
參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。select_statement：必填。子查詢語句。如果子查詢語句為格式2，子查詢結果必須只有一行。格式請參見SELECT文法。
sq_alias_name：可選。子查詢的別名。
table_name：必填。目標表名稱。
使用樣本
樣本1：使用格式1子查詢文法。命令樣本如下。
set odps.sql.allow.fullscan=true; select * from (select shop_name from sale_detail) a;返回結果如下：
+------------+ | shop_name | +------------+ | s1 | | s2 | | s3 | | null | | s6 | | s7 | +------------+樣本2：使用格式2子查詢文法。命令樣本如下。
set odps.sql.allow.fullscan=true; select (select * from sale_detail where shop_name='s1') from sale_detail;返回結果如下：
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | +------------+-------------+-------------+------------+------------+樣本3：使用格式1子查詢文法，在
from子句中，子查詢可以被當做一張表，與其他的表或子查詢進行join操作。命令樣本如下。--先建立一張表，再執行join操作。 create table shop as select shop_name,customer_id,total_price 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;返回結果如下：
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | null | c5 | NULL | | s6 | c6 | 100.4 | | s7 | c7 | 100.5 | | s1 | c1 | 100.1 | | s2 | c2 | 100.2 | | s3 | c3 | 100.3 | +------------+-------------+-------------+
IN SUBQUERY
in subquery與left semi join用法類似。
命令格式
格式1
select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>); --等效於leftsemijoin如下語句。 select<select_expr1>from<table_name1><alias_name1>leftsemijoin<table_name2><alias_name2>on<alias_name1>.<select_expr2>=<alias_name2>.<select_expr3>;說明如果
select_expr2為分區列，則select <select_expr2> from <table_name2>會單獨啟動作業執行子查詢，而不會轉化為semi join。執行後的結果會依次與select_expr2比較，table_name1中select_expr2值不在返回結果中的分區將不會被讀取，保證分區裁剪仍然有效。格式2
MaxCompute不僅支援
in subquery，還支援Correlated條件。子查詢中的where <table_name2_colname> = <table_name1>.<colname>即是一個Correlated條件。MaxCompute 1.0版本不支援這種既引用了子查詢中源表，又引用了外層查詢源表的運算式。MaxCompute 2.0已支援這種用法，這種過濾條件構成了semi join中on條件的一部分。select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>where <table_name1>.<col_name>=<table_name2>.<col_name>);說明MaxCompute支援
in subquery不作為join條件，例如出現在非where語句中，或雖然在where語句中，但無法轉換為join條件情境。此時無法轉換為semi join，必須啟動一個單獨的作業運行子查詢，不支援Correlated條件。格式3
在上述能力及限制的基礎上，相容PostgreSQL支援多列的需求，相較於拆分為多個Subquery的實現方式，會減少一次JOIN過程並節省計算資源。支援的多列用法如下：
in後的運算式可以為簡單的SELECT多列語句。in後的運算式中可以使用彙總函式。更多彙總函式資訊，請參見彙總函式。in後的運算式可以為常量。
參數說明
select_expr1：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
select_expr2、select_expr3：必填。表示table_name1和table_name2互相映射的列名。
col_name：必填。表的列名。
注意事項
使用
IN的子查詢時，在子查詢的返回結果中會自動去除NULL值的記錄。使用樣本
樣本1：使用格式1子查詢文法。命令樣本如下。
set odps.sql.allow.fullscan=true; select * from sale_detail where total_price in (select total_price from shop);返回結果如下：
+-----------+-------------+-------------+-----------+--------+ | 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 | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +-----------+-------------+-------------+-----------+--------+樣本2：使用格式2子查詢文法。命令樣本如下。
set odps.sql.allow.fullscan=true; select * from sale_detail where total_price in (select total_price from shop where customer_id = shop.customer_id);返回結果如下：
+-----------+-------------+-------------+-----------+--------+ | 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 | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +-----------+-------------+-------------+-----------+--------+樣本3：SELECT多列情境。命令樣本如下。
--為方便理解，此處重新構造樣本資料。 create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint); create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint); insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1); insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1); --情境一：in後的運算式為簡單的SELECT多列語句。 select a, b from t1 where (c, d) in (select a, b from t2 where e = t1.e); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | +------------+------------+ --情境二：in後的運算式使用彙總函式。 select a, b from t1 where (c, d) in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 2 | 2 | +------------+------------+ --情境三：in後的運算式為常量。 select a, b from t1 where (c, d) in ((1, 3), (1, 1)); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 2 | 2 | | 3 | 1 | +------------+------------+
NOT IN SUBQUERY
not in subquery與left anti join用法類似，但並不完全相同。如果查詢目標表的指定列名中有任意一行為NULL，則not in運算式值為NULL，導致where條件不成立，無資料返回，這點與left anti join不同。
命令格式
格式1
select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>); --等效於left anti join如下語句。 select <select_expr1> from <table_name1> <alias_name1> left anti join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;說明如果
select_expr2為分區列，則select <select_expr2> from <table_name2>會單獨啟動作業執行子查詢，而不會轉化為anti join。執行後的結果會依次與select_expr2比較，table_name1中select_expr2值不在返回結果中的分區將不會被讀取，保證分區裁剪仍然有效。格式2
MaxCompute不僅支援
not in subquery，還支援Correlated條件。子查詢中的where <table_name2_colname> = <table_name1>.<colname>即是一個Correlated條件。MaxCompute 1.0版本不支援這種既引用了子查詢中源表，又引用了外層查詢源表的運算式。MaxCompute 2.0已支援這種用法，這種過濾條件構成了anti join中on條件的一部分。select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);說明MaxCompute支援
not in subquery不作為join條件，例如出現在非where語句中，或雖然在where語句中，但無法轉換為join條件情境。此時無法轉換為anti join，必須啟動一個單獨的作業運行子查詢，不支援Correlated條件。格式3
在上述能力的基礎上，相容PostgreSQL支援多列的需求，相較於拆分為多個Subquery的實現方式，會減少一次JOIN過程並節省計算資源。支援的多列情境如下：
not in後的運算式可以為簡單的SELECT多列語句。not in後的運算式中可以使用彙總函式。更多彙總函式資訊，請參見彙總函式。not in後的運算式可以為常量。
參數說明
select_expr1：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
select_expr2、select_expr3：必填。表示table_name1和table_name2互相映射的列名。
col_name：必填。表的列名。
注意事項
使用
NOT IN的子查詢時，在子查詢的返回結果中會自動去除NULL值的記錄。使用樣本
樣本1：使用格式1子查詢文法。命令樣本如下。
--建立一張新表shop1並追加資料。 create table shop1 as select shop_name,customer_id,total_price from sale_detail; insert into shop1 values ('s8','c1',100.1); select * from shop1 where shop_name not in (select shop_name from sale_detail);返回結果如下：
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+樣本2：使用格式2子查詢文法。命令樣本如下。
set odps.sql.allow.fullscan=true; select * from shop1 where shop_name not in (select shop_name from sale_detail where customer_id = shop1.customer_id);返回結果如下：
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+樣本3：
not in subquery不作為join條件。命令樣本如下。set odps.sql.allow.fullscan=true; select * from shop1 where shop_name not in (select shop_name from sale_detail) and total_price < 100.3;因為
where中包含了and，所以無法轉換為anti join，會單獨啟動作業執行子查詢。返回結果如下：
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+樣本4：假設查詢表中有任意一行為NULL，則無資料返回。命令樣本如下。
--建立一張新表sale並追加資料。 create table if not exists sale ( shop_name string, customer_id string, total_price double ) partitioned by (sale_date string, region string); alter table sale add partition (sale_date='2013', region='china'); insert into sale partition (sale_date='2013', region='china') values ('null','null',null),('s2','c2',100.2),('s3','c3',100.3),('s8','c8',100.8); set odps.sql.allow.fullscan=true; select * from sale where shop_name not in (select shop_name from sale_detail);返回結果如下：
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ +------------+-------------+-------------+------------+------------+樣本5：SELECT多列情境。命令樣本如下。
--為方便理解，此處重新構造樣本資料。與IN SUBQUERY中的樣本資料相同。 create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint); create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint); insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1); insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1); --情境一：not in後的運算式為簡單的SELECT多列語句。 select a, b from t1 where (c, d) not in (select a, b from t2 where e = t1.e); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 2 | 1 | | 1 | 1 | +------------+------------+ --情境二：not in後的運算式使用彙總函式。 select a, b from t1 where (c, d) not in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 1 | 3 | | 3 | 1 | | 2 | 1 | | 1 | 1 | +------------+------------+ --情境三：not in後的運算式為常量。 select a, b from t1 where (c, d) not in ((1, 3), (1, 1)); --返回結果如下。 +------------+------------+ | a | b | +------------+------------+ | 1 | 3 | | 2 | 1 | | 1 | 1 | +------------+------------+
EXISTS SUBQUERY
使用exists subquery時，當子查詢中有至少一行資料時，返回True，否則返回False。
MaxCompute只支援含有Correlated條件的where子查詢。exists subquery實現的方式是轉換為left semi join。
命令格式
select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
col_name：必填。表的列名。
注意事項
使用
EXISTS的子查詢時，在子查詢的返回結果中會自動去除NULL值的記錄。使用樣本
set odps.sql.allow.fullscan=true; select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id); --等效於以下語句。 select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;返回結果如下：
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | null | c5 | NULL | 2014 | shanghai | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
NOT EXISTS SUBQUERY
使用not exists subquery時，當子查詢中無資料時，返回True，否則返回False。
MaxCompute只支援含有Correlated條件的where子查詢。not exists subquery實現的方式是轉換為left anti join。
命令格式
select <select_expr> from <table_name1> where not exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
col_name：必填。表的列名。
注意事項
使用
NOT EXISTS的子查詢時，在子查詢的返回結果中會自動去除NULL值的記錄。使用樣本
set odps.sql.allow.fullscan=true; select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name); --等效於以下語句。 select * from sale_detail a left anti join shop b on a.shop_name = b.shop_name;返回結果如下：
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ +------------+-------------+-------------+------------+------------+
SCALAR SUBQUERY
當子查詢的輸出結果為單行單列時，可以做為標量使用，即可以參與標量運算。
所有的滿足一行一列輸出值的子查詢，都可以按照如下命令格式重寫。如果查詢的結果只有一行，在外面嵌套一層max或min操作，其結果不變。
命令格式
select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <標量運算子> <scalar_value>; --等效於以下語句。 select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <標量運算子> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;說明select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>的輸出結果是一個Row Set，可以判斷出這條語句的輸出有且僅有一行一列。因此它可以作為標量。但在實現過程中，會儘可能地轉成join來處理。可以作為標量的子查詢必須是在編譯階段就能夠確認其返回結果只有一行一列的查詢。如果一個子查詢只能在實際運行過程中才能判斷出它只產生一行資料（而無法在編譯過程中判斷），則編譯器會報錯。目前編譯器能夠接受的語句需滿足兩個特徵：
子查詢的
select列表裡面用了彙總函式，且不在資料表值函式的參數列表中。子查詢中包含彙總函式的這一層查詢沒有
group by語句。
在上述能力及限制的基礎上，SCALAR SUBQUERY還支援多列用法如下：
SELECT列為包含多列的SCALAR SUBQUERY運算式，只支援等值運算式。
SELECT列可以為BOOLEAN運算式，只支援等值比較。
where支援多列比較，只支援等值比較。
參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
col_name：必填。表的列名。
標量運算子：必填。例如大於（>）、小於（<）、等於（=）、大於等於（>=）或小於等於（<=）等。
scalar_value：必填。標量值
使用限制
scalar subquery支援引用外層查詢的列，當嵌套多層scalar subquery時，只支援引用直接外層的列。--允許的操作。 select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; --不允許的操作，不能在子查詢的select中引用外部查詢的列。 select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3;scalar subquery只能在where子句中使用。--不能在子查詢的select中引用。 select * from t1 where (select t1.b + count(*) from t2) = 3; --select返回列不允許引用外層的列。 select(selectcount(t1.a)fromt2wheret2.a=t1.a)fromt1; select(selectt1.afromt2wheret2.a=t1.a)fromt1;
使用樣本
樣本1：常見用法，命令樣本如下。
set odps.sql.allow.fullscan=true; select * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;返回結果如下：
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s1 | c1 | 100.1 | | s2 | c2 | 100.2 | | s3 | c3 | 100.3 | | null | c5 | NULL | | s6 | c6 | 100.4 | | s7 | c7 | 100.5 | +------------+-------------+-------------+樣本2：SELECT多列情境。命令樣本如下。
--為方便理解，此處重新構造樣本資料。 create table if not exists ts(a bigint,b bigint,c double); create table if not exists t(a bigint,b bigint,c double); insert into table ts values (1,3,4.0),(1,3,3.0); insert into table t values (1,3,4.0),(1,3,5.0); --情境一：SELECT列為包含多列的SCALAR SUBQUERY運算式，只支援等值運算式。錯誤用法：select (select a, b from t where c > ts.c) as (a, b), a from ts; select (select a, b from t where c = ts.c) as (a, b), a from ts; --返回結果如下。 +------------+------------+------------+ | a | b | a2 | +------------+------------+------------+ | 1 | 3 | 1 | | NULL | NULL | 1 | +------------+------------+------------+ --情境二：SELECT列為BOOLEAN運算式，只支援等值比較。錯誤用法：select (a,b) > (select a,b from ts where c = t.c) from t; select (a,b) = (select a,b from ts where c = t.c) from t; --返回結果如下。 +------+ | _c0 | +------+ | true | | false | +------+ --情境三：where支援多列比較，只支援等值比較。錯誤用法：select * from t where (a,b) > (select a,b from ts where c = t.c); select * from t where c > 3.0 and (a,b) = (select a,b from ts where c = t.c); --返回結果如下。 +------------+------------+------------+ | a | b | c | +------------+------------+------------+ | 1 | 3 | 4.0 | +------------+------------+------------+ select * from t where c > 3.0 or (a,b) = (select a,b from ts where c = t.c); --返回結果如下。 +------------+------------+------------+ | a | b | c | +------------+------------+------------+ | 1 | 3 | 4.0 | | 1 | 3 | 5.0 | +------------+------------+------------+