定义
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
- 2
- 3
- 4
- 5
- 6
- 7
解释
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
解释
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
获取 部门的平均工资,在获取平均工资的最大值