子查询

普通的select是从几张表中读数据，例如select column_1, column_2 … from table_name，但是查询的对象也可以是另一个select操作。

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与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条件。

select * from mytable1 where id in (select id from mytable2 where value = mytable1.value);

子查询中的where value = mytable1.value即是一个Correlated条件。MaxCompute 1.0版本不支持这种既引用了子查询中源表，又引用了外层查询源表的表达式。MaxCompute 2.0版本已支持这种用法，这样的过滤条件构成了semi join中on条件的一部分。

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值不在返回结果中的分区将不会被读取，保证分区裁剪仍然有效。

示例一

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;

当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来处理，例如上述示例中的等效语句。

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

• 子查询的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中引用。