10. 查询和子查询

本章描述SQL查询以及子查询。包含以下章节：

关于查询和子查询

创建简单查询

层次查询

集合操作

查询结果排序

JOIN操作

使用子查询

限制嵌套查询层数

DUAL表查询

PIVOT和UNPIVOT行列转换

公共表达式

从数据库中检索数据的过程或命令叫做查询。在 SQL 里 SELECT 命令用于指定查询。 SELECT 命令的一般语法是

下面几个章节将详细介绍如何选择列表、表表达式和排序声明。

一个简单类型的查询为：

假设有一个表叫做 table1 ，这条命令将 table1 中检索所有行和所有用户定义的列（检索的方法取决于客户端应用。例如，ksql程序将在屏幕上显示一个 ASCII 形式的表格， 而客户端库将提供函数来从检索结果中抽取单个值）。选择列表声明 * 意味着所有表表达式提供的列。一个选择列表也可以选择可用列的一个子集或者在使用它们之前对列进行计算。例如，如果 table1 有叫做 a 、 b 和 c 的列（可能 还有其他），那么可以用下面的查询：

（假设 b 和 c 都是数字数据类型）。

FROM table1 是一种非常简单的表表达式：它只读取了一个表。通常，表表达式可以是基本表、连接和子查询组成的复杂结构。但也可以省略 整个表表达式而把 SELECT 命令当做一个计算器：

如果选择列表里的表达式返回变化的结果，那么这就更有用了。例如，你可以用这种方法调用函数：

子查询指定了一个派生表，它必须被包围在圆括弧里，并且可以被赋予一个表别名，若未指定别名则系统会自动生成一个别名。例如：

这个例子等效于 FROM table1 AS alias_name 。更有趣的情况是在子查询里面有分组或聚集的时候， 子查询不能被简化为一个简单的连接。

一个子查询也可以是一个 VALUES 列表：

再次的，这里可以使用一个表别名，若未指定则系统自动生成一个别名。为 VALUES 列表中的列分配别名是可选的，但是指定别名这样做是一个好习惯 。

出现在SELECT关键字之后的FROM子句之前的表达式列表成为选择列表。在选择列表中，您可以指定数据库从一个或多个表、视图、物化视图返回的结果集中的一个或多个列。列数及其数据类型和长度由选择列表的元素决定。

如果两个或多个表有一些共同的列名，则必须用表名限定列名。否则完全限定的列名是可选的。明确限定表和列名是一种比较谨慎的方式，但我们在使用过程中通常会忽略表名。

可以使用列的别名来标记选择列表中的表达式，这样结果集会以别名来显示列。在查询期间，别名有效的避免了列名冲突，别名可以在ORDER BY子句中使用，但是不能再其子查询中使用。

层次查询是一种特定类型的查询，用于在基于父子关系的数据中以层次顺序返回结果集中的记录。通常，层次是用一个反转顺序的树结构表示。树由相互连接的节点组成。每个节点可能会连接0个或多个子节点。在层次查询中，结果集的记录为一或多棵树中的节点。

KingbaseES和Oracle均支持层次查询，且二者兼容。

下表列出了层次查询中的所有操作符：

操作符

功能

PRIOR

在当前元组的父元组上求 值，如果当前元组是根（Root）元组，则求值为NULL。

CONNECT_BY_ROOT

在当前层次查询的根（Root）元组上求值。

伪列与普通列十分相似，但其值并不是和表数据存储在一起的。层次查询具有三个伪列，具体如下表所示。

伪列

描述

LEVEL 伪列

描述当前元组的所在的层

CONNECT_BY_ISLEAF 伪列

描述 当前节点是否为叶节点。若是为1，否则为0。

CONNECT_BY_ISCYCLE 伪列

如果 一个元组的CONNECT_BY_ISCYCLE值是1，则代表 这个元组有子元组，并且这个子元组又是它的 祖先元组，即数据库中的数据成环；否则为0。

例10-1： 层次查询应用的示例。在该例中，表 emp 表数据的树状结构图如下所示：

执行以下附带层次查询的 SELECT 命令：

运行结果如下所示：

例10-2： 层次查询伪列的示例。通过对伪列 LEVEL 值执行 LPAD 操作，雇员名称被缩进，这样能够进一步强调每条记录在层次中的 深度。

这个查询的检索结果如下所示：

例10-3： 层次查询对同层节点排序的示例。层次查询语句如下所示：

这个查询的检索结果如下所示：

例10-4 ：层次查询的 WHERE 过滤的示例，查询语句如下所示：

这个查询的检索结果如下，在下面的结果中不满足 WHERE 子句的记录不会在输出中出现。

KingbaseES数据库的集合运算包括: UNION， UNION ALL，INTERSECT和MINUS操作。

一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。

1.嵌套循环连接（NESTED LOOP）

知识点描述

嵌套循环连接操作关系到两个表，一个内部表和一个外部表。KingbaseES比较内部数据集的每一条记录和外部数据集的每一条记录，并 返回满足条件的记录。

嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格的限制。

当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有索引的嵌套循环连接(NESTED LOOP)，通常有索引的嵌套循环连接在产 生较小的数据量的情况下可以较快的执行。

在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确的驱动数据集，通常情况下使用包含数据量较小的表作为驱动表。

一般如果使用基于成本的优化器，系统会自动选择驱动表，如果是使用基于规则的优化器，则后表作为驱动表。

应用原则

一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。

在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主键或唯一键，同时希望在另外一张表在相同的列上有索引。

举例

下面给出了两种连接的例子：

对于不存在索引的表students和score执行以下操作：

为NESTED LOOP JOIN，因为数据库需要对表Student中所有的行进行处理。如果此时student表的sno列上有索引则上述查询的方式转变为有索引的嵌套循环连接(Nested Loop)。

2.散列连接(Hash Join)

知识点描述

散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接，连接过程中，对大表的访问使用了散列访问。散列连接可以提高等连接的速度。

如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够很好地运行简单的散列连接.

应用原则

一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据全部被读入内存，其处理的速度较快。

举例

3.合并连接(Merge Join)

知识点描述

合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记录集分别排序的操作。合并过程将来自一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。

合并连接有几种不同的排序方法：外部合并连接，反合并连接和半合并连接。这些不同的排列方法使得合并步骤服务于不同的目的，可以是找到记录数据集的交集，也可以是找到满足SQL语句中WHERE子句条件的那些记录。

应用原则

一般的合并连接是在散列连接不能达到应用的要求或KingbaseES优化器认为排序合并连接效率更高的情况下使用。在下述的条件下排序 合并连接被使用：

1)数据表之间的连接不是等值连接而是其它连接

2)数据库使用的优化模式是基于RBO而不是CBO

举例

1. 限制表连接操作所涉及的表的个数

对于数据库的连接操作操作，可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。

一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此需要尽可能的限制参与连接的表的数量。

1). 3-5个表的处理方法

对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。

RBO模式下，在SQL语句中FROM子句后面的表就是要进行连接操作的数据表，KingbaseES按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。

CBO模式下，则不需要考虑表放置的位置。

2). 5个表以上的处理方法

对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。

对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。

同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。

3). 对表连接操作涉及的表数目不应多于8个表

如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分地测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。

2. 限制嵌套查询的层数

应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。

因此，降低查询的嵌套层数有助于提高查询语句的效率。

对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现，不应多于5层。

3. 灵活应用中间表或临时表

在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的一个重要的方法。

通过使用一些中间表，可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N