当您需要在某个查询的执行结果基础上进一步执行查询操作时,可以通过子查询操作实现。本文为您介绍MaxCompute支持的子查询的定义与使用方法。
功能介绍
示例数据
--创建一张分区表sale_detail。
create table if not exists sale_detail
(
shop_name string,
customer_id string,
total_price double
)
partitioned by (sale_date string, region string);
--向源表增加分区。
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');
--向源表追加数据。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
基础子查询
select
语句,这种查询为子查询。在from
子句中,子查询可以被当作一张表,与其他表或子查询进行join
操作。join
详情请参见JOIN。
- 命令格式
- 格式1
select <select_expr> from (<select_statement>) <sq_alias_name>;
- 格式2
select (<select_statement>) from <table_name>;
- 格式1
- 参数说明
- select_expr:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - select_statement必填。子查询语句。如果子查询语句为格式2,子查询结果必须只有一行。格式请参见SELECT语法。
- sq_alias_name:必填。子查询的别名。
- table_name:必填。目标表名称。
- select_expr:必填。格式为
- 使用示例
- 示例1:使用格式1子查询语法。命令示例如下:
返回结果如下:select * from (select shop_name from sale_detail) a;
+------------+ | shop_name | +------------+ | s1 | | s2 | | s3 | | null | | s6 | | s7 | +------------+
- 示例2:使用格式2子查询语法。命令示例如下:
返回结果如下:select (select * from sale_detail where shop_name='s1') from sale_detail;
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | +------------+-------------+-------------+------------+------------+
- 示例3:使用格式1子查询语法,在
from
子句中,子查询可以被当做一张表,与其他的表或子查询进行join
操作。命令示例如下:
返回结果如下:--先新建一张表,再执行
join
操作。 create table shop as select shop_name,customer_id,total_price 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;+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | null | c5 | NULL | | s6 | c6 | 100.4 | | s7 | c7 | 100.5 | | s1 | c1 | 100.1 | | s2 | c2 | 100.2 | | s3 | c3 | 100.3 | +------------+-------------+-------------+
- 示例1:使用格式1子查询语法。命令示例如下:
IN SUBQUERY
in subquery
与SEMI JOIN(半连接)用法类似。
- 命令格式
- 格式1
select <select_expr1> from <table_name1> where <select_expr2> in (select <select_expr2> from <table_name2>); --等效于
left semi join
如下语句。 select <select_expr1> from <table_name1> <alias_name1> left semi join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;说明 如果select_expr2
为分区列,则select <select_expr2> from <table_name2>
会单独启动作业执行子查询,而不会转化为semi join
。执行后的结果会依次与select_expr2
比较,table_name1
中select_expr2
值不在返回结果中的分区将不会被读取,保证分区裁剪仍然有效。 - 格式2
MaxCompute不仅支持
in subquery
,还支持Correlated条件。子查询中的where <table_name2_colname> = <table_name1>.<colname>
即是一个Correlated条件。MaxCompute 1.0版本不支持这种既引用了子查询中源表,又引用了外层查询源表的表达式。MaxCompute 2.0已支持这种用法,这种过滤条件构成了semi join
中on
条件的一部分。select <select_expr1> from <table_name1> where <select_expr2> in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
说明 MaxCompute支持in subquery
不作为join
条件,例如出现在非where
语句中,或虽然在where
语句中,但无法转换为join
条件场景。此时无法转换为semi join
,必须启动一个单独的作业运行子查询,不支持Correlated条件。
- 格式1
- 参数说明
- select_expr1:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - table_name1、table_name2:必填。表的名称。
- select_expr2:必填。表示table_name1和table_name2互相映射的列名。
- col_name:必填。表的列名。
- select_expr1:必填。格式为
- 使用示例
- 示例1:使用格式1子查询语法。命令示例如下:
返回结果如下:select * from sale_detail where shop_name in (select shop_name from shop);
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | null | c5 | NULL | 2014 | shanghai | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
- 示例2:使用格式2子查询语法。命令示例如下:
返回结果如下:select * from shop where shop_name in (select shop_name from sale_detail where customer_id = shop.customer_id);
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s3 | c3 | 100.3 | | s7 | c7 | 100.5 | | s2 | c2 | 100.2 | | null | c5 | NULL | | s6 | c6 | 100.4 | | s1 | c1 | 100.1 | +------------+-------------+-------------+
- 示例3:
in subquery
不作为join
条件。命令示例如下:select * from sale_detail where shop_name in (select shop_name from shop) and total_price > 100.3;
因为
返回结果如下:where
中包含了and
,所以无法转换为semi join
,会单独启动作业执行子查询。+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +------------+-------------+-------------+------------+------------+
- 示例1:使用格式1子查询语法。命令示例如下:
NOT IN SUBQUERY
not in subquery
与SEMI JOIN(半连接)用法类似,但并不完全相同。如果查询目标表的指定列名中有任意一行为NULL,则not in
表达式值为NULL,导致where
条件不成立,无数据返回,这点与left anti join
不同。
- 命令格式
- 格式1
select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>); --等效于
left semi join
如下语句。 select <select_expr1> from <table_name1> <alias_name1> left anti join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;说明 如果select_expr2
为分区列,则select <select_expr2> from <table_name2>
会单独启动作业执行子查询,而不会转化为anti join
。执行后的结果会依次与select_expr2
比较,table_name1
中select_expr2
值不在返回结果中的分区将不会被读取,保证分区裁剪仍然有效。 - 格式2
MaxCompute不仅支持
not in subquery
,还支持Correlated条件。子查询中的where <table_name2_colname> = <table_name1>.<colname>
即是一个Correlated条件。MaxCompute 1.0版本不支持这种既引用了子查询中源表,又引用了外层查询源表的表达式。MaxCompute 2.0已支持这种用法,这种过滤条件构成了anti join
中on
条件的一部分。select <select_expr1> from <table_name1> where <select_expr2> in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
说明 MaxCompute支持not in subquery
不作为join
条件,例如出现在非where
语句中,或虽然在where
语句中,但无法转换为join
条件场景。此时无法转换为anti join
,必须启动一个单独的作业运行子查询,不支持Correlated条件。
- 格式1
- 参数说明
- select_expr1:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - table_name1、table_name2:必填。表的名称。
- select_expr2:必填。表示table_name1和table_name2互相映射的列名。
- col_name:必填。表的列名。
- select_expr1:必填。格式为
- 使用示例
- 示例1:使用格式1子查询语法。命令示例如下:
返回结果如下:--创建一张新表shop1并追加数据。 create table shop1 as select shop_name,customer_id,total_price from sale_detail; insert into shop1 values ('s8','c1',100.1); select * from shop2 where shop_name not in (select shop_name from sale_detail);
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+
- 示例2:使用格式2子查询语法。命令示例如下:
返回结果如下:select * from shop1 where shop_name not in (select shop_name from sale_detail where customer_id = shop1.customer_id);
+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+
- 示例3:
not in subquery
不作为join
条件。命令示例如下:select * from shop1 where shop_name not in (select shop_name from sale_detail) and total_price < 100.3;
因为
返回结果如下:where
中包含了and
,所以无法转换为anti join
,会单独启动作业执行子查询。+------------+-------------+-------------+ | shop_name | customer_id | total_price | +------------+-------------+-------------+ | s8 | c1 | 100.1 | +------------+-------------+-------------+
- 示例4:假设查询表中有任意一列为NULL,则无数据返回。命令示例如下:
返回结果如下:--创建一张新表sale并追加数据。 create table if not exists sale ( shop_name string, customer_id string, total_price double ) partitioned by (sale_date string, region string); alter table sale add partition (sale_date='2013', region='china'); insert into sale partition (sale_date='2013', region='china') values ('null','null',null),('s2','c2',100.2),('s3','c3',100.3); select * from sale where shop_name not in (select shop_name from sale_detail);
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ +------------+-------------+-------------+------------+------------+
- 示例1:使用格式1子查询语法。命令示例如下:
EXISTS SUBQUERY
使用exists subquery
时,当子查询中有至少一行数据时,返回True,否则返回False。
MaxCompute只支持含有Correlated条件的where
子查询。exists subquery
实现的方式是转换为left semi join
。
- 命令格式
select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
- 参数说明
- select_expr:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - table_name1、table_name2:必填。表的名称。
- col_name:必填。表的列名。
- select_expr:必填。格式为
- 使用示例
返回结果如下:select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id); --等效于以下语句。 select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | null | c5 | NULL | 2014 | shanghai | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
NOT EXISTS SUBQUERY
使用not exists subquery
时,当子查询中无数据时,返回True,否则返回False。
MaxCompute只支持含有Correlated条件的where
子查询。not exists subquery
实现的方式是转换为left anti join
。
- 命令格式
select <select_expr> from <table_name1> where not exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
- 参数说明
- select_expr:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - table_name1、table_name2:必填。表的名称。
- col_name:必填。表的列名。
- select_expr:必填。格式为
- 使用示例
返回结果如下:select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name); --等效于以下语句。 select * from sale_detail a left semi join shop b on a.shop_name = b.shop_name;
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ +------------+-------------+-------------+------------+------------+
SCALAR SUBQUERY
当子查询的输出结果为单行单列时,可以做为标量使用,即可以参与标量运算。
所有的满足一行一列输出值的子查询,都可以按照如下命令格式重写。如果查询的结果只有一行,在外面嵌套一层max
或min
操作,其结果不变。
- 命令格式
select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <标量运算符> <scalar_value>; --等效于以下语句。 select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <标量运算符> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;
说明select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>
的输出结果是一个Row Set,可以判断出这条语句的输出有且仅有一行一列。因此它可以作为标量。但在实现过程中,会尽可能地转成join
来处理。- 可以作为标量的子查询必须是在编译阶段就能够确认其返回结果只有一行一列的查询。如果一个子查询只能在实际运行过程中才能判断出它只产生一行数据(而无法在编译过程中判断),则编译器会报错。目前编译器能够接受的语句需满足两个特征:
- 子查询的
select
列表里面用了聚合函数,且不在表值函数的参数列表中。 - 子查询中包含聚合函数的这一层查询没有
group by
语句。
- 子查询的
- 参数说明
- select_expr:必填。格式为
col1_name, col2_name, 正则表达式,...
,表示待查询的普通列、分区列或正则表达式。 - table_name1、table_name2:必填。表的名称。
- col_name:必填。表的列名。
- 标量运算符:必填。例如大于(>)、小于(<)、等于(=)、大于等于(≥)或小于等于(≤)等。
- scalar_value:必填。标量值
- select_expr:必填。格式为
- 使用限制
scalar subquery
支持引用外层查询的列,当嵌套多层scalar subquery
时,只支持引用直接外层的列。--允许的操作。 select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; --不允许的操作,不能在子查询的select中引用外部查询的列。 select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3;
scalar subquery
只能在where
子句中使用。--不能在子查询的select中引用。 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 * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | null | c5 | NULL | 2014 | shanghai | | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | | s6 | c6 | 100.4 | 2014 | shanghai | | s7 | c7 | 100.5 | 2014 | shanghai | +------------+-------------+-------------+------------+------------+
在文档使用中是否遇到以下问题
更多建议
匿名提交