oracle的子查询概述
分类:
单行子查询:返回结果是单行就是单行查询
多行子查询:返回结果是多行就是多行查询
子查询:嵌套查询,嵌入其他sql语句中的色select语句
(1)可以使用子查询的位置:where;select;having;from
(2)子查询用小括号括起来
(3)group by不可以使用子查询
(4)主查询和子查询可以不是同一张表
例子:查询部门名称是accounting的员工信息
方法(1) 子查询
select *
from emp
where deptno =(select deptno from dept where dname='accounting');
方法(2) 多表查询
select *
from emp e,dept d
where e.deptno = d.deptno and d.dname ='ACCOUNTING';
结论:根据业务需求和具体情况选择运行效率高的方式
(5)一般子查询不使用排序,但在top-N分析问题时,必须对子查询排序
例: rownum:序号(伪列)
select rownum,empno,ename,sal
from(select * from emp order by sal desc)
where rownum<=5
注:rownum只能<=或者<,不能>=或>
(6)一般先执行子查询再执行主查询,但相关子查询例外
当子查询需要引用主查询时,oracle会执行相关子查询
相关子查询是先执行主查询,再执行子查询
示例:查询工资高于部门平均工资的雇员名,工资和部门号
select ename,sal,deptno
from emp e
where sal>(select avg(sal)
from emp
where deptno = e.deptno);
(7)单行子查询只能使用单行操作符,多行子查询只能使用多行操作符
(8)子查询中的空值null问题
单行子查询中如果返回一个空值,则主查询将不会查到任何结果
多行子查询中如果返回一个空值,则主查询将不会查到任何结果
(想正常显示多行查询,可以将空值去掉)
(9)子查询书写风格
(10) 一个主查询可以有多个子查询
例:查询职位和7521相同,并且工资大于7934这个员工工资的员工信息
select *
from emp
where job=(select job from emp where empno =7521)
and sal>(select sal from emp where empno =7934)
---------------------------------------------------------------------
示例:查询工作地在new york 和chicago的部门所对应的员工信息
select *
from emp
where deptno in(select deptno
from dept
where loc='new york’or loc ='chicago');
(2)在多行子查询中使用any操作符
示例:查询工资高于10号部门任意一个员工的员工名,工资和部门号
select ename,sal,deptno
from emp
where sal>any(select sal
from emp
where deptno=10);
(3)在多行子查询中使用all操作符
示例:查询工资高于30号部门所有员工的员工名,工资和部门号
select ename,sal,deptno
from emp
where sal>all(select sal
from emp
where deptno = 30)
-----------------------------------------------------------
(1)在DML中的子查询
select * from emp
where job = (select job from emp where enamel= 'smith')
(2)在DDL中的子查询
create table empnew
as select * from emp
将emp中的值赋值给newemp表
-----------------------------------------------------------
例:在where子句中进行子查询
查询职位和smith一样的所有员工信息(ename:职位)
方法(1)
select job from emp where ename ='clerk’(得到smith的职位是clerk)
select * from emp where job ='clerk’
方法(2)
select *
from emp
where job =(select job
from emp
where ename ='clerk’)
----------------------------------------------------------
在select子句中进行子查询
查询出每个部门的编号(deptno),名称(dname),位置(loc),部门人数:count(empno)
select deptno,dname,loc,(select count(empno)
from emp
where emp.deptno = dept.deptno) cnt
from dept
---------------------------------------------------------------
在having子句中进行子查询
查询员工信息表,按部门编号进行分组,要求显示员工的部门编号,平均工资:avg(sal),
查询条件是平均工资大于30号部门的最高工资
select deptno,avg(sal)
from emp
group by deptmo
having avg(sal)>(select max(sal)
from emp
where deptno=30);
-------------------------------------------------------------------
在from子句中进行子查询
把子查询看成一张新表
select * from(select enpno,ename,sal,from emp)
例:
查询显示高于部门平均工资的雇员信息
select ename job,sal
from emp,(select deptno,avg(sal) avgsal from emp group by deptno) dept
where emp,deptno = dept.deptno and sal>avgsal;