子查询基本定义

普通的Select是从几张表中读数据,如 select column_1, column_2 … from table_name,但查询的对象也可以是另外一个Select操作,如下所示:
select * from (select shop_name from sale_detail) a;
说明
子查询必须要有别名。
在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;