子查询基本定义

普通的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 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对于这种既引用了子查询中源表,又引用了外层查询源表的表达式时,会报错。现在MaxCompute已经支持这种用法,这样的过滤条件事实上构成了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会单独启动作业执行子查询,而不会转化为SEMIJOIN,执行后的结果会逐个与ds比较,sales_detail中ds值不在返回结果中的分区不会读取,保证分区裁剪仍然有效。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

EXISTS SUBQUERY时,当SUBQUERY中有至少一行数据时,返回true,否则false。NOT EXISTS时则相反。

目前只支持含有correlated WHERE条件的子查询。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必须是在编译阶段就能够确认返回结果只有一行一列的查询,如果一条语句,即使能够确定在实际运行过程中只会产生一行数据,但是编译过程中确定不了,编译器也是会报错。

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

  • 子查询的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操作,其结果不变)。