关联子查询+嵌套子查询

关联子查询
定义
1引用外部的一列或多列
2将外部查询的每一行都传递给子查询，子查询依次读取传递过来的每一行的值，并将其使用到子查询上，直到外部查询的所有行都处理完为止，然后返回子查询的结果

获取员工工资低于所在部门的平均工资的员工信息
select empno,ename,sal,deptno from emp out
where sal <(
select avg(sal) from emp inner
where inner.deptno=out.deptno
)

上面等价于

select  out.empno,out.ename,out.sal,out.deptno from emp out, 
(
select avg(sal) avgSal ,deptno from emp 
group by deptno
) inner
where inner.deptno=out.deptno
and out.sal<inner.avgSal

解释
1外部查询（select empno,ename,sal,deptno from emp）的的每一行记录传递给子查询
（select avg(sal) from emp inner
where inner.deptno=out.deptno）
2.子查询读取读取外部传过来的值，最后返回部门平均工资的列(隐藏分组deptno)

简单的理解就是：

select out.empno,out.ename,out.sal,out.deptno from emp out,
(
select avg(sal) avgSal ,deptno from emp
group by deptno
) inner
where inner.deptno=out.deptno
and out.sal< inner.avgSal
out表关联inner表，在获取小于平均工资的员工

exists在关联子查询的使用

获取80年的员工号信息
select empno,ename,sal,hiredate,substr(hiredate,8,2)
from emp out where exists(
select empno from emp inner
where out.empno=inner.empno
and substr(hiredate,8,2)=’80’);

select empno,ename,sal,hiredate,substr(hiredate,8,2)
from emp out where exists(
select 1 from emp inner
where out.empno=inner.empno
and substr(hiredate,8,2)=’80’);

嵌套子查询
子查询内部嵌套子查询，嵌套子查询层数最大255层

根据工作地点在'NEW YORK','CHICAGO'的2个部门，根据2个部门平均工资的最大值，获取工资大于这个最大值的员工信息
select empno,ename,ename,sal,deptno from emp
where sal>(
            select  max (avg(sal)) from emp where deptno in
                    (
                    select deptno from dept
                    where loc in ('NEW YORK','CHICAGO')
                    )
            group by deptno
          );

解释
1 (
select deptno from dept
where loc in (‘NEW YORK’,’CHICAGO’)
)
获取工作地点在’NEW YORK’,’CHICAGO’的2个部门

2select max (avg(sal)) from emp where deptno in
(部门集合 )
group by deptno
获取 部门的平均工资，在获取平均工资的最大值