当您需要在某个查询的执行结果基础上进一步执行查询操作时，可以通过子查询操作实现。本文为您介绍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);

基础子查询

普通查询操作的对象是目标表，但是查询的对象也可以是另一个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子查询语法。命令示例如下：
      select * from (select shop_name from sale_detail) a;
      返回结果如下：
      +------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
| null       |
| s6         |
| s7         |
+------------+
    • 示例2：使用格式2子查询语法。命令示例如下：
      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_expr2> from <table_name2>);
--等效于left semi join如下语句。
select <select_expr1> from <table_name1> <alias_name1> left semi join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;
      说明 如果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_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
      说明 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：必填。表示table_name1和table_name2互相映射的列名。
    • col_name：必填。表的列名。
  • 使用示例
    • 示例1：使用格式1子查询语法。命令示例如下：
      select * from sale_detail where shop_name in (select shop_name from shop);
      返回结果如下：
      +------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+
    • 示例2：使用格式2子查询语法。命令示例如下：
      select * from shop where shop_name in (select shop_name from sale_detail where customer_id = shop.customer_id);
      返回结果如下：
      +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s3         | c3          | 100.3       |
| s7         | c7          | 100.5       |
| s2         | c2          | 100.2       |
| null       | c5          | NULL        |
| s6         | c6          | 100.4       |
| s1         | c1          | 100.1       |
+------------+-------------+-------------+
    • 示例3：in subquery不作为join条件。命令示例如下：
      select * from sale_detail where shop_name in (select shop_name from shop) and total_price > 100.3;

      因为where中包含了and，所以无法转换为semi join，会单独启动作业执行子查询。

      返回结果如下：
      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+
    • 示例4：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> 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：必填。表示table_name1和table_name2互相映射的列名。
    • col_name：必填。表的列名。
  • 使用示例
    • 示例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子查询语法。命令示例如下：
      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条件。命令示例如下：
      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);

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：必填。表的列名。
  • 使用示例
    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：必填。表的列名。
  • 使用示例
    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 (select count(*) from t2 where t2.a = t1.a) from t1;
  • 使用示例
    • 示例1：常见用法，命令示例如下：
      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        |
+------------+------------+------------+