开发指南—DQL语句—子查询-阿里云开发者社区

本文介绍PolarDB-X支持的子查询类别及在PolarDB-X中使用子查询的相关限制和注意事项。

使用限制

相比原生MySQL，PolarDB-X在子查询使用上增加了如下限制：

不支持在HAVING子句中使用子查询，示例如下：

SELECT name, AVG( quantity )
FROM tb1
GROUP BY name
HAVING AVG( quantity ) > 2* (
   SELECT AVG( quantity )
   FROM tb2
 );

不支持在JOIN ON子句中使用子查询，示例如下：

SELECT * FROM tb1 p JOIN tb2 s on (p.id=s.id and p.quantity>All(select quantity from tb3))

等号操作行符的标量子查询（The Subquery as Scalar Operand）不支持ROW语法。示例如下：

select * from tb1 where row(id, name) = (select id, name from tb2)

不支持在UPDATE SET子句中使用子查询，示例如下：

UPDATE t1 SET c1 = (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) LIMIT 10

注意事项

PolarDB-X中部分子查询仅能以APPLY的方式执行，查询效率低下。在实际使用中请尽量避免如下例子中的低效SQL：

WHERE条件中OR与子查询共存时，执行效率会依外表数据情况大幅降低。示例如下：

高效：select * from tb1 where id in (select id from tb2)

高效：select * from tb1 where id in (select id from tb2) and id>3
低效：select from tb1 where id in (select id from tb2) or id>3

关联子查询（Correlated Subqueries）的关联项中带函数或非等号运算符。示例如下：

高效：select  from tb1 a where id in
      (select id from tb2 b where a.name=b.name)
低效：select * from tb1 a where id in
      (select id from tb2 b where UPPER(a.name)=b.name)
低效：select * from tb1 a where id in
      (select id from tb2 b where a.decimal_test=abs(b.decimal_test))
低效：select * from tb1 a where id in
      (select id from tb2 b where a.name!=b.name)
低效：select * from tb1 a where id in
      (select id from tb2 b where a.name>=b.name)

关联子查询（Correlated Subqueries）关联项与其它条件的逻辑运算符为OR。示例如下：

高效：select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  and b.date_test<'2015-12-02')
低效：select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  or b.date_test<'2015-12-02')
低效：select * from tb1 a where id in
      (select id from tb2 b where a.name=b.name
                                  or b.date_test=a.date_test)

标量子查询（The Subquery as Scalar Operand）带关联项。示例如下：

高效：select * from tb1 a where id >
        (select id from tb2 b where b.date_test<'2015-12-02')
低效：select * from tb1 a where id >
        (select id from tb2 b where a.name=b.name 
                                    and b.date_test<'2015-12-02')

跨关联层子查询。示例如下：

- SQL多层关联，每层子查询关联项仅与直接上层关联，此类高效。

高效：select * from tb1 a where id in(select id from tb2 b 
        where a.name=b.name and 
        exists (select name from tb3 c where b.address=c.address))

- SQL多层关联，但表c的子查询关联项中与表a的列进行了关联，此类低效。

低效：select * from tb1 a where id in(select id from tb2 b 
        where a.name=b.name and 
        exists (select name from tb3 c where a.address=c.address))

说明上述示例中，表a和表b、表b和表c为直接层级关联，表a和表c间为跨层关联。
子查询中包含GROUP BY，请确保GROUP BY的分组列包含关联项。示例如下：

- SQL子查询中包含聚合函数和关联项，关联项b.pk包含于分组列pk之中，此类高效。

高效：select * from tb1 a where exists 
    (select pk from tb2 b 
                where a.pk=b.pk and  b.date_test='2003-04-05' 
                group by pk);

- SQL子查询中包含聚合函数和关联项，关联项b.date_test不包含于分组列pk之中，此类低效。

低效：select * from tb1 a where exists 
    (select pk from tb2 b 
                where a.date_test=b.date_test and b.date_test='2003-04-05' 
                group by pk);

