oracle中的相关子查询(correlated subqueries)

以前我们曾介绍过oracle中的嵌套子查询，你可以在你需要的任何位置(除了group by子句)使用嵌套之查询，例如常用的where子句位置，你也可以在having子句中使嵌套子查询。嵌套子查询实际上oracle中一种比较简单的子查询，oracle中稍微复杂的查询是相关子查询。

相关子查询的典型结构如下：
select columnlist from table1 t1 where column2 in (select column3 from table2 t2 where t2.column3 = t1.column4 )
也就是说在子查询中使用到了外查询的表和相关的列。这样无法像嵌套子查询一样一次将子查询的结果计算出来然后再和外查询挨个比对，相关子查询对于外部查询的每一个值都会有一个结果与其对应，其计算的过程是这样的：
1.扫描外查询的第一条记录
2.扫描子查询，并将第一条记录的对应值传给子查询，由此计算出子查询的结果
3.根据子查询的结果，返回外查询的结果。
4.重复上述动作，开始扫描外查询的第二条记录，第三条记录，直至全部扫描完毕

对于相关子查询我们还是来举几个例子比较好：
1.下面这个子查询虽然在外查询和内查询使用了同一个表，但他确实是一个相关子查询。这是因为子查询引用了外查询的列。这里的SQL语句找出来的结果是那些工资高于其所在部门的平均工资的那些员工。
select ename,sal from scott.emp t1
where sal >= (select avg(sal) from scott.emp t2 where t2.deptno = t1.deptno);

2.下面这个例子则是使用先关子查询来求出那些换了二次或者二次以上工作的员工。
select last_name
from hr.employees e
where 2 <= (select count(*) from hr.job_history j where j.employee_id = e.employee_id)

这里的例子都是在用在查询中，实际上oracle中的相关子查询是可以用在很多地方的，例如在update语句中，我们知道在oracle中没有update from 语句，似乎不如SQL Server那么灵活，但我们可以使用oracle中的相关子查询来解决这个问题，关于这个问题，在前面已经有所涉及，如果有兴趣，可以点连接进去看看。同样我们亦可以在delete语句中使用相关子查询。以及在exists操作符后面使用相关子查询等等。这个会稍后介绍。

 

 

Oracle的嵌套子查询 

Oracle的子查询分为两类分别是嵌套子查询和相关子查询，这里要介绍的是嵌套子查询。
所谓嵌套子查询是指，子查询是一个独立的查询不与外部查询相关，子查询将被先执行，而且只被执行一次，子查询执行完成后，再执行外部的查询，外部查询在执行过程中会使用到子查询的结果。
下面是一个嵌套子查询的例子：
select ename,sal
from emp
where sal >
(select avg(sal) from emp);
其含义是找出在emp员工中所有工资要高于平均水平的员工的姓名和工资。
在这里oracle将会先执行select avg(sal) from emp这个子查询然后，然后在执行外部的查询。
对于嵌套在查询有几点需要注意：
1. 子查询一定要包含在一对括号内。
2. 将子查询放在比较运算符的右边
3. “order by”在子查询中是不需要，除非当你想使用一个top-n的分析
4. 当你的子查询结果预计只有一个时请使用单行操作符，当你的子查询结果预计有多个时，请使用多行操作符。
这里所谓的当行操作符就是我们常用的(>,<,>=,<=,<>等等)，所谓多行操作符就是在oracle的子查询中使用in,all,any,some,not in等等
例如下面这段SQL语句的意义是找出那些deptno为20的人的姓名和工资
Select ename,sal
From emp
Where empno in
(
Select empno from emp where deptno = 20
);
当然这里我们完全没有必要使用子查询，之所以在这里用了子查询是因为，我们说明这里的子查询返回了多个结果，我们必须使用多行操作符