本文为您介绍MaxCompute支持的子查询操作。

子查询基本定义

普通的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/NOT IN SUBQUERY

in subqueryleft 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 joinon条件的一部分。
not in subqueryleft 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_detailds值不在返回结果中的分区将不会被读取,保证分区裁剪仍然有效。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

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

目前只支持含有Correlated where条件的子查询。exists subquerynot 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中引用。

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