第四部分创建复杂的数据库查询

04-13Ctrl+D 收藏本站

关灯小中大繁直达底部

第13章在查询里结合表

第14章使用子查询定义未确定数据

第15章组合多个查询

第13章在查询里结合表

本章的重点包括：

简介表的结合

不同类型的结合

如何、何时使用结合

表结合的范例

不恰当表结合的影响

在查询中利用别名对表进行重命名

到目前为止，我们执行的数据库查询只是从一个表里获取数据。这一章将介绍如何在一个查询里结合多个表来获取数据。

13.1 从多个表获取数据

能够从多个表选择数据是SQL最强大的特性之一。如果没有这种能力，关系型数据库的整个概念就无法实现了。有时单表查询就可以得到有用的信息，但在现实世界里，最实用的查询是要从数据库里的多个表获取数据。

第 4 章已经介绍过，关系型数据库为达到简单和易于管理的目的，被分解为较小的、更易管理的表。正是由于表被分解为较小的表，它们通过共有字段（主键和外键）形成相互关联的表，并且能够通过这些字段结合在一起。

有人就会问了，既然最终还是要利用重新结合表来获取需要的数据，那么为什么还要对表进行规格化呢？在实际应用中，我们很少会从表里选择全部数据，因此最好是根据每个查询的需求进行挑选。虽然数据库的规格化会对性能造成一点影响，但从整体来说，编程和维护都更加容易了。需要记住的是，规格化的主要目的是减少冗余和提高数据完整性。数据库管理员的最终目标是确保数据安全。

13.2 结合的类型

结合是把两个或多个表组合在一起来获取数据。不同的实现具有多种结合表的方式，本章将介绍最常用的结合方式，它们是：

等值结合或内部结合；

非等值结合；

外部结合；

自结合。

13.2.1 结合条件的位置

从前面的课程可以知道，SELECT和FROM是SQL语句的必要子句；而在结合表时， WHERE子句是必要的。要结合的表列在FROM子句里，而结合是在WHERE子句里完成的。多个操作符可以用于结合表，比如=、<、>、<>、<=、>=、!=、BETWEEN、LIKE和NOT，其中最常用的是等于号。

13.2.2 等值结合

最常用也是最重要的结合就是等值结合，也被称为内部结合。等值结合利用通用字段结合两个表，而这个字段通常是每个表里的主键。

等值结合的语法如下所示：

具体范例如下：

这个SQL语句返回雇员标识和雇佣日期。雇员标识来自于表EMPLOYEE_TBL（虽然它存在于两个表里，但我们必须指定一个表），而雇佣日期来自于表 EMPLOYEE_PAY_TBL。由于雇员标识在两个表都存在，所以字段名称前面必须用表名加以修饰，从而让数据库服务程序明确到哪里获取数据。

注意：在SQL语句中使用缩排

注意到在上面这个范例SQL语句里使用了缩排方式来提高可读性。缩排方式不是必须的，但是推荐使用。

下面的范例从表EMPLOYEE_TBL和EMPLOYEE_PAY_TBL里获取数据，使用了等值结合。

SELECT子句里每个字段名称都以表名作为前缀，从而准确标识各个字段。在查询中，这被称为限定字段，它只有在字段存在于多个表时才有必要。在调试或修改SQL代码时，我们通常会对全部字段进行限定，从而提高一致性并减少问题。

另外，SQL里可以利用 INNER JOIN语法来提高可读性，如下所示：

在这种方式里，WHERE 子句里的结合操作符被去掉了，取而代之的是关键字 INNER JOIN。要被结合的表位于JOIN之后，而结合操作符位于关键字ON之后。下面的范例使用JOIN语法来返回与前例一样的结果：

上述两个范例的语法虽然不同，但它们都返回一样的结果。

13.2.3 使用表的别名

使用表的别名意味着在SQL语句里对表进行重命名，这是一种临时性的改变，表在数据库里的实际名称不会受到影响。稍后我们就会看到，让表具有别名是完成自结合的必要条件。给表起别名一般是为了减少键盘输入，从而得到更短、更易读的SQL语句。另外，输入较少就意味着更少的输入错误。而且，在对别名进行引用时，由于它一般比较短，而且更能准确描述数据，所以编程错误也会更少。给表起别名同时也意味着被选择字段必须用表的别名加以修饰。下面是使用表的别名的一些范例：

