5.子查询嵌套查询-CSDN博客

本文链接：https://blog.csdn.net/ruyigongfang/article/details/141432172

定义:在一个查询语句中嵌套一个或者多个查询语句，该嵌套的查询语句成了外层查询的一部分

里层查询 --子查询

外层查询 --主查询

把子查询（里层查询）的结果，当做条件来进行主查询（外层查询）。

分类：

按照子查询是否与主查询相关联来分类：

1.不相关子查询：

子查询可以单独运行，子查询不会牵扯到主查询的部分内容，

主查询只在乎子查询的查询结果，把子查询（里层查询）的结果，当做条件来进行主查询（外层查询）

2.相关子查询：

子查询不能单独运行，子查询会牵扯到主查询的部分内容

主查询只在乎和子查询的关联关系

不相关子查询：

子查询可以单独运行，子查询不会牵扯到主查询的部分内容，

主查询只在乎子查询的查询结果，把子查询（里层查询）的结果，当做条件来进行主查询（外层查询）

分类：按照子查询的查询结果不同分类

1.1单列子查询

语法：select *|列 from 表名1 where 列 =|<>|>|<|in (select 列 from 表名2) ;

案例：

1.查询地点在纽约的部门下的所有员工信息

select * from dept;
--分步：
--1.地点在纽约的部门编号
select deptno from dept where loc = 'NEW YORK';
--2.查询步骤1结果中的员工信息
select * from emp where deptno=10;
--3.合并前2步：
select * from emp where 
deptno=(select deptno from dept where loc = 'NEW YORK');  ---子查询需要括号括起来

2.查询和SMITH在同一个部门下的员工名字，工资，部门编号

--分步：
--1.SMITH的部门编号
select deptno from emp where ename='SMITH';
--2.查询步骤1结果中的员工信息
select * from emp where deptno=20;
--3.合并前2步：
select * from emp 
where deptno=(select deptno from emp where ename='SMITH');  ---子查询需要括号括起来

3.查询emp表中一共有多少个部门

--方法一：
--把子查询的结果当作一个临时的结果表
--(子查询实现)
--分步：
--1.先取到员工表中不同的部门编号：
select deptno from emp group by deptno;
select distinct deptno from emp ;
--2.把步骤1的结果当作一个临时表，取该表中的记录数：
select count(1) from (select deptno from emp group by deptno);
select count(1) from (select distinct deptno from emp);

--方法二：
--count(列) ---取该列不为空的值的个数
select count(distinct deptno) from emp;

1.2多列子查询

语法：select * from 表名1 where (列1，列2) =|in (select 列1，列2 from 表2 where 条件);

--1.查询与SMITH相同部门且相同岗位的员工信息

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

--2.查询和10号部门的职位和领导都相同的员工信息

select * from emp where (job,mgr)in (select job,mgr from emp where deptno=10) and deptno<>10;

注意：多列子查询查询出来的列的个数，顺序，数据类型要和主查询条件里保持一致。

相关子查询：

子查询不能单独运行，子查询会牵扯到主查询的部分内容

主查询只在乎和子查询的关联关系

案例：

1.查询员工姓名，岗位，薪资，部门编号，以及对应的部门名称

1.1 主查询
select ename,job,sal ,t.deptno ,(对应的部门名称)  
from emp t;
1.2 子查询：
select dname from dept where deptno =t.deptno;
1.3合并前两步：
select ename,job,sal ,t.deptno ,(select dname from dept where deptno =t.deptno) as 部门名称 
from emp t;

select ename,job,sal ,deptno ,(select dname from dept where deptno =deptno) as 部门名称 
from emp ;  ---报错：单行子查询返回多行

2.要求每个员工后边显示他自己的领导姓名

2.1 每个员工的信息
select empno,ename,t.mgr,(显示他自己的领导姓名) from emp t;
2.2 员工的领导姓名
select ename from emp where empno=t.mgr;  ---员工的领导编号 ==领导的员工编号

2.3合并前两步
select empno,ename,t.mgr,(select ename from emp where empno=t.mgr) from emp t;

--在相关子查询中，子查询和主查询用到的表，一定要起别名区分。
select empno,ename,t.mgr,(select ename from emp where empno=t.mgr) 
from emp t;

3.查询工资高于自己部门平均工资的员工信息

3.1 员工信息
select * from emp where sal>(自己部门平均工资)
3.2 自己部门平均工资
--10部门的平均工资
select avg(sal) from emp where deptno=10;
--20部门的平均工资
select avg(sal) from emp where deptno=20;
select avg(sal) from emp where deptno=t.deptno;

3.3合并前两步
select t.*, (select avg(sal) from emp where deptno = t.deptno)
  from emp t
 where sal > (select avg(sal) from emp where deptno = t.deptno);