Oracle之子查询

子查询 //在一个查询语句中，嵌入一条或者多条查询语句，该条嵌入的查询语句的结果成为外层查询的一部分

注意：最外层称为主查询，里层的查询称为子查询// 子查询出现的形式可以是层层嵌套，还可以并列形式存在

子查询必须用括号括起来

子查询分类：

根据子查询是否能单独运行分为：

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后面