这个 SQL 语句里给表设置了别名。EMPLOYEE_TBL 被重命名为 E，EMPLOYEE_PAY_TBL被重命名为EP。选择什么名称作为别名没有限制，这里使用E是因为EMPLOYEE_TBL以E开头。虽然EMPLOYEE_PAY_TBL也以E开头，但不能再使用E了，所以用第一个字母E和第二个单词的第一个字母（P）组成EP作为这个表的别名。被选择的字段由相应表的别名加以修饰。注意WHERE子句里使用的SALARY字段也必须用表的别名加以修饰。

13.2.4 不等值结合

不等值结合根据同一个字段在两个表里值不相等来实现结合，其语法如下所示：

具体范例如下：

下面的SQL语句返回在两个表里没有相应记录的全部雇员的标识及雇佣日期，使用的就是不等值结合：

警告：不等值组合可能会产生多余数据

在使用不等值结合时，可能会得到很多无用的数据，其结果需要仔细检查。

每个表里只有 6 条记录，上面这个 SQL 语句为什么会返回 30 行记录呢？对于表EMPLOYEE_TBL里的每条记录，在EMPLOYEE_PAY_TBL里都有一条相应的记录。由于在表结合时测试的是不相等条件，所以第一个表里每条记录在与第二个表里的全部记录进行比较时，除其对应的记录，其他记录都满足条件。这意味着每条记录都与第二个表里5条不相关记录满足条件，因此6乘以5得到总共30条记录。

在前面小节中使用等值结合的例子里，第一个表里的每条记录都只与第二个表里的一行记录相匹配（其对应的记录），所以6乘以1得到总共6条记录。

13.2.5 外部结合

外部结合会返回一个表里的全部记录，即使对应的记录在第二个表里不存在。加号（+）用于在查询里表示外部结合，放在WHERE子句里表名的后面。具有加号的表是没有匹配记录的表。在很多实现里，外部结合被划分为左外部结合、右外部结合和全外部结合。

注意：结合的语法结构多变

关于外部结合的使用与语法请查看具体实现的文档。很多主流实现都使用“+”表示外部结合，但这并不是标准。实际上，相同数据库实现的不同版本，其相关规定也不尽相同。例如，Microsoft SQL Server 2000支持这种语法，但其2005及以上版本却不支持。所以，在使用这种语法结构时务必要小心。

外部结合的一般语法如下所示：

Oracle的语法是：

注意：外部结合的应用

外部结合只能用于JOIN条件的一侧，但可以在JOIN条件里对同一个表里的多个字段进行外部结合。

外部结合的概念将在下面的两个范例里加以解释。第一个范例选择了产品描述和订购数量，这两个值取自两个单独的表里。需要注意的是，并不是每件产品在表ORDERS_TBL里都有相应的记录。这里执行了一个普通的等值结合：

这里只得到了7种产品的17条记录，但产品共有9种。我们想显示全部的产品，不管它是否有订单。

下面的范例通过使用外部结合来达到我们的目的，这里使用的是Oracle语法：

这里也可以使用前面介绍的比较繁琐的语法结构，获得相同的结果。下面的范例就使用了这种繁琐结构，但清晰易懂。

这个查询返回了全部的产品，不论它是否有相应的订单。外部结合会包含表PRODUCT_TBL里的全部记录，不管它在表ORDER_TBL里是否有对应的记录。

13.2.6 自结合

自结合利用表别名在SQL语句对表进行重命名，像处理两个表一样把表结合到自身。其语法如下所示：

具体范例如下：

这个SQL语句返回表EMPLOYEE_TBL里所有姓相同的雇员的姓名。当需要的数据都位于同一个表里，而我们又必须对记录进行一些比较时，就可以使用自结合。

还可以像下面这样利用 INNER JOIN来得到同样的结果：

使用自结合的另一个常见范例是：假设有一个表保存了雇员标识号码、姓名、雇员主管的标识号码。我们想列出所有雇员及其主管的姓名，问题在于雇员主管的姓名并不是表里的一个字段：

