定义，就是指查询中嵌套查询。
子查询可以出现在很多位置，比如: 当列、当表、当条件等
语法：
SELECT (子查询)
FROM (子查询)
WHERE (子查询)
GROUP BY 子句
HAVING (子查询)
ORDER BY 子句
注：子查询要用括号括起来。
如：
-- 找出与Ben同一个部门的员工
--第一步：先找出Ben所在的部门
select dept_id from s_emp where first_name = 'Ben';
--第二步：找出该部门的所有员工
select first_name from s_emp where dept_id = 数值
--第三步：
select first_name from s_emp where dept_id =(select dept_id from s_emp where first_name = 'Ben') and first_name != 'Ben';
如：
-- 查询出顾客名及他拥有的订单数
--第一步：
select c.name,订单数 from s_customer c;
--第二步：订单数
select c.name,count(o.id) from s_ord o right join s_customer c on o.customer_id = c.id group by c.name;
--第三步：合并
select c.name,(select count(*) from s_ord o where o.customer_id = c.id) from s_customer c;
select c.name,count(o.id) from s_ord o
right join s_customer c on o.customer_id = c.id
group by c.name;
-- 查询出部门名及此部门的员工数。 [使用子查询]
select c.name,(select count(*) from s_emp e where e.dept_id = c.id) from s_dept c;
-- 查询出工资超过公司平均工资 的员工.
--第一步
select avg(salary) from s_emp
--第二步
select first_name from s_emp where salary >第一步
--3合并
select salary,(select avg(salary) from s_emp)，first_name from s_emp where salary > (select avg(salary) from s_emp) ;
-- 查询部门平均工资超过公司平均工资的部门
select e.dept_id,avg(salary) "部门平均工资"
from s_emp e group by e.dept_id
having avg(salary) > (select avg(salary) from s_emp);
子查询分类
1. 无关子查询
是指子查询中没有使用外部查询所定义的变量/别名。
2. 相关子查询
是指子查询中要使用外部查询所定义的变量/别名。
==========================
rownum
它总是从1开始，依次递增，绝不会产生GAP【间隔】
如：
select d.*,rownum from s_dept d where rownum = 1;
select d.*,rownum from s_dept d where rownum = 5;
select d.*,rownum from s_dept d where rownum > 5;
select d.*,rownum from s_dept d where rownum < 5;
-- rownum 的作用
1. TopN 问题
解题思路：
a.利用子查询先按要求的列做排序.
b.再利用外部查询以及rownum 来过滤出 TopN记录
-- 查询出工资排名前三的员工
//错误
select e.id,e.first_name from s_emp e order by salary desc and rownum <=3;
//正确
--第一步：查出所有员工的工资情况，并且按照工资降序排列
select * from s_emp order by salary desc;
--第二步：再取前三
select * from (select * from s_emp order by salary desc) where rownum <=3;
--查询出41部门工资最高的员工
select first_name from (select * from s_emp where dept_id = 41 order by salary desc)where ronum <2;
--查询出拥有订单数排名前3的客户
select * from (select c.name count(o.id) from s_customer c left join s_ord o on o.customer_id = c.id group by c.name order by count(o.id) desc) where rownm <=3;
2. 分页查询问题
-- 查询出员工表中第2页[第7行至第12行]数据。
//错误
select * from s_emp where rownum between 7 and 12;
select * from s_emp where rownum >=7 and rownum<= 12;
//正确：方法一
select * from (
--中间层，负责把上限行给取出来
select v1_.*,rownum rw_ from (
-- 业务的核心语句
select id,first_name,salary from s_emp
) v1_
where rownum <= 12
) v2_
where v2_.rw_ >= 7;
方法二：
select * from
(select * from s_emp where rownum <=12)
where id not in (select id from s_emp where rownum <7);
-- 查询出在'Asia'地区工作的员工的第二行至第四行记录。
select * from (
--中间层，负责把上限行给取出来
select v1_.*,rownum rw_ from (
select first_name,salary,dept_id from s_emp where dept_id in (
select id from s_dept where region_id = (
select id from s_region where name = 'Asia'
)
)
) v1_
where rownum <= 4
) v2_
where v2_.rw_ >= 2;
----------------------------------------
有关 exists 与 not exists 运算符
exists用来判断子查询是否有结果，如果有，则返回TRUE,否则，返回FALSE
-- 找出各部门工资排名前2名的员工
分析:存在这样的员工，与我同一部门并且比我工资高的员工不超过1个
select dept_id,first_name,salary from s_emp e1
where exists (
select 1 from s_emp e2
where e1.dept_id = e2.dept_id
and e1.salary < e2.salary
having count(*) <= 1
)
order by dept_id,salary desc;
==
关于子查询的集合操作
UNION 返回两个子查询的并集，不含重复记录
UNION ALL 返回两个子查询的并集，包含复复记录
如：
select id,first_name from s_emp where salary > 1200
union
select id,first_name from s_emp where salary between 850 and 1400;
INTERSECT 返回交集
MINUS 两个子查询相减