当您需要在某个查询的执行结果基础上进一步执行查询操作时，例如单独计算聚合值、检查某条记录的存在性、筛选基于另一查询结果的数据、关联更新或删除操作、简化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。
命令格式
格式1
select <select_expr> from (<select_statement>) [<sq_alias_name>];格式2
select (<select_statement>) from <table_name>;
参数说明
select_expr：必填。格式为
col1_name, col2_name, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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, 正则表达式,...，表示待查询的普通列、分区列或正则表达式。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 | +------------+------------+------------+
相关文档
大量或不当使用子查询可能会导致查询变慢，尤其是在大数据环境中，可能需要考虑子查询的替代方法，如使用临时表、物化视图或将多个子查询重构为JOIN，以提高查询效率，请参见物化视图推荐与管理、JOIN。