在下面的语句里，我们在FROM子句里包含了表EMP两次，让表具有两个别名。这样我们就可以像使用两个不同的表一样进行操作。所有的主管也都是雇员，所以 JOIN 条件比较第一个表里的雇员标识号码与第二个表里的主管标识号码。第一个表就像是保存雇员信息的表，而第二个表就像是保存主管信息的表：

13.2.7 结合多个主键

大多数结合操作都会基于一个表里的主键和另一个表里的主键来合并数据。根据数据库的设计情况，有时我们需要结合多个主键来描述数据库里的数据。比如可能某个表的主键由多个字段组成，可能某个表的外键由多个字段组成，分别引用多个主键。

比如下面这个Oracle表：

PROD里的主键是由字段SERIAL_NUMBER和VENDOR_NUMBER组成的。也许两个产品在配送公司具有相同的序列号，但在每个商家的序列号都是唯一的。

ORD里的外键也是由字段SERIAL_NUMBER和VENDOR_NUMBER组成的。

在从两个表（PROD和ORD）里选择数据时，结合操作可能是这样的：

类似地，如果要使用 INNER JOIN，只需要在关键字ON之后列出多个结合操作：

13.3 需要考虑的事项

在使用结合之前需要考虑一些事情：基于什么字段进行结合、是否有公用字段进行结合、性能问题。查询里的结合越多，数据库需要完成的工作就越多，也就意味着需要越多的时间来获取数据。在从规格化的数据库里获取数据时，结合是不可避免的，但需要从逻辑角度来确定结合是正确执行的。不恰当的结合会导致严重的性能下降和不准确的查询结果。关于性能的问题将在第18章详细介绍。

13.3.1 使用基表

要结合什么？如果需要从两个表里获取数据，但它们又没有公用字段，我们就必须结合另一个表，这个表与前两个表都有公用字段，这个表就被称为基表。基表用于结合具有公用字段的一个或多个表，或是结合没有公用字段的多个表。下面是基表范例要用到的表：

假设我们要使用表CUSTOMERS_TBL和PRODUCTS_TBL，但它们之间没有公用字段。现在来看表ORDERS_TBL，它与表 CUSTOMER_TBL 可以通过 CUST_ID字段结合，与表PRODUCTS_TBL可以通过PROD_ID字段结合。相应的JOIN条件及结果如下所示：

注意：别名的使用

注意WHERE子句里的表别名和它们如何用于字段。

13.3.2 笛卡尔积

笛卡尔积是笛卡尔结合或“无结合”的结果。如果从两个或多个没有结合的表里获取数据，输出结果就是所有被选表里的全部记录。如果表的规模很大，其结果可能是几十万，甚至是数百万行数据。因此，在从两个或多个表里获取数据时，强烈建议使用WHERE子句。笛卡尔积通常也被称为交叉结合。

其语法如下所示：

下面是交叉结合（或称为可怕的笛卡尔积）的范例：

虽然没有执行 JOIN 操作，数据还是取自两个单独的表。由于我们没有指定第一个表里的记录如何与第二个表里的记录相结合，数据库服务程序把第一个表里每行记录都与第二个表里的全部记录相匹配。每个表都有6条记录，所以最终结果是6乘以6共计36条记录。

为了更好地理解笛卡尔积是如何得到的，再看下面这个范例：

警告：务必确保所有的表都结合完毕

在查询里结合多个表要特别小心。如果查询里的两个表没有结合，而且每个表都包含 1 000 行数据，那么笛卡尔积就会是 1 000 乘以 1 000，也就是1 000 000 行数据。在处理大量数据时，笛卡尔积有时会导致主机停止或崩溃。因此，对于DBA和系统管理员来说，密切监视长时间运行的查询是件很重要的工作。

13.4 小结

本章介绍了SQL最强大的功能之一：表的结合。想象一下，如果在查询里只能从一个表获取数据，那我们将受到多么大的局限。这里介绍了多种结合类型，它们分别具有自己的功能。内部结合可以根据相等或不相等的条件连接多个表里的数据。外部结合是相当强大的，即使在被结合的表没有匹配数据时，也能从中获取数据。自结合用于把表与自身相结合。对于交叉结合，也就是笛卡尔积，要特别小心，它是多个表没有进行任何结合的结果，经常会产生大量不必要的结果。因此，在从多个表里获取数据时，一定要根据相关联的字段（通常是主键）把表进行结合。如果没有恰当地对表进行结合，可能会产生不完整或不正确的输出结果。

