子查询_SELECT语句_SQL及函数_开发

子查询基本定义

普通查询操作的对象是目标表，但是查询的对象也可以是另一个SELECT语句，此查询为子查询（SUBQUERY）。子查询必须要有别名。

--普通查询。
select * from sale_detail;
--子查询。
select * from (select shop_name from sale_detail) a;

您也可以使用如下语句达到同样的效果，但它只能返回一条记录。

select (select a from table1) from table2;

在from子句中，子查询可以被当作一张表，与其它的表或子查询进行join操作。

create table shop as select * 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;

IN SUBQUERY/NOT IN SUBQUERY

IN SUBQUERY
in subquery与left semi join用法类似。
```
select * from mytable1 where id in (select id from mytable2);
--等效于以下语句。
select * from mytable1 a left semi join mytable2 b on a.id = b.id;
```
目前MaxCompute不仅支持in subquery，还支持Correlated条件。子查询中的where value = mytable1.value即是一个Correlated条件。MaxCompute 1.0版本不支持这种既引用了子查询中源表，又引用了外层查询源表的表达式。MaxCompute 2.0版本已支持这种用法，这样的过滤条件构成了semi join中on条件的一部分。
```
select * from mytable1 where id in (select id from mytable2 where value = mytable1.value);
```
NOT IN SUBQUERY
not in subquery与left anti join用法类似，但并不完全相同。示例如下。
```
select * from mytable1 where id not in (select id from mytable2);
--如果mytable2中的所有id都不为NULL，则等效于以下语句。
select * from mytable1 a left anti join mytable2 b on a.id = b.id;
```
如果mytable2中有任意一列为NULL，则not in表达式会为NULL，导致where条件不成立，无数据返回，这点与left anti join不同。
MaxCompute 1.0版本也支持(not) in subquery不作为join条件：例如出现在非where语句中，或者虽然在where语句中，但无法转换为join条件。MaxCompute 2.0版本仍然支持这种用法，但是因为此时无法转换为semi join，必须启动一个单独的作业运行Subquery，所以不支持Correlated条件，示例如下。
```
select * from mytable1 where id in (select id from mytable2) or value > 0;
```
因为where中包含了or，所以无法转换为semi join，会单独启动作业执行子查询。
在处理分区表的时候，也会有特殊处理，示例如下。
```
SELECT * from sales_detail where ds in (select dt from sales_date);
```
如果ds是分区列，则select dt from sales_date会单独启动作业执行子查询，而不会转化为semi join。执行后的结果会依次与ds比较，sales_detail中ds值不在返回结果中的分区将不会被读取，保证分区裁剪仍然有效。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

使用exists subquery时，当Subquery中有至少一行数据时，返回True，否则返回False。使用not exists subquery时，则相反。

目前只支持含有Correlatedwhere条件的子查询。exists subquery和not exists subquery实现的方式是转换为left semi join或者left anti join。

示例一

SELECT * from mytable1 where exists (select * from mytable2 where id = mytable1.id);
--等效于以下语句。
SELECT * from mytable1 a left semi join mytable2 b on a.id = b.id;

示例二

select * from mytable1 where not exists (select * from mytable2 where id = mytable1.id);
--等效于以下语句。
select * from mytable1 a left anti join mytable2 b on a.id = b.id;

SCALAR SUBQUERY

当Subquery的输出结果为单行单列时，可以做为标量使用。

select * from t1 where (select count(*) from t2 where t1.a = t2.a) > 1; 
--等效于以下语句。
select t1.* from t1 left semi join (select a, count(*) from t2 group by a having count(*) > 1) t2 on t1.a = t2.a;

select count(*) from t2 where t1.a = t2.a;的输出结果是一个Row Set，可以判断出这条语句的输出有且仅有一行一列。因此它可以作为标量，即可以参与标量运算（‘>’）。但在实现过程中，会尽可能地转成join来处理，例如上述示例中的等效语句。

说明可以作为标量的Subquery必须是在编译阶段就能够确认其返回结果只有一行一列的查询。如果一个Subquery只能在实际运行过程中才能判断出它只产生一行数据（而无法在编译过程中判断），则编译器会报错。

目前编译器能够接受的语句需满足两个特征：

子查询的select列表里面用了聚合函数，且不在表值函数的参数列表中。
子查询中包含聚合函数的这一层查询没有group by语句。

使用限制：

Scalar Subquery支持引用外层查询的列，当嵌套多层Scalar Subquery时，只支持引用直接外层的列。

select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; --允许的操作。
select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3; --不允许的操作，不能在子查询的子查询中引用外部查询的列。

Scalar Subquery只能在where语句中使用。

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中引用。

所有的满足一行一列输出值的子查询，都可以按照上述示例进行重写（如果查询的结果只有一行，在外面嵌套一层max或min操作，其结果不变）。