10.查询和子查询

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

关于查询和子查询

创建简单查询

层次查询

集合操作

查询结果排序

JOIN操作

使用子查询

限制嵌套查询层数

DUAL表查询

10.1.关于查询和子查询

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

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

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

一个简单类型的查询为：

SELECT * FROM table1;

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

SELECT a, b + c FROM table1;

（假设b和c都是数字数据类型）。参阅选择列表获取更多细节。

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

SELECT 3 * 4;

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

SELECT random();

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

FROM (SELECT * FROM table1) AS alias_name 或者 FROM (SELECT * FROM table1)

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

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

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))AS names(first, last)

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

10.2.创建简单查询

出现在SELECT关键字之后的FROM子句之前的表达式列表成为选择列表。在选择列表中，您可以指定数据库从一个或多个表、视图、物化视图返回的结果集中的一个或多个列。列数及其数据类型和长度由选择列表的元素决定。 如果两个或多个表有一些共同的列名，则必须用表名限定列名。否则完全限定的列名是可选的。明确限定表和列名是一种比较谨慎的方式，但我们在使用过程中通常会忽略表名。 可以使用列的别名来标记选择列表中的表达式，这样结果集会以别名来显示列。在查询期间，别名有效的避免了列名冲突，别名可以在ORDER BY子句中使用，但是不能再其子查询中使用。

10.3.层次查询

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

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

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

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

10.3.1.层次查询示例

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

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

SELECT ename, empno, mgrFROM empSTART WITH mgr IS NULLCONNECT BY PRIOR empno = mgr;

运行结果如下所示：

ENAME | EMPNO | MGR----------+-------+------KING| 7839 |JONES | 7566 | 7839SCOTT | 7788 | 7566ADAMS | 7876 | 7788FORD| 7902 | 7566SMITH | 7369 | 7902ANDERSON | 8142 | 7902BLAKE | 7698 | 7839ALLEN | 7499 | 7698WARD| 7521 | 7698MARTIN | 7654 | 7698TURNER | 7844 | 7698JAMES | 7900 | 7698CLARK | 7782 | 7839MILLER | 7934 | 7782(15 行)

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

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgrFROM emp START WITH mgr IS NULLCONNECT BY PRIOR empno = mgr;

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

LEVEL | employee | EMPNO | MGR-------+----------------+-------+------1 | KING | 7839 |2 | JONES | 7566 | 78393 |SCOTT| 7788 | 75664 | ADAMS | 7876 | 77883 |FORD | 7902 | 75664 | SMITH | 7369 | 79024 | ANDERSON | 8142 | 79022 | BLAKE | 7698 | 78393 |ALLEN| 7499 | 76983 |WARD | 7521 | 76983 |MARTIN| 7654 | 76983 |TURNER| 7844 | 76983 |JAMES| 7900 | 76982 | CLARK | 7782 | 78393 |MILLER| 7934 | 7782(15 行)

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

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgrFROM emp START WITH mgr IS NULLCONNECT BY PRIOR empno = mgrORDER SIBLINGS BY ename ASC;

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

LEVEL | employee | EMPNO | MGR-------+----------------+-------+------1 | KING | 7839 |2 | BLAKE | 7698 | 78393 |ALLEN| 7499 | 76983 |JAMES| 7900 | 76983 |MARTIN| 7654 | 76983 |TURNER| 7844 | 76983 |WARD | 7521 | 76982 | CLARK | 7782 | 78393 |MILLER| 7934 | 77822 | JONES | 7566 | 78393 |FORD | 7902 | 75664 | ANDERSON | 8142 | 79024 | SMITH | 7369 | 79023 |SCOTT| 7788 | 75664 | ADAMS | 7876 | 7788(15 行)

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

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgrFROM emp WHERE mgr IN (7839, 7782, 7902, 7788)START WITH ename IN ('BLAKE','CLARK','JONES')CONNECT BY PRIOR empno = mgrORDER SIBLINGS BY ename ASC;

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

LEVEL | employee | EMPNO | MGR-------+--------------+-------+------1 | BLAKE | 7698 | 78391 | CLARK | 7782 | 78392 | MILLER| 7934 | 77821 | JONES | 7566 | 78393 |ANDERSON | 8142 | 79023 |SMITH | 7369 | 79023 |ADAMS | 7876 | 7788

(7 行)

10.4.集合操作

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

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

10.4.1.使用连接方式的原则

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

知识点描述

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

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

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

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

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

应用原则

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

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

举例

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

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