13.5 问与答

问：在结合表时，它们的结合次序必须与它们在FROM子句里出现的次序一样吗？

答：不必，它们不必以同样的次序出现。但是，表在FROM里的次序和表被结合的次序可能会对性能有所影响。

问：在使用基表结合没有关联的表时，必须从基表里选择字段吗？

答：不必。使用基表结合不相关的表并不要求从基表里选择字段。

问：在结合表时可以基于多个字段吗？

答：可以。有些查询要求基于多个字段进行结合，才能描述表的记录之间的完整关系。

13.6 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

13.6.1 测验

1．如果不论相关表里是否存在匹配的记录，都要从表里返回记录，应该使用什么类型的结合？

2．JOIN条件位于SQL语句的什么位置？

3．使用什么类型的结合来判断相关表的记录之间的相等关系？

4．如果从两个不同的表获取数据，但它们没有结合，会产生什么结果？

5．使用如下的表：

下面使用外部结合的语法正确吗？

如果使用繁琐语法，上述查询语句会是什么样子？

13.6.2 练习

1．在数据库中输入以下代码，研究得到的结果（笛卡尔积）：

2．输入以下命令来结合表EMPLOYEE_TBL和EMPLOYEE_PAY_TBL：

3．改写练习2里的SQL查询语句，使用 INNER JOIN语法。

4 ．编写一个 SQL 语句，从表 EMPLOYEE_TBL 返回 EMP_ID、LAST_NAME 和FIRST_NAME字段，从表EMPLOYEE_PAY_TBL返回SALARY和BONUS字段。使用两种类型的 INNER JOIN技术。完成上述查询以后，再进一步计算出每个城市雇员的平均薪水是多少。

5．尝试自己编写几条使用结合操作的查询语句。

第14章使用子查询定义未确定数据

本章的重点包括：

什么是子查询

使用子查询的原因

常规数据库查询中使用子查询的范例

子查询与数据操作命令

嵌入式子查询

本章介绍子查询的相关内容。子查询可以帮助用户更便捷地完成复杂的查询操作。

14.1 什么是子查询

子查询也被称为嵌套查询，是位于另一个查询的 WHERE 子句里的查询，它返回的数据通常在主查询里作为一个条件，从而进一步限制数据库返回的数据。它可以用于 SELECT、INSERT、UPDATE和DELETE语句。

在某些情况下，子查询能够间接地基于一个或多个条件把多个表里的数据关联起来，从而代替结合操作。当查询里使用子查询时，子查询首先被执行，然后主查询再根据子查询返回的结果执行。子查询的结果用于在主查询的 WHERE 子句里处理表达式。子查询可以用于主查询的WHERE子句或HAVING子句。逻辑和关系操作符，比如=、>、<、<>、!=、IN、NOT IN、AND、OR，可以用于子查询里，也可以在WHERE或HAVING子句里对子查询进行操作。

注意：子查询规则

标准查询的规则同样也适用于子查询，结合操作、函数、转换和其他选项都可以在子查询里使用。

注意：使用缩进来提高可读性

注意范例中所使用的缩进。使用缩进基本上就是为了提高可读性。我们发现在查找SQL语句里的错误时，语句越整洁，就越容易阅读并发现语法中的错误。

子查询必须遵循以下规则。

子查询必须位于圆括号里。

除非主查询里有多个字段让子查询进行比较，否则子查询的SELECT子句里只能有一个字段。

子查询里不能使用ORDER BY子句。在子查询里，我们可以利用GROUP BY子句实现ORDER BY功能。

返回多条记录的子查询只能与多值操作符（比如IN）配合使用。

SELECT列表里不能引用任何BLOB、ARRAY、CLOB或NCLOB类型的值。

子查询不能直接被包围在函数里。

操作符BETWEEN不能用于子查询，但子查询内部可以使用它。子查询的基本语法如下所示：

下面的范例展示了操作符 BETWEEN 与子查询的关系。首先是在子查询里使用BETWEEN的正确范例。

