子查询、集合查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询，换句话说，子查询几乎可以出现在一条 SQL 语句的任意位置上，且必须用一对小括号来包裹子查询的定义。本系列博客的第 12 篇的 4.2 节已经演示了常见的三种子查询写法。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

本人查阅了大量资料，似乎根本就找不到一种“正确的子查询分类方法”。常见的分类名有：单行子查询、多行子查询、多列子查询、相关子查询、标量子查询、内联视图等，前三个顾名思义，后三个分别简要说明如下：

相关子查询：子查询引用了外部查询中包含的一列或多列，也就是说内部查询得依靠外部查询获得值，这样的子查询被称为相关子查询，也称为重复子查询。

标量子查询：只返回单一值的子查询称为标量子查询。

内联视图：一般是指写在 FROM 子句后面的子查询，它本质上是视图，也称内嵌视图，与标准视图的区别主要是无需事先编写创建视图的语句，便于执行查询。

下面我再补充几个 WHERE 子句带子查询的示例：

WITH 子查询的作用类似于内联视图，包括内联视图在内的其它子查询都只能够引用一次；而 WITH 子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。Oracle 从 11g R2 开始支持递归的 WITH，即允许在 WITH 子查询的定义中对自身引用，而其它数据库，如 SQL Server、PostgreSQL、DB2 等都先于 Oracle 支持这一特性。语法示例：

案例一：查询年龄在25岁及以下，固定工资在5000及以上的员工基本信息。示例：

结果：

案例二：统计个个部门的人数、总工资、平均工资、最高工资、最低工资。示例：

案例二不使用 WITH 的示例：

在数学中可以对集合做交并差运算，在 SQL 中同样可以对查询结果集做交并差操作。这三种 SQL 集合查询对应的操作符关键字分别是 INTERSECT、UNION/UNION ALL、MINUS。

最常见的 SQL 集合查询就是并集查询，并集查询操作符有 UNION 和 UNION ALL 两个，用法完全一致。示例：

从 UNION 和 UNION ALL 的查询结果来看，它们的区别之一就是：UNION 会去除重复行，而 UNION ALL 会保留重复行，如果把上例中的 ALL 关键字去掉，第 1、3 行中就会有 1 行被当作重复行去除；区别之二就是：结果集默认的排序不同，UNION ALL 只是按关联次序来组织数据，不再排序，而 UNION 将会按默认规则对整个数据集进行排序。另外，UNION ALL 的运算效率比 UNION 要高，故此，UNION ALL 也相对常用一些。

可通过 MINUS 操作符求两个结果集的差集。差集结果集不包括重复行，且会按默认规则排序。示例：

可通过 INTERSECT 操作符求两个结果集的交集。交集结果集不包括重复行，且会按默认规则排序。示例：

无论是交并差中的那种集合查询，只需要在最后一个查询的后面加上 ORDER BY 子句，即可对整个结果集排序。示例：

有个需要注意的细节问题是：这个 ORDER BY 后的排序字段不能加任何别名限定，这个也好理解，毕竟排序操作是针对整个结果集的。也就是说上例中 ORDER BY 后的 <code>dept_code</code> 不属于 t1、t2、t3 中任何一个表，而是整个结果集的。那万一 t1、t2、t3 表中的字段名不相同的话，排序的字段名又改如何确定呢？本人的测试结果是：它一定是第 1 个查询里的字段名或列别名，但前提是后面所有查询的该列都是真实字段名，或列别名与第 1 个查询里的字段名或列别名相同，NULL 除外。如果你嫌这个规则太复杂也不好记，那么也可以给所有查询的该列取一个相同的别名。另外还可以利用 ORDER BY 的另一个语法规则：<code>ORDER BY N</code>，即直接写排序字段的列编号。如上例的查询想要根据第 2 列来排序，这一这么来写：

DISTINCT 子句的作用就是把一组数据中的重复行去掉，留下唯一的数据。

示例：

COUNT、SUM、MAX、MIN、AVG 五个常见聚合函数内部都支持 DISTINCT。

语法：

示例一：

示例二：