MySQL数据库（七）----查询相关操作（子查询）

子查询是指在一个查询语句（主查询）内部嵌套的另一个查询语句。子查询可以出现在 SELECT、FROM、WHERE、HAVING 等子句中。它可以将一个复杂的查询问题分解为多个简单的查询步骤，从而更方便地获取所需的数据。

引入子查询：

-- 查询所有比“CLARK”工资高的员工的信息  
-- 步骤1：“CLARK”工资
select sal from emp where ename = 'CLARK'  -- 2450
-- 步骤2：查询所有工资比2450高的员工的信息  
select * from emp where sal > 2450;
-- 两次命令解决问题 --》效率低 ，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是
-- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改
-- 将步骤1和步骤2合并 --》子查询：
select * from emp where sal > (select sal from emp where ename = 'CLARK');
-- 一个命令解决问题 --》效率高

执行顺序：
先执行子查询，再执行外查询；
不相关子查询：
子查询可以独立运行，称为不相关子查询。
不相关子查询分类：
根据子查询的结果行数，可以分为单行子查询和多行子查询。

单行子查询：

通常返回一个单一的值（一个标量值），可以用于 WHERE 子句的条件判断。

例子：

1、查询工资高于平均工资的雇员名字和工资。

select ename, sal
from emp
where sal > (select avg(sal) from emp);  -- 子查询：计算所有雇员的平均工资，并返回该值-- 外部查询：选择所有工资大于平均工资的雇员名字和工资

2、查询和CLARK同一部门且比他工资低的雇员名字和工资。

select ename, sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK')  -- 子查询1：查找CLARK所在的部门编号and sal < (select sal from emp where ename = 'CLARK');  -- 子查询2：查找CLARK的工资-- 外部查询：选择在CLARK所在部门并且工资低于CLARK的雇员名字和工资

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

select * 
from emp
where job = (select job from emp where ename = 'SCOTT')  -- 子查询1：查找SCOTT的职务and hiredate < (select hiredate from emp where ename = 'SCOTT');  -- 子查询2：查找SCOTT的雇佣日期-- 外部查询：选择职务与SCOTT相同且雇佣日期早于SCOTT的所有雇员信息

多行子查询：

返回多个值，可以与 IN, ANY, ALL 等操作符一起使用，用于在外部查询中进行多值比较。

例子：

1、查询【部门20中职务同部门10的雇员一样的】雇员信息。

select * from emp 
where deptno = 20 
and job in (select job from emp where deptno = 10);  -- 子查询：返回部门10中的所有职务-- 外部查询：选择部门20中职务与部门10相同的所有雇员信息

select * from emp 
where deptno = 20 
and job = any(select job from emp where deptno = 10);  -- 子查询：返回部门10中的所有职务-- 外部查询：选择部门20中职务与部门10中任意职务相同的所有雇员信息

• IN 和 ANY 都能处理多行子查询。IN 用于在外部查询中选择在子查询返回的多个值中的任意一个匹配项。
• ANY 允许外部查询的条件与子查询返回的多个值进行比较，条件满足任意一个值即可。

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

-- 多行子查询：
select empno, ename, sal 
from emp 
where sal > all(select sal from emp where job = 'SALESMAN');  -- 子查询：返回所有“SALESMAN”的工资-- 外部查询：选择那些工资大于所有“SALESMAN”的工资的雇员信息

-- 单行子查询：
select empno, ename, sal 
from emp 
where sal > (select max(sal) from emp where job = 'SALESMAN');  -- 子查询：返回“SALESMAN”中最高的工资-- 外部查询：选择工资大于最高“SALESMAN”工资的雇员信息

• 多行子查询：ALL 用来与子查询返回的所有值进行比较，只有当外部查询的值大于子查询中所有返回的工资时才会返回结果。
• 单行子查询：MAX 聚合函数用于获取 SALESMAN 中最高的工资，外部查询返回工资大于该最高工资的雇员。

3、查询工资低于任意一个“CLERK”的工资的雇员信息。

select * 
from emp
where sal < any(select sal from emp where job = 'CLERK') 
and job != 'CLERK';  -- 子查询：返回所有“CLERK”的工资-- 外部查询：选择工资低于任意“CLERK”的工资的雇员信息，排除职务为“CLERK”的雇员

-- 单行子查询：
select * 
from emp
where sal < (select max(sal) from emp where job = 'CLERK') 
and job != 'CLERK';  -- 子查询：返回“CLERK”中最高的工资-- 外部查询：选择工资低于“CLERK”中最高工资的雇员信息，排除职务为“CLERK”的雇员

• 多行子查询：ANY 用于选择那些工资低于 CLERK 中任意一个工资的雇员。
• 单行子查询：MAX 聚合函数返回 CLERK 中最高的工资，外部查询选择工资低于该最高工资的雇员。

 

不相关的子查询引入：
不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。
相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

相关的子查询优缺点：
好处：简单   功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
缺点：稍难理解

例子：

1、 查询本部门最高工资的员工  。

 方法1：通过不相关子查询实现：

select * from emp 
where deptno = 10 
and sal = (select max(sal) from emp where deptno = 10)  -- 子查询：查找部门10中的最高工资
union
select * from emp 
where deptno = 20 
and sal = (select max(sal) from emp where deptno = 20)  -- 子查询：查找部门20中的最高工资
union
select * from emp 
where deptno = 30 
and sal = (select max(sal) from emp where deptno = 30);  -- 子查询：查找部门30中的最高工资

• 使用 UNION 将每个部门的查询结果合并，返回部门10、20、30中最高工资的员工信息。
• 缺点：这种方式需要手动为每个部门写查询，如果部门数量增加，SQL 语句也会随之增长，效率不高，且不具备通用性

方法2： 相关子查询:

select * from emp e 
where sal = (select max(sal) from emp where deptno = e.deptno)  -- 相关子查询：查找每个员工所在部门的最高工资
order by deptno;  -- 按照部门排序

• 这种方式不需要手动列出每个部门，能够动态地计算出所有部门的最高工资并返回对应的员工信息。
• 优点：比不相关子查询更简洁，且能够自动适应不同的部门数量。

 

2、查询工资高于其所在岗位的平均工资的那些员工。

不相关子查询：

select * from emp 
where job = 'CLERK' 
and sal >= (select avg(sal) from emp where job = 'CLERK');  -- 子查询：计算所有“CLERK”岗位的平均工资

• 外部查询选择所有工资高于或等于该平均工资的 "CLERK" 岗位的员工。
• 缺点：这种方式无法动态处理不同岗位，若要查询其他岗位，需要重复编写类似的查询。

 

相关子查询：

select * from emp e 
where sal >= (select avg(sal) from emp e2 where e2.job = e.job);  -- 相关子查询：查找每个员工所在岗位的平均工资

• 外部查询选择那些工资高于或等于自己岗位的平均工资的员工。
• 优点：此方法更加通用，可以适用于所有岗位，不需要手动指定岗位名称（如 'CLERK'），它会动态计算每个岗位的平均工资并返回符合条件的员工。

总结

子查询是 SQL 中非常重要的功能，可以将复杂的查询问题分解为多个简单的步骤。

不相关子查询和相关子查询各有优缺点，应该根据实际场景选择使用。

不相关子查询适合用于简单的查询，相关子查询则更适合处理依赖外部查询数据的复杂情况。