不能够在子查询外使用BETWEEN。下面是错误地把BETWEEN用于子查询的范例：

14.1.1 子查询与SELECT语句

虽然子查询也可以用于数据操作语句，但它最主要还是用于SELECT语句里，获取数据给主查询使用。

基本语法如下所示：

下面是一个范例：

上面这条SQL语句返回小时工资低于雇员443679012的所有雇员的标识、姓、名和小时工资。这时，我们不必准确知道（或关心）这个特定雇员的小时工资是多少，只想知道比这个雇员工资低的人都是谁。

注意：使用子查询来查找不确定的值

在不能确定条件里的准确数值时，通常可以使用子查询来实现。雇员220984332的薪水是不确定的，但子查询可以帮我们完成这些跑腿的工作。

下面的查询选择某个雇员的小时工资，这个查询将作为后面范例里的一个子查询。

前面的查询在下面查询的WHERE子句里充当一个子查询：

子查询的结果是11（见前一个范例），所以上面这个WHERE子句的条件实际上是：

在执行这个查询时，我们不知道特定雇员的小时工资是多少，但主查询还是可以把每个雇员的小时工资与子查询的结果进行比较。

14.1.2 子查询与INSERT语句

子查询可以与数据操作语言（DML）配合使用。首先是INSERT语句，它将子查询返回的结果插入到另一个表。我们可以用字符函数、日期函数或数值函数对子查询里选择的数据进行调整。

注意：提交执行DML命令

在使用像 INSERT 语句这样的 DML 命令时，要记得使用 COMMIT 和ROLLBACK命令。

基本语法如下所示：

下面是在INSERT语句里使用子查询的范例：

这个 INSERT 语句把小时工资高于雇员 220984332 的所有雇员的 EMP_ID、LAST_NAME、FIRST_NAME和PAY_RATE插入到一个名为RICH_EMPLOYEES的表里。

14.1.3 子查询与UPDATE语句

子查询可以与UPDATE语句配合使用来更新一个表里的一个或多个字段，其基本语法如下所示：

下面的范例展示了如何在 UPDATE 语句里使用子查询。第一个查询返回居住在Indianapolis的全部雇员的标识，可以看到共有4人满足条件。

前面这个查询作为一个子查询用于下面这个UPDATE语句里。前面的结果说明了子查询会返回的雇员数量。下面是使用这个子查询的UPDATE语句：

不出所料，有4条记录被更新了。与前一小节的子查询范例不同的是，这个子查询返回多条记录，因此要使用操作符IN而不是等号（IN可以把一个表达式与列表里的多个值进行比较）。这里如果使用了等号，数据库会返回一个错误消息。

14.1.4 子查询与DELETE语句

子查询也可以与DELETE语句配合使用，其基本语法如下所示：

下面的范例从表EMPLOYEE_PAY_TBL里删除GRANDON GLASS的记录。这时我们不知道Brandon的标识号码，但可以利用一个子查询，根据FIRST_NAME和LAST_NAME字段的值从表EMPLOYEE_TBL里获取他的标识号码。

14.2 嵌套的子查询

子查询可以嵌入到另一个子查询里，就像子查询嵌套在普通查询里一样。在有子查询时，子查询先于主查询执行。类似地，在嵌套的子查询里，最内层的子查询先被执行，然后再依次执行外层的子查询，直到主查询。

注意：确认实现对子查询的限制规定

一个语句里能够嵌套的子查询的数量取决于具体的实现，请查看相应的文档。

嵌套子查询的基本语法如下所示：

下面的范例使用了两个子查询，一个嵌套在另一个之内。这个范例返回一些顾客的信息，这些顾客的订单的数量乘以单个订单的结果大于全部产品的价格总和。

警告：使用WHERE子句

不要忘记在UPDATE和DELETE语句里使用WHERE子句，否则目标表里的全部数据都会被更新或删除。可以先使用一个带有 WHERE 子句的SELECT语句进行查询，以便确认所要操作的数据准确无误。详情请见第5章的内容。

共有6条记录满足两个子查询的条件。

下面分别是两个子查询的结果，可以帮助我们更好地理解主查询是如何运行的。

当最内层子查询执行完成之后，主查询实际上就变成这样：

当外层子查询也执行完成之后，主查询就是这样了：

下面是最终的结果：

警告：多个子查询可能会产生问题

使用多个子查询可能会延长响应时间，还可能降低结果的准确性，因为代码里可能存在错误。

14.3 关联子查询

关联子查询在很多SQL实现里都存在，它的概念属于ANSI标准。关联子查询是依赖主查询里的信息的子查询。这意味着子查询里的表可以与主查询里的表相关联。

在下面这个范例里，子查询里结合的表CUSTOMER_TBL和ORDERS_TBL依赖于主查询里CUSTOMER_TBL的别名（C）。这个查询返回订购超过10件物品的顾客的姓名。

下面这个语句对子查询进行了一点修改，显示每个顾客订购的物品数量。

在这个范例里，GROUP BY子句是必需的，因为另一个字段被汇总函数SUM使用了。这样我们就得到了每个顾客订购的数量总和。在前一个子查询里，SUM函数用于获得整个查询的总和，就不是必须使用GROUP BY子句了。

14.4 子查询的效率

子查询会对执行效率产生影响。在应用子查询前，必须首先考虑好其所带来的影响。由于子查询会在主查询之前进行，所以子查询所花费的时间，会直接影响整个查询所需要的时间。看下面的范例。

注意：适当使用关联子查询

在进行关联子查询时，如果要在子查询中使用某个表，必须首先在主查询中引用这个表。

如果PRODUCTS_TBL表中包含有数以千计的产品信息，而ORDERS_TBL表中则保存了数以百万计的订单信息，想象一下这将意味着什么。对PRODUCTS_TBL表进行汇总，并与ORDERS_TBL进行关联，将在很大程度上影响操作的运行速度。所以，在需要使用子查询从数据库中获得相应信息的时候，务必考虑清楚子查询的执行效率。

14.5 小结

简单来说，子查询就是在另一个查询里执行的查询，用于进一步设置查询的条件。子查询可以用于SQL语句的WHERE子句或HAVING子句。它不仅可以在查询里使用，还可以用于DML（数据操作语言）语句，比如INSERT、UPDATE和DELETE，但这时要注意遵守DML的基本规则。

子查询的语法实质上与普通查询是一样的，只是有一些细微的限制。其中之一是不能使用ORDER BY子句，但可以使用GROUP BY子句，也能得到同样的效果。子查询可以向查询提供不必事先确定的条件，增强了SQL的功能灵活性。

14.6 问与答

问：在子查询的范例里有很多的缩进，这是语法要求的吗？

答：当然不是，缩进只是把语句划分为多个部分，让语句更易于阅读和理解。

问：一个查询里能够嵌套的子查询数量是否有限制？

答：像允许嵌套的子查询数量、查询里能够结合的表的数量等限制都是取决于具体实现的。有些实现可能没有限制，但子查询嵌套太多可能会明显降低语句的性能。大多数限制受到实际的硬件、CPU速度和可用系统内存的影响，当然还有其他一些考虑。

问：调试具有子查询，特别是嵌套子查询的语句似乎很容易让人迷惑，有什么好方法来调试具有子查询的语句吗？

答：调试具有子查询的语句的最好方法是分几个部分对查询进行求值。首先，运算最内层的子查询，然后逐步扩展到主查询（这与数据库执行查询的次序一样）。在单独运行了每个子查询之后，就可以把子查询的返回值代入到主查询，检查主查询的逻辑是否正确。子查询带来的错误经常是由对其使用的操作符造成的，比如=、IN、<、>等。

14.7 实践

14.7.1 测验

1．在用于SELECT语句时，子查询的功能是什么？

2．在子查询与UPDATE语句配合使用时，能够更新多个字段吗？

3．下面的语法正确吗？如果不正确，正确的语法应该是怎样？

a．

b．

c．

4．下面语句执行的结果是什么？

14.7.2 练习

1．编写SQL的子查询代码，与书中提供的进行比较。使用下面的表来完成练习。

2．使用子查询编写一个SQL语句来更新表CUSTOMER_TBL，找到ORD_NUM列中订单号码为 23E934的顾客，把顾客名称修改为DAVIDS MARKET。

