子查询

子查询是指嵌套在一个上层查询中的查询。上层的查询一般被称为父查询或外层查询。子查询的结果作为输入传递回"父查询"或"外部查询"。父查询将这个值结合到计算中，以便确定最后的输出。

SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。同时，子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 语句，FROM 语句，WHERE 语句等。

子查询

在数据库中，子查询可以分成有依赖关系的子查询和没有依赖关系的子查询。有依赖关系的子查询是指该子查询的执行依赖了外部查询的变量，所以这种子查询通常会被计算多次。没有依赖关系的子查询是指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。下面的示例分别展示了没有依赖关系的子查询和有依赖关系的子查询。

• 没有依赖关系的子查询：

  obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
  

• 有依赖关系的子查询，子查询中用到了外层查询变量 t1.b：

  obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM T2 WHERE t2.b = t1.b);
  

标量子查询

结果为单个元素的子查询称为标量子查询，查询结果可以作为标量操作数，并具有标量操作数的基本特性，例如数据类型、长度、是否可以为 NULL 等。例如以下查询返回的结果，就可以作为标量操作数：

obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT (SELECT c2 FROM t1);
+---------------------+
| (SELECT c2 FROM t1) |
+---------------------+
| OceanBase           |
+---------------------+
1 row in set

以上查询返回 VARCHAR 类型，长度为 20 的值 OceanBase，字符集与字符序服从租户的默认值。标量子查询所选值的可空性不会被复制，虽然 c2 列具有 NOT NULL 约束，但如果子查询结果为空，则结果仍会返回 NULL。

虽然返回单个元素，但标量子查询不能用于所有语句，如果语句仅允许字面量，则不能使用标量子查询来替代。

下面的例子可以帮助您更好地了解标量子查询。

该查询返回 2，是由于表 t2 中包含了与 t1 中同名的列 c1。

obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c1 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT (SELECT c1 FROM t2) FROM t1;
+---------------------+
| (SELECT s1 FROM t2) |
+---------------------+
|                   2 |
+---------------------+
1 row in set

此外，标量子查询可以是表达式的一部分，例如：

obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT HEX((SELECT c2 FROM t1 WHERE c1=1)) FROM t1;
+-------------------------------------+
| HEX((SELECT c2 FROM t1 WHERE c1=1)) |
+-------------------------------------+
| 4F6365616E42617365                  |
+-------------------------------------+
1 row in set

子查询的比较

子查询常见的语法如下所示：

operand operator {ANY | SOME | ALL} (subquery)
operand LIKE (subquery)
operand {IN | NOT IN | EXISTS | NOT EXISTS} (subquery)

其中，operand 是用于与子查询结果比较的操作数，operator 可以是以下操作符：

• 等于（= 或 <=>）

• 大于（>）

• 小于（<）

• 大于等于（>=）

• 小于等于（<=）

• 不等于（!= 或 <>）

• LIKE 用于字符或字符串比较。

例如以下子查询用于查询 t1 中 c1 列与 t2 中最大的 c2 列相同的值，该查询不可被改写为 t1 与 t2 的联接。

obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c2 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT * FROM t1 WHERE c1 = (SELECT MAX(c2) FROM t2);

显然，只有标量子查询的结果可用于直接比较，如果子查询返回一列值，则需要对集合进行处理。OceanBase 数据库提供了以下用于将集合化为标量值的方式：

• 聚合函数：将集合中的所有值聚合为一个值，将该值与操作数进行比较。

• ANY | SOME：将集合中所有值与操作数比较，如果存在满足比较条件的行，则返回 TRUE。SOME 是 ANY 的别名。

• ALL：将集合中的所有值与操作数比较，如果所有行都满足比较条件，则返回 TRUE。

• IN：如果操作数属于集合，则返回 TRUE。IN 等价于 = ANY。

• NOT IN：如果操作数不属于集合，则返回 TRUE。NOT IN 等价于 <> ALL。

• EXIST：如果子查询有返回行，则返回 TRUE。

• NOT EXIST：如果子查询无返回行，则返回 TRUE。

行子查询

标量子查询或列子查询返回标量或一列值，而行子查询则返回的是单个行，因此可以返回多个列值。

例如以下子查询仅返回单行：

obclient> CREATE TABLE t1(id INTEGER PRIMARY KEY,c1 INTEGER,c2 INTEGER);
obclient> CREATE TABLE t2(id INTEGER PRIMARY KEY,c3 INTEGER,c4 INTEGER);
obclient> SELECT * FROM t1
 WHERE ROW(c1,c2) = (SELECT c3, c4 FROM t2 WHERE id = 10);

