當您需要在某個查詢的執行結果基礎上進一步執行查詢操作時，例如單獨計算彙總值、檢查某條記錄的存在性、篩選基於另一查詢結果的資料、關聯更新或刪除操作、簡化JOIN操作以擷取單個值、作為派生表給主查詢使用、提供排序或分組的依據以及進行逐行比較，可以通過子查詢操作實現。本文為您介紹MaxCompute支援的子查詢的定義與使用方法。
功能介紹
子查詢指在一個完整的查詢語句之中，嵌套若干個不同功能的小查詢，從而一起完成複雜查詢的一種編寫形式。MaxCompute支援的子查詢包含如下幾種：
基礎子查詢是在查詢的
FROM子句中使用子查詢，作為一個暫存資料表使用，可用於複雜計算或者在查詢中進行資料轉換。當需要匹配一組由子查詢返回的值時，可以在
WHERE子句中使用IN子查詢，適用於從主查詢中選擇匹配子查詢條件的行。用於從一個集合中排除另一個集合，在
WHERE子句中使用NOT IN子查詢時，會從主查詢結果中移除匹配子查詢的結果集的行。EXISTS子查詢在主查詢中用來判斷子查詢是否返回了任何結果。它適用於檢查一個記錄是否存在於子查詢中，而不關心子查詢具體返回了什麼。
與EXISTS子查詢相反，當子查詢不返回任何結果時，主查詢中的記錄才會被選擇。適用於從主查詢中選擇在子查詢中沒有匹配的行。
標量子查詢返回單個值的子查詢，通常用在SELECT列表中，或作為
WHERE或HAVING子句中的一個條件值，適用於計算特定的彙總值或者當需要從子查詢中提取單個資料點。
部分子查詢（例如SCALAR、IN、NOT IN、EXISTS或NOT EXISTS）在執行過程中會被轉換成JOIN進行計算，MAPJOIN是一種高效的JOIN演算法，若您確定SUBQUERY的計算結果為小表，可以在子查詢SUBQUERY語句中使用HINT來顯式地指定使用MAPJOIN演算法。詳情請參見SUBQUERY_MAPJOIN HINT。
樣本資料
為便於理解，本文為您提供來源資料，基於來源資料提供相關樣本。建立表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。
命令格式
select <select_expr> from (<select_statement>) [<sq_alias_name>];參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。select_statement：必填。子查詢語句。格式請參見SELECT文法。
sq_alias_name：可選。子查詢的別名。
table_name：必填。目標表名稱。
使用樣本
樣本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：在
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操作，其結果不變。SCALAR SUBQUERY還支援多列用法，例如：SELECT列為包含多列的SCALAR SUBQUERY運算式，只支援等值運算式；SELECT列可以為BOOLEAN運算式，只支援等值比較；where支援多列比較，只支援等值比較。
命令格式
格式1
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語句。
格式2
select (<select_statement>) from <table_name>;
參數說明
select_expr：必填。格式為
col1_name, col2_name, Regex,...，表示待查詢的普通列、分區列或Regex。table_name1、table_name2：必填。表的名稱。
col_name：必填。表的列名。
標量運算子：必填。例如大於（>）、小於（<）、等於（=）、大於等於（>=）或小於等於（<=）等。
scalar_value：必填。標量值。
select_statement：必填。子查詢語句。如果子查詢語句為格式2，子查詢結果必須只有一行。格式請參見SELECT文法。
使用限制
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;
使用樣本
樣本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 | +------------+------------+------------+樣本3：使用格式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 | +------------+-------------+-------------+------------+------------+
相關文檔
大量或不當使用子查詢可能會導致查詢變慢，尤其是在巨量資料環境中，可能需要考慮子查詢的替代方法，如使用暫存資料表、物化視圖或將多個子查詢重構為JOIN，以提高查詢效率，請參見物化視圖推薦與管理、JOIN。