3．使用子查询编写一个SQL语句，返回小时工资高于 JOHN DOE的全部雇员的姓名；JOHN DOE的雇员标识号码是 343559876。

4．使用子查询编写一个SQL语句，列出所有价格高于全部产品平均价格的产品。

第15章组合多个查询

本章的重点包括：

简介用于组合查询的操作符

何时对查询进行组合

GROUP BY子句与组合命令

ORDER BY与组合命令

如何获取准确的数据

本章介绍如何使用操作符UNION、UNION ALL、INTERSECT和EXCEPT把多个SQL查询组合为一个。同样的，这些操作符的实际使用方法请参考具体实现的文档。

15.1 单查询与组合查询

单查询是一个SELECT语句，而组合查询具有两个或多个SELECT语句。

组合查询由负责结合两个查询的操作符组成，下面的范例使用操作符UNION结合两个查询。

单个SQL语句的范例：

下面是同一个语句使用操作符UNION：

上面的语句返回所有雇员的工资信息，包含月薪和小时工资。

组合操作符用于组合和限制两个SELECT语句的结果，它们可以返回或清除重复的记录。组合操作符可以获取不同字段里的类似数据。

注意：UNION操作符如何起作用

第二个查询的输出结果里有两个列标题：EMP_ID 和 SALARY，每个人的工资都列在SALARY之下。在使用UNION操作符时，列标题是由SELECT语句里的字段名称或字段别名决定的。

组合查询可以把多个查询的结果组合为一个数据集，而且通常比使用复杂条件的单查询更容易编写。另外，组合查询对于数据检索也具有更强的灵活性。

15.2 组合查询操作符

不同数据库厂商提供的组合操作符略有不同。ANSI标准包括 UNION、UNION ALL、EXCEPT和INTERSECT，下面的小节将分别讨论这些操作符。

15.2.1 UNION

UNION 操作符可以组合两个或多个 SELECT 语句的结果，不包含重复的记录。换句话说，如果某行的输出存在于一个查询结果里，那么其他查询结果同一行的记录就不会再输出了。在使用UNION操作符时，每个SELECT语句里必须选择同样数量的字段、同样数量的字段表达式、同样的数据类型、同样的次序——但长度不必一样。

语法如下：

比如下面这个范例：

雇员ID在两个表里都存在，但在结果里只出现一次。

本章的范例由从两个表获取数据的简单SELECT语句开始：

现在利用UNION操作符组合上述两个查询，构造一个组合查询：

注意：创建表PRODUCTS_TBL

表PRODUCTS_TBL是在第3章里创建的。

第一个查询返回11条数据，第二个查询返回11条数据，但使用UNION操作符组合两个查询之后只返回了11条数据，这是因为UNION不会返回重复的数据。

下面的范例使用UNION操作符组合两个不相关的查询：

PROD_DESC和LAST_NAME的值被列在一起，列标题来自于第一个查询的字段名称。

15.2.2 UNION ALL

UNION ALL操作符可以组合两个SELECT语句的结果，并且包含重复的结果。其使用规则与UNION一样，它与UNION基本上是一样的，只是一个返回重复的结果，一个不返回。

基本语法如下所示：

下面这个SQL语句返回全部雇员的ID，并且包含重复的记录：

下面是使用UNION ALL操作符改写前一小节的组合查询：

因为UNION ALL操作符会返回重复的数据，所以这个查询返回了 22条记录（11+11）。

15.2.3 INTERSECT

INTERSECT 可以组合两个 SELECT 语句，但只返回第一个 SELECT 语句里与第二个SELECT语句里一样的记录。其使用规则与UNION操作符一样。目前MySQL5.0尚不支持INTERSECT，但SQL Server和Oracle全都提供支持。

基本语法如下所示：

范例如下：

前面这个SQL语句返回具有订单的顾客的ID。

下面的范例使用INTERSECT组合两个查询：

这里只返回了11条记录，因为两个查询之间只有11条记录是一样的。

15.2.4 EXCEPT

EXCEPT 操作符组合两个 SELECT 语句，返回第一个 SELECT 语句里有但第二个SELECT语句里没有的记录。同样的，它的使用规则与UNION操作符一样。目前MySQL并不支持EXCEPT。而在Oracle中，则使用MINUS操作符来实现同样的功能。

