支持的子查询

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，即不能包含子查询外部表的引用。