Oracle中的子查询

 

                              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);