codecamp

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

要查询的目标表

如果子查询中的列与外层查询中的列具有相同列名,必须在外层查询中在重复列名前加上表名或使用别名。

当上层查询引用到子查询中相关列时,将执行子查询,上层查询可以是SELECTUPDATE 或 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)
+------------------------------------+
OceanBase 连接
OceanBase DDL
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

OceanBase 控制台指南

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 客户端版 ODC

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase SQL 参考(MySQL 模式)

OceanBase SQL 参考(Oracle 模式)

OceanBase 基本元素

OceanBase 数据库对象

OceanBase 函数

OceanBase 单行函数

OceanBase 返回数字的字符串函数

OceanBase 通用比较函数

OceanBase 编码解码函数

OceanBase SQL 调优指南

OceanBase 相关协议

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }