子查询
分为 关联子查询 和 非关联子查询
特点：子查询可以单独执行，其结果作为外部查询的条件。
不相关子查询
概念：子查询不需要与父查询的结果进行比对
1
2
3
4
5
6
7
8
9
10
11
12
-- 查询各个部门薪水最高的员工信息
select * from emp
where (deptno, sal) in
(
select deptno, max(sal) from emp group by deptno
)
-- 哪些部门没有员工
select * from dept
where deptno not in
(
select deptno from emp group by deptno
)
整个过程中，父查询的值没有进入子查询
普通 where 条件 子查询
1
2
3
4
5
6
-- 查询所有工资高于FORD的员工信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp
where sal >
(
select sal from emp where ename = 'FORD'
)
not in 型子查询
1
2
3
4
5
-- 哪些员工没有下属
select emp.* from emp where empno not in
(
select mgr from emp where mgr is not null
)
不要将 null 参与 not in 的计算，会导致无法查询任何数据
in 型子查询
1
2
3
4
5
6
-- 查询所有 'SALESMAN' 所在的部门信息
select * from dept
where deptno in
(
select deptno from emp where job = 'SALESMAN'
)
如果子查询返回一个数据，可以使用 =，<，>，<=，>=，<> 这些单行比较运算符
而如果子查询返回的是多行数据，就必须使用多行比较运算符： in
最值子查询
1
2
3
4
5
6
7
8
9
10
11
12
-- 查询收入最低的员工信息(编号，姓名)
select empno, ename from emp
where sal =
(
select min(sal) from emp
)
-- 查询 emp 中薪水最高的人
select empno, ename from emp
where sal =
(
select max(sal) from emp
)
相关子查询
概念：子查询需要与父查询的 每一个 结果进行比对，从而得到满足条件的数据。
相当于是做一个双重循环
第一重循环是由父查询执行的select
第二重循环是在父查询每执行一次之后，子查询进行select，用自己查出的数据与父查询传下来的一个值进行遍历比较，如果符合条件，则父查询将其输出。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 同上一题
select * from emp e1
where sal =
(
select max(sal) from emp e2
group by deptno
where e2.deptno = e1.deptno
)
-- 哪些员工的薪水比本部门的平均薪水高
select * from emp e1
where sal >
(
select avg(sal) from emp e2
where e2.deptno = e1.deptno
)
exists与相关子查询的结合
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询所有有下属的员工
select * from emp e1
where exists
(
select * from emp e2
where e1.empno = e2.mgr
)
-- 查询没有部门的员工
select * from dept d
where not exists
(
select * from emp e
where e.deptno = d.deptno
)
建议
-
优先采用多表连接查找
部分聚合函数比较的条件用子查询更容易实现。
-
所有带 in 、比较运算符、any 和 ALL 的子查询都能用 exists 的子查询 等价替换
但带 exists 或 not exists 的子查询不能被其他形式的子查询等价替换。
特殊查询
DECODE （Oracle 特有）
类似于java的 switch…case…
1
2
3
4
5
6
7
8
select ename, job, sal,
DECODE(
job, 'MANAGER', 1.2,
'ANALYST', 1.1,
'SALESMAN', 1.05,
1.0
)*sal bonus
from emp;
Case… When （sql几乎通用）
1
2
3
4
5
6
7
8
select ename, job, sal,
case job when 'MANAGER' then sal*1.2
when 'ANALYST' then sal*1.1
when 'SALESMAN' then sal*1.05
else sal
end
bonus
from emp;
集合操作（了解为主）
集合A={1，2，3，4，5，6}
集合B={2，4，6，8}
- 并集：A union B = {1，2，3，4，5，6，8}
- 全并集：A union all B = {1，2，2，3，4，4，5，6，6，8}
- 交集：A intersect B = {2，4，6}
- 差集：A minus B = {1，3，5}