ORACLE复杂查询之子查询

子查询分为两类：标准子查询和相关子查询。

一、标准子查询：子查询先于主查询独立执行，返回明确结果供主查询使用。

子查询只执行一次，不依赖于主查询。

例如：

其中子查询能够返回结果：2450。所以断定其为标准子查询。

1、单行子查询（子查询返回的结果是单行单列）

WHERE子句中使用单行子查询时，可以使用单行比较运算符（=,>,>=,<,<=,<>），将子查询的返回结果当做单一数值来使用。

需要小心的是子查询返回的结果的不确定定，即返回空值或多值。具体分为三种情况：

（1）返回单行：即为单行子查询。

（2）未返回任何行：如果子查询未返回任何行（相当于返回NULL），则主查询将不再执行，所以主查询也不会返回任何结果。

（3）返回多行：是多行子查询，此时不允许使用单行比较运算符，否则出错。

2、多行子查询（子查询返回多行单列）

在WHERE子句中使用多行子查询时，可以使用多行比较运算符（IN,ALL,ANY），将子查询的返回结果当做数值集合来使用。其中，ALL和ANY运算符必须与单行比较运算符（=,>,>=,<,<=,<>）结合使用。例如WHERE sal>ALL(子查询)。

IN：等于任何一个

ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于sal>3，即大于所有。

ANY：和子查询返回的任意一个值比较。例如：sal>ANY(1,2,3)等价于sal>1，即大于任意一个就可以。

EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为TRUE，否则为FALSE。（常用于相关子查询）

3、多列子查询（子查询返回多列，可以是单行、多行或者不返回任何结果）

在WHERE子句中使用多列子查询时，

a.如果返回结果是单行多列，则可以使用单行比较运算符；例如

b.如果返回结果是多行多列，则只能使用多行比较运算符（IN,ALL,ANY）；例如：

c.如果未返回结果，则主查询也不返回结果。

二、相关子查询：子查询不能提前运行以得到明确结果。

子查询执行过程中需要用到主查询的相关结果，即子查询在主查询返回的结果集上执行（针对主查询的每一行结果，子查询都要执行一次）。子查询和主查询在执行过程中相互依赖。

例1：SELECT字段列表中使用子查询

其中，只看子查询，发现emp.deptno=dept.deptno中的emp并不存在与子查询的FROM语句中，而是存在于外层的主查询中，所以断定其为相关子查询。

等价于：

可见，等价后的WHERE中增加了一个条件。

例2：WHERE子句中使用子查询

等价于：

可见，等价后的WHERE子句中增加了一个条件。

例3：WHERE子句中使用EXISTS（子查询）

等价于：

注意这里使用DISTINCT的原因。第一个查询是从dept中查出的，所有不会重复，但第二个查询是连接查询，会有重复的内容（emp表中的不同员工可能属于同一部门，即一个部门可能有超过一名员工），所以要用DISTINCT去重。