Oracle中的子查询
(执行顺序先从最内层往外层查,可以替代任意的表达式,可以出现在select、where、having后面,注:from后也可出现子查询,这里不做介绍如:select * from (select rownum num ,s_emp.* from s_emp )where num between 11 and 20;)
子查询,就是可以嵌在sql语句中的select语句。
在select语句中嵌套子查询时,会先执行子查询,返回结果再执行主查询。一般的会将子查询放在运算符的右边。
注意:在使用子查询时,要注意这个运算符是单行的(也就是只能是单值)比如 “ = “要确认返回的是单行,还是多行运算符(范围,多值,比如in)。配合使用子查询返回的结果必须符合运算符的用法。
在使用子查询执行操作时,需要遵循下面的一些原则:
l子查询必须使用括号括起来,否则无法判断子查询语句的开始和结束。
l子查询只能出现在select,where,having的右端。
l不能在子查询语句中包含order by子句,而外查询语句可以有一个order by子句。
l子查询可以嵌套多层。
l在子查询中可以使用两种比较运算符:单行运算符和多行运算符。
子查询分类:
分三种类型:单行子查询,多行子查询,多列子查询。
l单行子查询:子查询语句只返回单行单列的结果,即返回一个常量值。
1. 单行子查询可以使用单行比较运算符:等于(=),不等于(!=或<>或^=),大于,小于,大于等于(>=),小于等于(<=)
2. 如果单行子查询中返回多行数据,那么这个子查询就是错误的,不能正确的执行。
例1:查询平均工资低于32部门的部门id
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)<(select avg(salary) from s_emp where dept_id=32);
例2:查询s_emp表中工资最低的员工
select last_name,salary from s_emp where salary=(select min(salary) from s_emp );
例3:求谁和Smith是做一样工作的
select ename from emp where job=(select job from emp where lower(ename)='smith');
l多行子查询:子查询语句返回多行单列的结果,即返回一系列值。
1. 多行子查询可以使用多行运算符:in(等于列表中的任何一个值),any(与子查询返回的每一个值进行比较),all(与子查询返回的所有值进行比较)。
2. any运算符表示与子查询中的每一个值进行比较。这时,需要把单行比较运算符与该运算符组合起来使用,并且返回行只需匹配子查询的任一个结果即可。与单行比较运算符组合之后的any运算符的结果是如下:
(1) <any,表示小于最大值。
(2) =any,与in运算符等价。
(3) >any,表示大于最小值。
3. all表示与子查询所有值进行比较这时,需要把单行比较运算符与该运算符组合起来使用。与单行比较运算符组合之后的all运算符的结果是如下:
(1) <all,表示小于最小值。
(2) >all,表示大于最大值。
注意:all和any操作符不能单独使用,而只能与单行比较符(=,< ,>,<=,>=,<>,)结合使用。
举例:
一:在多行子查询中使用in操作符举例
例1:求哪些人是经理
select last_name ,title from s_emp where id in (select manager_id from s_emp)
例2:*求哪些人是普通员工
select first_name from s_emp where id not in (select manager_id from s_emp
where manager_id is not null)
例3:查询和42部门员工职位相同的所有员工的姓名
SELECT TITLE,LAST_NAME||' '||FIRST_NAME FROM S_EMP WHERE TITLE IN(SELECT TITLE FROM S_EMP WHERE DEPT_ID=42)
例4:显示匹配于10岗位的雇员名,岗位,工资,部门名。
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);
二:在多行子查询中使用any操作符举例
例1:显示工资高于部门30的任意雇员工资的雇员名,工资和部门号。
select e name,sal,deptno from emp where sal >any (select sal from emp where deptno=30);
三:在多行子查询中使用all操作符举例
例1:显示高于部门30的所有雇员工资的雇员名,工资和部门号。
select e name,sal,deptno from emp where sal >all (select sal from emp where deptno=30);
l多列子查询:子查询语句返回多列的结果。
多列子查询的语法形式如下:
Select column,column,……from table where(column,column…..)in(select column,colunm……from table where condition)
说明1:单行子查询是指子查询只返回单列单行数据,多行子查询是指子查询返回单列多行数据,二者都是针对单列而言的。而多列子查询则是指返回多列数据的子查询语句。当多列子查询返回单行数据时,在where子句中可以使用单行比较符;当多列子查询返回多行数据时,在where子句中必须使用多行比较符(in,any,all) 。
说明2:在使用子查询比较多个列的数据时,既可以使用成对的比较,也可以使用非成对的比较。其中,成对比较要求多个列的数据必须同时匹配;而非成对比较则不要求多个列的数据同时匹配。它们的区别在于如图所示:
举例1:成对比较
例1:显示与smith部门和岗位完全相同的所有雇员
select * from emp where (deptno, job)=(select deptno,job from emp where lower(ename)='smith');
例2:显示工资和补助与部门30雇员的工资和补助完全匹配的所有雇员
select * from emp where (sal,nvl(comm,0)) in (select sal,nvl(comm,0) from emp where deptno=30);
举例2:非成对比较(执行非成对比较时,应该要使用多个多行子查询来实现)
例1:显示工资匹配于部门30工资列表,补助匹配于部门30补助列表的所有雇员
select * from emp where sal in (select sal from emp where deptno=30)and nvl(comm,0) in (select nvl(comm,0)from emp where deptno=30);