test=# \d studentTable "public.student"Column | Type | Collation | Nullable | Default--------+----------------------------+-----------+----------+---------sno | integer | | |sname | character varying(20 char) | | |ssex | character varying(5 char) | | |test=# \d scoreTable "public.score"Column | Type | Collation | Nullable | Default-------+---------+-----------+----------+---------sno | integer | | |cno | integer | | |degree | numeric | | |test=# explain select * from student, score where student.sno = score.sno and student.sno = 5;QUERY PLAN---------------------------------------------------------------------Nested Loop (cost=0.00..44.06 rows=24 width=126)-> Seq Scan on score (cost=0.00..24.88 rows=6 width=40)Filter: (sno = 5)-> Materialize (cost=0.00..18.89 rows=4 width=86)-> Seq Scan on student (cost=0.00..18.88 rows=4 width=86)Filter: (sno = 5)(6 rows)

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

test=# create index student_ind1 on student(sno);CREATE INDEXtest=# explain select * from student, score where student.sno = score.sno and student.sno = 5;QUERY PLAN-----------------------------------------------------------------------------------Nested Loop (cost=10000000000.13..10000000033.08 rows=6 width=126)-> Index Scan using student_ind1 on student (cost=0.13..8.15 rows=1 width=86)Index Cond: (sno = 5)-> Seq Scan on score (cost=10000000000.00..10000000024.88 rows=6 width=40)Filter: (sno = 5)(5 rows)

2.散列连接(Hash Join)

知识点描述

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

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

应用原则

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

举例

test=# explain select * from student, score where student.sno = score.sno;QUERY PLAN-----------------------------------------------------------------------Hash Join (cost=25.98..198.70 rows=4224 width=126)Hash Cond: (score.sno = student.sno)-> Seq Scan on score (cost=0.00..21.90 rows=1190 width=40)-> Hash (cost=17.10..17.10 rows=710 width=86)-> Seq Scan on student (cost=0.00..17.10 rows=710 width=86)(5 rows)

3.合并连接(Merge Join)

知识点描述

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

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

应用原则

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

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

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

举例

在下述的查询中

test=# explain select * from student, score where student.sno = score.sno;;QUERY PLAN----------------------------------------------------------------------Merge Join (cost=83.80..90.07 rows=30 width=49)Merge Cond: (student.sno = score.sno)-> Sort (cost=1.11..1.12 rows=5 width=9)Sort Key: student.sno-> Seq Scan on student (cost=0.00..1.05 rows=5 width=9)-> Sort (cost=82.69..85.66 rows=1190 width=40)Sort Key: score.sno-> Seq Scan on score (cost=0.00..21.90 rows=1190 width=40)(8 rows)

10.4.2.复杂查询的原则

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

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

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

3-5个表的处理方法

对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，

可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。

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

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

5个表以上的处理方法

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

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

行时间。

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

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

如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过

充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。

2.限制嵌套查询的层数

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

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

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

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

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

通过使用一些中间表，可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N <<M*N，则查询的复杂度被大大地降低。

下面显示了一个使用中间结果集来替代多表操作的例子。

test=# create table student_ng as select sno from score where degree <= 60;SELECT 2test=# select * from student_ng ;sno-----23(2 rows)test=# delete from student where sno in (select * from student_ng );DELETE 2test=# select * from student;sno | sname | ssex-----+-------+------1 | lq | f4 | zl | m5 | zs | m(3 rows)

上述sql等效于：

test=# delete from student where sno in (select sno from score where degree <= 60);DELETE 2test=# select * from student;sno | sname | ssex-----+-------+------1 | lq | f4 | zl | m5 | zs | m(3 rows)

4.使用一些改写复杂查询的技巧

转换连接类型

参见上文的改写查询语句部分

把OR转换为UNION ALL

区分不同的情况使用IN或EXISTS

对于主查询中包含较多条件而子查询条件较少的表使用EXISTS，对于主查询中包含较少条件而子查询条件较多的表使用IN。

使用合理的连接方式

在不同的情况下使用不同的连接方式：散列连接适用于一个较小的表和较大的表的连接，排序合并连接需要对小表进行排序操作，索引的嵌套循

环连接对于一般连接是有效的，但是需要在连接的关键字上有索引的存在。

应用开发人员应该根据不同的情况选取合适的连接方式。

1.使用并行查询

如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。

2.使用PL/SQL过程和临时表代替复杂查询。

对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以考虑使用PLSQL替代原来的查询。

10.5.查询结果排序

使用ORDER BY子句对查询选择的行进行排序。

可选的ORDERBY子句的形式如下：

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

ORDERBY子句导致结果行被按照指定的表达式排序。 如果两行按照最左边的表达式是相等的，则会根据下一个表达式比较它们， 依次类推。如果按照所有指定的表达式它们都是相等的，则它们被返回的 顺序取决于实现。

每一个``expression``可以是输出列（SELECT列表项）的名称或 者序号，它也可以是由输入列值构成的任意表达式。

序号指的是输出列的顺序（从左至右）位置。这种特性可以为不具有唯一 名称的列定义一个顺序。这不是绝对必要的，因为总是可以使用AS子句为输出列赋予一个名称。

也可以在ORDERBY子句中使用任意表达式，包括没 有出现在SELECT输出列表中的列。因此， 下面的语句是合法的：

