開發指南—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);      

• • 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. 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. 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. 1. 查詢需求：擷取分組資料SUM後的平均值。若直接使用如下SQL則會報錯，無法執行：

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

1. 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. 1. 說明

• • • • Derived Tables必須擁有一個别名（如示例中的

        t1

        ）。
      • Derived Tables可以傳回一個标量、列、行或表。
      • Derived Tables不可以成為Correlated Subqueries，即不能包含子查詢外部表的引用。