支持的子查询

PolarDB-X目前支持如下类别的子查询：

Comparisons Using SubqueriesComparisons Using Subqueries指带有比较运算符的子查询，这类子查询最为常见。

- 语法

non_subquery_operand comparison_operator (subquery)
comparison_operator： =  >  <  >=  <=  <>  !=  <=> like

- 示例

select * from tb1 WHERE 'a' = (SELECT column1 FROM t1)

- 说明目前仅支持子查询在比较运算符的右边。

Subqueries with ANY、ALL、IN/NOT IN、EXISTS/NOT EXISTS

- 语法

operand comparison_operator ANY (subquery)
operand comparison_operator ALL (subquery)
operand IN (subquery)
operand NOT IN (subquery)
operand EXISTS (subquery)
operand NOT EXISTS (subquery)
comparison_operator:=  >  <  >=  <=  <>  !=

- 示例

- - ANY：如果子查询返回的任意一行满足ANY前的表达式，返回TRUE，否则返回FALSE。
  - ALL：如果子查询返回所有行都满足ALL前的表达式，返回TRUE，否则返回FALSE。
  - IN：在子查询前使用时，IN等价于=ANY。示例如下：

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

- - NOT IN：NOT IN在子查询前使用时，等价于<>ALL。示例如下：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

- - EXISTS：如果子查询返回任意行，EXISTS子查询结果为TRUE；如果子查询返回空值，EXISTS子查询结果为FALSE。示例如下：

SELECT column1 FROM t1 WHERE EXISTS (SELECT  FROM t2);

- - 说明如果EXISTS子查询中包含任意行，即使只包含NULL的行值，WHERE条件也会返回TRUE。
  - NOT EXISTS：如果子查询返回任意行，NOT EXISTS子查询结果为FALSE；如果子查询返回空值，NOT EXISTS子查询结果为TRUE。

Row Subqueries

- Row Subqueries支持如下比较运算符：

comparison_operator：=  >  <  >=  <=  <>  !=  <=>

- 示例

SELECT  FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

- 以上两个SQL是等价的，只有同时满足以下条件时，t1表的数据行才会返回：

- - 子查询（SELECT col3, col4 FROM t2 WHERE id=10 ）仅返回一行记录，返回多行会报错。
  - 子查询返回的col3，col4结果与主表中col1，col2的值需一一对应。

Correlated SubqueriesCorrelated Subqueries指子查询中包含对外层查询表的引用。示例如下：

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

示例子查询SQL中并没有包含表t1及其列名column2，此时会向上一层寻找表t1的引用。
Derived Tables（Subqueries in the FROM Clause）Derived Tables指在FROM子句中的子查询。

- 语法

SELECT ... FROM (subquery) [AS] tbl_name ...

- 示例

1. 1. 数据准备：使用如下语法创建表t1：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);

1. 1. 使用如下查询并得到查询结果为2, '2', 4.0。

SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

1. 1. 查询需求：获取分组数据SUM后的平均值。若直接使用如下SQL则会报错，无法执行：

SELECT AVG(SUM(s1)) FROM t1 GROUP BY s1;

1. 1. 此时可使用如下Derived Tables子查询，并得到查询结果为1.5000：

SELECT AVG(sum_s1)
  FROM (SELECT SUM(s1) AS sum_s1
        FROM t1 GROUP BY s1) AS t1;

1. 1. 说明

- - - Derived Tables必须拥有一个别名（如示例中的t1）。
    - Derived Tables可以返回一个标量、列、行或表。
    - Derived Tables不可以成为Correlated Subqueries，即不能包含子查询外部表的引用。

开发指南—DQL语句—子查询

使用限制

注意事项

支持的子查询

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

开发指南—DQL语句—子查询

使用限制

注意事项

支持的子查询

热门文章

最新文章

相关电子书