SELECT name FROM distributors ORDER BY code;

这种特性的一个限制是一个应用在UNION、INTERSECT或EXCEPT子句结果上的ORDERBY只能指定输出列名称或序号，但不能指定表达式。

如果一个ORDERBY表达式是一个既匹配输出列名称又匹配 输入列名称的简单名称，ORDERBY将把它解读成输出列名 称。这与在同样情况下GROUPBY会做出的选择相反。这种 不一致是为了与 SQL 标准兼容。

可以为ORDERBY子句中的任何表达式之后增加关键词ASC（上升）DESC（下降）。如果没有指定，ASC被假定为默认值。或者，可以在USING子句中指定一个特定的排序操作符名称。一个排序操作符必须是某个 B-树操作符族的小于或者大于成员。ASC通常等价于USING<而DESC通常等价于USING>（但是一种用户定义数据类型的创建者可以 准确地定义默认排序顺序是什么，并且它可能会对应于其他名称的操作符）。

如果指定NULLSLAST，空值会排在非空值之后；如果指定NULLSFIRST，空值会排在非空值之前。如果都没有指定， 在指定或者隐含ASC时的默认行为是NULLSLAST， 而指定或者隐含DESC时的默认行为是NULLSFIRST（因此，默认行为是空值大于非空值）。 当指定USING时，默认的空值顺序取决于该操作符是否为 小于或者大于操作符。

注意顺序选项只应用到它们所跟随的表达式上。例如ORDERBYx,yDESC和ORDERBYxDESC,yDESC是不同的。

字符串数据会被根据引用到被排序列上的排序规则排序。根据需要可以通过在``expression``中包括一个COLLATE子句来覆盖，例如ORDERBYmycolumnCOLLATE"en_US"。更多信息请见排序规则表达式和排序规则。

10.6.JOIN操作

JOIN(联接)是一种查询，它组合了两个或多个表、视图、物化视图中的行。当查询的FROM子句中出现多个表时，数据库会执行联接。查询的选择列表可以从这些表中选择任何列。如果两个表中的任意两个列有相同的名字，那么在查询过程中，必须用表名限定对这些列的引用，以免产生歧义。 KingbaseES支持以下几种JOIN类型：

jointypes

[INNER]JOIN

LEFT[OUTER]JOIN

RIGHT[OUTER]JOIN

FULL[OUTER]JOIN

CROSSJOIN

对于INNER和OUTER连接类型，必须指定 一个连接条件，即NATURAL、ONjoin_condition或者USING(join_column[, ...]) 之一（只能有一种）。其含义见下文。对于CROSSJOIN，上述子句不能出现。

一个JOIN子句联合两个FROM项（ 为了方便我们称之为“表”，尽管实际上它们可以是任何类型 的FROM项）。如有必要可以使用圆括号确定嵌套的顺序。 在没有圆括号时，JOIN会从左至右嵌套。在任何情 况下，JOIN的联合比分隔FROM-列表 项的逗号更强。

CROSSJOIN和INNERJOIN会产生简单的笛卡尔积，也就是与在FROM的顶层列出两个 表得到的结果相同，但是要用连接条件（如果有）约束该结果。CROSSJOIN与INNERJOINON(TRUE)等效，也就是说条件不会移除任何行。这些连接类型只是一种 记号上的方便，因为没有什么是你用纯粹的FROM和WHERE能做而它们不能做的。

LEFTOUTERJOIN返回被限制过的笛卡尔积 中的所有行（即所有通过了其连接条件的组合行），外加左手表中 没有相应的通过了连接条件的右手行的每一行的拷贝。通过在右手 列中插入空值，这种左手行会被扩展为连接表的完整行。注意在决 定哪些行匹配时，只考虑JOIN子句自身的条件。之后 才应用外条件。

相反，RIGHTOUTERJOIN返回所有连接行，外加每 一个没有匹配上的右手行（在左端用空值扩展）。这只是为了记号 上的方便，因为你可以通过交换左右表把它转换成一个LEFTOUTERJOIN。

FULLOUTERJOIN返回所有连接行，外加每 一个没有匹配上的左手行（在右端用空值扩展），再外加每一个没有 匹配上的右手行（在左端用空值扩展）。

ONjoin_condition

``join_condition``是一个会得到boolean类型值的表达式（类似于一个WHERE子句），它说明一次连接中哪些行被认为 相匹配。

10.7.使用子查询

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

FROM (SELECT * FROM table1) AS alias_name 或者 FROM (SELECT * FROM table1)

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

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

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))AS names(first, last)

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

10.8.限制嵌套查询层数

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

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

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

10.9.DUAL表查询

KingbaseES兼容 Oracle 的DUAL伪表。该表可被所有的用户访问，但只有数据库管理员才能删除、增加、修改*DUAL*表 的内容。

例2-44：伪表DUAL的示例。

SELECT SYSDATE AS CURRENT_DATE_TIME FROM DUAL;