如果 c3,c4 的值等于 t1 中的任意 c1,c2 值对，则返回所有满足条件的 c1,c2。如果不存在，则返回空集。此外，如果子查询中没有指定 WHERE 条件，则子查询可能会返回多行，此时查询会发生错误。行子查询只能返回单行。

表达式 ROW(c1,c2) 可以简写为 (c1,c2)，ROW() 称为行构造函数。行构造函数和子查询返回的行必须包含相同数量的值。行构造函数用于与返回两列或更多列的子查询进行比较，行构造函数不能与返回单列的列子查询同时使用，例如以下查询会返回语法错误：

obclient> SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ') = (SELECT c1 FROM t2)' at line 1

行构造函数在优化器中会被展开，例如以下两条 SQL 是等价的：

obclient> SELECT * FROM t1 WHERE (c1,c2) = (1,1);
obclient> SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;

关联子查询

如果子查询中引用率父查询中的列，则称为关联子查询。例如以下形式，子查询的 WHERE 条件中引用了 t1 的 c2 列：

obclient> SELECT * FROM t1 WHERE c1 IN (SELECT c3 FROM t2 WHERE t2.c4 = t1.c2);

对于此类语句，会从内向外对查询进行评估。例如以下写法中，x.c2 必须是 t2 中的列而不是 t1 中的列，这是由于对 SELECT c1 FROM t2 AS x 语句首先对 t2 进行了重命名，而 SELECT c1 FROM t1 是外部的查询。

obclient> SELECT c1 FROM t1 AS x
  WHERE x.c1 = (SELECT c1 FROM t2 AS x
    WHERE x.c1 = (SELECT c1 FROM t3
      WHERE x.c2 = t3.c1));

对于 HAVING 或 ORDER BY 子句中的子查询，OceanBase 数据库同样会在外部 SELECT 列表中查找列名。

关联子查询中的聚合函数若包含外部引用，则该函数只能包含外部引用，并且该函数不能包含于另一个函数或表达式中。

派生表

派生表是在查询 FROM 子句范围内生成表的表达式，例如 SELECT 语句的 FROM 子句中的查询作为派生表的情况：

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

作为 FROM 子句的派生表，[AS] table_name 不再是可选的别名，而是必须为子查询的结果集指定的名称。派生表的任何列都必须有单独的名称。

派生表常用于分步计算，例如以下查询计算了分组后每一分组的均值：

obclient> SELECT AVG(sum_c1)
FROM (SELECT SUM(c1) AS sum_cumn1
FROM t1 GROUP BY c1) AS t1;

派生表可以返回标量、列、行或表。

派生表具有以下限制：

• 派生表不能是关联子查询。

• 派生表不能包含对同一 SELECT 语句中的其他表的引用。

• 派生表不能包含外部引用。

横向派生表（Lateral Derived Table）

横向派生表是指在 FROM 子句中可以引用 FROM 列表中先前定义的表或派生表的特性。

横向派生表可以在使用子查询时提供更多的灵活性和效率，尤其是当子查询依赖于外部查询中的列时。从 OceanBase 数据库（MySQL 模式） V4.2.2 版本开始，LATERAL 关键字被引入以支持这一特性。

示例如下：

SELECT *
FROM A, LATERAL (SELECT * FROM B WHERE B.col1 = A.col1) AS derived_table;

更多有关横向派生表的使用信息，请参见 横向派生表。

子查询展开（Subquery Unnesting）

子查询展开是数据库的一种优化策略，它把一些子查询置于外层的父查询中，其实质是把某些子查询转化为等价的多表联接操作。这种策略带来的一个明显的好处就是，有些访问路径，联接方法和联接顺序可能被有效的利用，使得查询语句的层次尽可能的减少。下面展示了一个子查询展开的例子，即子查询被改写成了联接语句。

obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

• 没有依赖关系的子查询。

  obclient> EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
  | ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |131664|
  |1 | TABLE SCAN|t1  |100000   |48372 |
  |2 | TABLE SCAN|t2  |100000   |41911 |
  ======================================
  ...
  

• 有依赖关系的子查询被展开改写成联接。

  obclient> EXPLAIN SELECT * FROM t1 WHERE T1.A IN (SELECT T2.B FROM T2 WHERE T2.C = T1.C);
  | ===========================================
  |ID|OPERATOR        |NAME |EST. ROWS|COST  |
  --------------------------------------------
  |0 |MERGE JOIN      |     |9703     |215436|
  |1 | TABLE SCAN     |t1   |100000   |64066 |
  |2 | SORT           |     |10001    |129621|
  |3 |  SUBPLAN SCAN  |VIEW1|10001    |111242|
  |4 |   HASH DISTINCT|     |10001    |109862|
  |5 |    TABLE SCAN  |t2   |100000   |64066 |
  ============================================
  ...