子查詢 - PolarDB

本文介紹PolarDB-X 1.0支援的子查詢類別及在PolarDB-X 1.0中使用子查詢的相關限制和注意事項。

使用限制

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

不支援在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 1.0中部分子查詢僅能以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 1.0目前支援如下類別的子查詢：

Comparisons Using Subqueries
Comparisons 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 Subqueries
Correlated 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. 資料準備：
    使用如下文法建立表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);
```
    使用如下查詢並得到查詢結果為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;
```
  2. 查詢需求：擷取分組資料SUM後的平均值。
    若直接使用如下SQL則會報錯，無法執行：
```
SELECT AVG(SUM(s1)) FROM t1 GROUP BY s1;
```
    此時可使用如下Derived Tables子查詢，並得到查詢結果為1.5000：
```
SELECT AVG(sum_s1)
  FROM (SELECT SUM(s1) AS sum_s1
        FROM t1 GROUP BY s1) AS t1;
```
    說明
    Derived Tables必須擁有一個別名（如樣本中的t1）。
    Derived Tables可以返回一個標量、列、行或表。
    Derived Tables不可以成為Correlated Subqueries，即不能包含子查詢外部表格的引用。