子查询（SUBQUERY） - MaxCompute

功能介绍

子查询指在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式。MaxCompute支持的子查询包含如下几种：

基础子查询
IN SUBQUERY
NOT IN SUBQUERY
EXISTS SUBQUERY
NOT EXISTS SUBQUERY
SCALAR SUBQUERY

示例数据

为便于理解，本文为您提供源数据，基于源数据提供相关示例。创建表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条件。
参数说明
- 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   |
+------------+-------------+-------------+------------+------------+

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条件。
参数说明
- 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     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

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语句。
参数说明
- 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;

使用示例

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       |
+------------+-------------+-------------+