其语法如下所示：

观察下面SQL Server实现中的范例：

根据结果可以了解到，有3条记录存在于第一个查询的结果且不存在于第二个查询的结果。

下面的范例展示了以MINUS代替EXCEPT。

15.3 组合查询里使用ORDER BY

ORDER BY子句可以用于组合查询，但它只能用于对全部查询结果的排序，因此组合查询里虽然可能包含多个查询或SELECT语句，但只能有一个ORDER BY子句，而且它只能以别名或数字来引用字段。

其语法如下所示：

下面这个范例从EMPLOYEE_TBL表和EMPLOYEE_PAY_TBL表中返回雇员ID，但是不显示重复记录，返回结果根据EMP_ID排序：

注意：在ORDER BY子句中使用数字

ORDER BY子句里的字段是以数字1进行引用的，没有什么实际的字段名称。

组合查询的结果以每个查询的第一个字段进行排序。在排序之后，重复的记录就很明显了。

下面的范例在组合查询里使用ORDER BY子句。如果排序的字段在全部查询语句里都具有相同的名称，它的名称就可以用于ORDER BY子句里。

下面的查询在ORDER BY子句里以数据代表字段：

15.4 组合查询里使用GROUP BY

与ORDER BY不同的是，GROUP BY子句可以用于组合查询中的每一个 SELECT语句，也可以用于全部查询结果。另外，HAVING子句也可以用于组合查询里的每个SELECT语句。

其语法如下所示：

下面的查询利用一个字符串代表顾客记录、雇员记录和产品记录。每个单独的查询就是统计表里的记录总数。GROUP BY子句用于把整个结果根据第一个字段进行分组。

下面的查询与前一个一样，只是使用了ORDER BY子句：

它根据每个表里的第二列进行排序，因此输出结果根据总数从小到大排列。

注意：错误数据

不完整的查询返回结果被称为错误数据。

15.5 获取准确的数据

使用组合查询时要小心。在使用INTERSECT操作符时，如果第一个查询的SELECT语句有问题，就可能会得到不正确或不完整的数据。另外，在使用UNION和UNION ALL操作符时，要考虑是否需要返回重复的数据。那EXCEPT呢？我们是否需要不存在于第二个查询里的数据？很明显，组合查询里的错误组合操作符或单个查询的次序有误都会导致返回不正确的数据。

15.6 小结

本章介绍了组合查询。之前介绍的SQL语句都是构成单个查询，而组合查询可以让多个查询一起返回一个统一的数据集。这里讨论的组合操作符包括 UNION、UNION ALL、INTERSECT和EXCEPT（MINUS）。UNION返回两个查询的结果，不包含重复记录。UNION ALL会返回两个查询的全部结果，不管数据是否重复。INTERSECT返回两个查询结果中一样的记录。EXCEPT（MINUS）返回一个查询结果中不存在于另一个查询结果的记录。组合查询具有很大的灵活性，能够满足各种查询的要求。如果不使用组合查询，可能需要很复杂的查询语句才能达到同样的结果。

15.7 问与答

问：组合查询中的GROUP BY子句如何引用字段？

答：如果被引用的字段在所有查询里都是相同的名称，就可以直接使用字段名称进行引用；否则可以使用字段在SELECT语句里的次序号码进行引用。

问：在使用EXCEPT操作符时，如果颠倒SELECT语句的次序是否会改变输出结果呢？

答：是的。在使用EXCEPT或MINUS操作符时，单个查询的次序是很重要的。返回的数据是存在于第一个查询结果且不存在于第二个查询结果的记录，所以改变单个查询的次序肯定会改变结果。

问：组合查询里的单个查询的字段是否一定要具有同样的数据类型和长度？

答：不，只有数据类型要求是一样的，长度可以不同。

问：使用UNION操作符时，字段名称是由什么决定的？

答：在使用UNION操作符时，第一个查询决定了输出的字段名称。

15.8 实践

15.8.1 测验

在下面的练习里使用INTERSECT或EXCEPT操作符时，请参考本章介绍的语法。请注意，MySQL目前还不支持这两个操作符。