子查询

用于当一次查询的结果是另一次查询所需要的时候, 可以使
用子查询

1 单行子查询

子查询的返回结果是单行数据.

a) 查询所有比“CLARK”工资高的员工的信息

select * from emp 
where sal > (select sal from emp where  ename='CLARK'); 

b) 查询工资高于平均工资的雇员名字和工资

select ename, sal from emp
where sal>(select avg(sal) from emp); 

c) 查询和 SCOTT 同一部门且比他工资低的雇员名字和工资

select ename, sal, deptno 
from emp 
where deptno=(select deptno from emp where 
ename='SCOTT') 
and sal<(select sal from emp where ename='SCOTT'); 

d) 查询职务和 SCOTT 相同，比 SCOTT 雇佣时间早的雇员信息

select * 
from emp 
where job=(select job from emp where ename='SCOTT') 
and hiredate<(select hiredate from emp where 
ename='SCOTT'); 

2 多行子查询

子查询的返回结果是多行数据. 此时, 不能再使用普通的比较运算符了.
多行记录比较运算符:
ANY: 跟结果中的任何一个数据进行比较

查询工资低于任何一个“CLERK”的工资的雇员信息

select * from emp where sal<ANY(select sal from emp 
where job='CLERK') and job<>'CLERK'; 

ALL: 跟结果中的所有数据进行比较

查询工资比所有的“SALESMAN”都高的雇员的编号、名字和
工资

select empno, ename, sal 
from emp 
where sal>ALL(select sal from emp where 
job='SALESMAN'); 

IN: 等于结果中的任何一个

查询部门 20 中职务同部门 10 的雇员一样的雇员信息

select * 
from emp 
where job in (select job from emp where deptno=10) 
and deptno=20; 

select * 
from emp 
where job =any (select job from emp where deptno=10) 
and deptno=20;

3 相关子查询和不相关子查询

不相关子查询: 子查询不会用到外查询的数据, 子查询可以独立运行.

相关子查询: 子查询会用到外查询的数据, 子查询不能独立运行.

查询本部门最高工资的员工
a) 不相关子查询的实现方式

select * 
from emp e 
where (e.deptno, e.sal) in (select deptno, max(sal) 
from emp group by deptno); 

b) 相关子查询的实现方式
查询最高工资的部门号

select * 
from emp e 
where sal=
(select max(sal) from emp where deptno=e.deptno); 

4 子查询可以作为一张表格进行多表连接查询 

查询每个部门平均薪水的等级

select t.deptno, t.avg_sal, s.grade 
from salgrade s 
join ( 
     select deptno, avg(sal) avg_sal from emp group 
by deptno 
) t 
on t.avg_sal between s.losal and s.hisal 
order by t.deptno;