子查询 //在一个查询语句中,嵌入一条或者多条查询语句,该条嵌入的查询语句的结果成为外层查询的一部分
注意:最外层称为主查询,里层的查询称为子查询// 子查询出现的形式可以是层层嵌套,还可以并列形式存在
子查询必须用括号括起来
子查询分类:
根据子查询是否能单独运行分为:
1.相关子查询
//子查询不能单独运行,子查询牵扯到主查询的部分内容,主查询只在乎与子查询的关系
2.非相关子查询
//子查询能单独运行,子查询不牵扯到主查询的部分内容,主查询只在乎与子查询的结果
非相关子查询:根据子查询返回的结果不同,分为四种情况:
1.单行单列
select avg(sal) from emp;
select ename,job,sal from emp where sal>(select sal from emp where ename='JONES')
2.单列多行
查询和SMITH相同岗位并且相同薪资的员工信息
select * from emp where (job,sal)=(select job,sal from emp where ename='SMITH' )
注意:子查询语句查询的结果要与主查询列的个数、顺序、属性要一致’
3.单行多列
查询和SMITH和KING 的工作部门
select deptno from emp where deptno in (select deptno from emp where ename='SMITH' or ename='KING ')
练习:
1.查询哪些员工是领导
SELECT * FROM EMP
select ename
from emp
where EMPNO in(select mgr
from emp )
2.查询不在SMITH 和ALLEN 部门工作的员工信息
select *
from emp
where deptno not in((select deptno
from emp
where ename = 'SMITH'
or ename = 'ALLEN'))
4.多行多列
//看做一张表
查询人数最多的部门
select count(1)from emp group by deptno;
注意:子查询当做数据源,select后面出现了原表中没有的列,必须使用别名才能被主查询使用
相关子查询
查询各个部门的员工姓名、部门编号,所在部门的平均工资
select ename, deptno,(所在部门的平均工资) from emp a
所在部门的平均工资:select avg(sal) from emp b where b.deptno=a.deptno
//b.代表表名归属deptno
select ename,
deptno,
(select avg(sal) from emp b where b.deptno = a.deptno)
from emp a
//逐条匹配
查询比自己所在岗位平均工资高的员工姓名,岗位,工资
select ename,job,sal,
(select avg(sal) from emp b where b.job=a.job)
from emp a
where sal>(select avg(sal) from emp b where b.job=a.job)
注意:
1.子查询作为一个结果放在where 后面和放在having后面//可以使用n行n列的每一种情况
//子查询和主查询查询的条件列个数属性要一致
//子查询作为一个数据源放在from后面(//可以使用n行n列//子查询作为数据源,必须取别名
///子查询作为一个常量跟在select后面(只能使用单行单列的情况或者使用相关子查询))
子查询不能放在group by后面,不建议放在order by后面