OceanBase 子查询
子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图，SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。
子查询可以分为相关子查询和非相关子查询。相关子查询指该子查询的执行依赖了外部查询的变量，这种子查询通常会执行多次。非相关子查询指该子查询的执行不依赖外部查询的变量，这种子查询一般只需要计算一次。对于非相关子查询与部分相关子查询，可以通过改写进行子查询消除，实现嵌套子查询的展开。
语法
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ]
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
| ( subquery ) [ order_by_clause ] [ row_limiting_clause ]参数
|
参数 |
说明 |
|---|---|
|
select_list |
查询列表 |
|
subquery |
子查询 |
|
hint |
注释 |
|
table_reference |
要查询的目标表 |
如果子查询中的列与外层查询中的列具有相同列名，必须在外层查询中在重复列名前加上表名或使用别名。
当上层查询引用到子查询中相关列时，将执行子查询，上层查询可以是SELECT，UPDATE 或 DELETE语句，各语句中使用子查询方式:
-
定义要插入到
INSERT或CREATE TABLE语句的目标表中的行集。 -
在
CREATE VIEW或CREATE MATERIALIZED VIEW语句中定义要包含在视图或物化视图中的行集。 -
在
UPDATE中定义要分配给现有行的一个或多个值。 -
在
WHERE子句、HAVING子句或START WITH中提供条件值。 -
定义包含查询操作的表。
嵌套子查询的展开（Unnesting of Nested Subqueries）
嵌套子查询展开是数据库的一种优化策略，它把一些子查询置于外层的父查询中，其实质是把某些子查询转化为等价的多表连接操作。这种策略带来的一个明显的好处是能够有效利用访问路径、连接方法和连接顺序，使查询的层次尽可能的减少。
以下情况数据库将进行嵌套子查询的展开:
-
不相关的
IN子查询。 IN和EXISTS中的相关子查询不包含聚合函数或GROUP BY子句。
可以通过 Hint UNNEST 控制是否展开嵌套子查询。
示例
以下语句创建了表 table_a和表 table_b，并向表中插入数据：
CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'福克斯');
INSERT INTO table_a VALUES(2,'警察');
INSERT INTO table_a VALUES(3,'的士');
INSERT INTO table_a VALUES(4,'林肯');
INSERT INTO table_a VALUES(5,'亚利桑那州');
INSERT INTO table_a VALUES(6,'华盛顿');
INSERT INTO table_a VALUES(7,'戴尔');
INSERT INTO table_a VALUES(10,'朗讯');
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'福克斯');
INSERT INTO table_b VALUES(2,'警察');
INSERT INTO table_b VALUES(3,'的士');
INSERT INTO table_b VALUES(6,'华盛顿');
INSERT INTO table_b VALUES(7,'戴尔');
INSERT INTO table_b VALUES(8,'微软');
INSERT INTO table_b VALUES(9,'苹果');
INSERT INTO table_b VALUES(11,'苏格兰威士忌');没有依赖关系的子查询，执行以下语句：
SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2);查询结果如下：
+------+-----------+
| PK | NAME |
+------+-----------+
| 1 | 福克斯 |
| 2 | 警察 |
| 3 | 的士 |
| 6 | 华盛顿 |
| 7 | 戴尔 |
+------+-----------+有依赖关系的子查询，子查询中用到了外层查询变量 T1.PK，执行以下语句：
SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2 WHERE T2.PK = T1.PK);查询结果如下：
+------+-----------+
| PK | NAME |
+------+-----------+
| 1 | 福克斯 |
| 2 | 警察 |
| 3 | 的士 |
| 6 | 华盛顿 |
| 7 | 戴尔 |
+------+-----------+有依赖关系的子查询被展开改写成连接，执行以下语句：
EXPLAIN SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.NAME FROM TABLE_B T2 WHERE T2.NAME = T1.NAME);查询结果如下：
+------------------------------------+
| Query Plan |
+------------------------------------+
=============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |HASH RIGHT SEMI JOIN| |8 |107 |
|1 | TABLE SCAN |T2 |8 |38 |
|2 | TABLE SCAN |T1 |8 |38 |
=============================================
Outputs & filters:
-------------------------------------
0 - output([T1.PK], [T1.NAME]), filter(nil),
equal_conds([T1.PK = T2.NAME], [T2.NAME = T1.NAME]), other_conds(nil)
1 - output([T2.NAME]), filter(nil),
access([T2.NAME]), partitions(p0)
2 - output([T1.NAME], [T1.PK]), filter(nil),
access([T1.NAME], [T1.PK]), partitions(p0)
+------------------------------------+
更多建议：