Sql子查询

 

SQL子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。子查询可以嵌入以下SQL子句中：Where子句、having子句和from子句。

例：查询工资比编号为7566雇员工资高的雇员姓名

Select ename from emp

where sal >

(select sal from emp where empno = 7566)

Order by ename;

说明：（1）子查询要用括号括起来；

      （2）将子查询放在比较运算符的右边

      （3）不要再子查询中使用order by子句，select语句中只能有一个order by子句，并且它只能是主select语句的最后一个子句。但是如果有top n的话，可以有order by。（例如分页查询中：select top 5 * from emp where empno in (select Top(40) empno from emp order by empno desc)）order by empno)

1、单行子查询

内部select语句只返回一行结果的查询（单列）。主查询的where子句使用单行子查询返回结果要采用单行比较运算符（=、>、>=、<、<=、<>）。

1.1Where子句中使用单行子查询

例：显示和雇员scott同部门的雇员姓名、工资和部门编号。

select ename,sal,deptno

from emp

where depot=

(select deptno from emp where ename=”SCOTT”);

练习：显示和雇员scott从事相同工作，并且工资大于JAMES的雇员姓名、工作和工资。

Select ename, job, sal

From emp

Where job=

(select job from emp where ename=”SCOTT”)

And sal >

    (select sal from emp where ename = “JAMES”)

1.2单行子查询中使用组函数

例：显示工资最低的雇员姓名、工作和工资

Select ename,job,sal

From emp

Where sal = (select min(sal) from emp)

练习1：显示工资最高的雇员姓名、工作和工资

Select ename, job, sal

Where sal = (select max(sal) from emp)

练习2：显示工资高于平均工资的雇员姓名、工作、工资和工资等级。

Select e.ename as 姓名

       e.job as 工作

       e.grade as 工资等级

from emp e, salgrade s

where e.sal > (select avg(sal) from emp)

and e.sal between s.losal and s. hisal

1.3having子句中使用单行子查询

例：显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资

（1）    按部门显示部门编号、部门最低工资

Select deptno as 部门编号，

      min(sal) as 最低工资

from emp

group by deptno

（2）    查询20部门最低工资

Select min(sal) from emp where deptno=20

（3）    使用having子句把（2）作为（1）的子查询

Select deptno as 部门编号，

       min(sal) as 最低工资

from emp

group by deptno

having min（sal） >

      (select min (sal)

From emp where deptno = 20)

练习：查询平均工资最低的工种名称及其平均工资

（1）    按工种查询平均工资

Select  job, avg(sal) from emp group by job

（2）    按工种查询最低平均工资

Select min(avg(sal)) from emp group by job;

（3）    使用having子句把（2）作为（1）子查询

Select job, avg(sal) from emp group by job

having avg(sal) =

(select min(avg(sal)) from emp group by job)

2、多行子查询

内部select语句返回多行结果，主查询的Where子句使用多行子查询返回的结果要采用多行比较运算符，多行比较运算符可以一个或多个值进行比较。多行运算比较符：in、 any、all.

2.1使用in运算符的多行子查询

例1：查询有下属的雇员姓名、工作、工资和部门号。

Select ename,job,sal,deptno from emp where empno in (select mgr from emp)

 查询没有下属的

Select ename,job,sal,deptno from emp where empno in (select mgr from emp where mgr not null) 其中如果子查询中含有空值则不能使用not in

例2：查询各部门中工资最低的员工姓名、工作、工资和部门号

Select ename，job，sal，deptno from emp

 Where sal in (select min(sal) fom emp group by deptno)

练1：查询部门中工资最高的雇员姓名、工作、工资和部门号

Select ename,job,sal, deptno

From emp

Where sal in (select max(sal)

From emp group by deptno)

练习2：查询与销售部门（SALES）工作相同的其它部门雇员姓名、工资和部门名称

Select e.ename,e.job,e.sal,d.dname

Where e.deptno = d.deptno

       and dname <>’SALES’

and job in (select distinct e.job from emp e, dept d where e.deptno = d.deptno and d.dname=’SALES’)

2.2使用any的多行子查询

Any运算符将和内部查询返回的结果逐个比较，与单行操作符配合使用

<any :表示比子查询返回结果中的最大值小

=any:表示可以是子查询返回结果中的任意一个值

>any:表示比子查询返回结果中的最小值大。

例1：查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。

Select empno，ename,job,sal

From emp

Where sal <any

       (select sal from emop

Where job =’CLERK’)

 And job<>’CLERK’

例2：查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。

Select empno，ename,job,sal

From emp

Where sal >any

       (select sal from emop

Where job =’CLERK’)

 And job<>’CLERK’

练习1：查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资和部门编号。

Select ename,job,sal,deptno

From emp

Where sal >any

(select sal

from emp

where deptno=30

)and deptno <>30;

练习2：查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资、部门编号和部门名称。

Select e.ename,e.job,e.sal,d.deptno,d.dname

From emp e,dept d

Where e.deptno = d.deptno

     And d.dname <>’SALES’

     And sal <any

            (select distinct e.sal from emp e, dept d

Where e.deptno=d.deptno and d.dname=’SALES’)

2.3使用all运算符的多行子查询

All运算符将和内部查询返回的每个结果比较

>all:比最大的大

<all:比最小的小

例1：查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号。

Select ename,job,sal,deptno

From emp

Where sal >all

              (select avg(sal)

From emp group by deptno

)

例2：查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号。

Select ename,job,sal,deptno

From emp

Where sal <all

              (select avg(sal)

From emp group by deptno

)

练习1：查询工资高于部门编号为30的部门内所欲员工工资的雇员姓名、工作、工资和部门编号

Select ename,job,sal,deptno

From emp

Where sal >all(select sal from emp where deptno=30)

练习2：查询工资等级为4的雇员姓名、、工作、工资、部门编号和工资等级，同事满足该雇员工资高于部门编号为30的部门内所有员工工资

Select e.ename，e.job,e.sal,e.deptno,s.grade

From emp e,salgrade s

Where s.grade = 4

And e.sal between s.losal and s.hisal

And e.sal >all(select sal from emp where deptno = 30)

3.多列子查询

多列子查询分会多列结果的内部select语句，多列子查询中的列比较有成对比较与不成对比较两种方法。

多列子查询分为成对比较多列子查询和飞成对比较多列子查询。

对emp表的数据进行修改

Update emp set sal =1600,comm=300where ename =’SMITH’

Update emp set sal = 1500, comm=300 where ename =’CLARK’;

3.1成对比较多列子查询

例：查询部门编号为30的部门中任意一个雇员的工资和奖金完全相同的雇员姓名、工资、奖金、部门编号，满足该雇员不是来自30号部门。

（1）    查询30部门内雇员工资和奖金。

Select sal nvl(comm, -1) from emp where deptno = 30

（2）    查询非30部门内雇员姓名工资、奖金和部门编号

Select sal, nvl(comm,-1),deptno from where deptno<>30

（3）    把（1）作为（2）的子查询

查询（2）中与查询（1）中工资和奖金完全匹配的只有SMITH一个雇员，下面找出该员工。

Select ename,sal,nvl(comm,-1),deptno

From emp

Where deptno<>30

       And(sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from emp where deptno = 30);

3.2非成对比较多列子查询

例：查询工资与30部门中任意一个雇员的工资相等，同事奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门

Select ename,sal,nvl(comm,-1),deptno

From emp

Where deptno <>30

       And
sal in (select sal from emp where deptno = 30)

       And nvl(comm, -1) in (select nvl(comm, -1) from emp where deptno = 30)

4，相关子查询（exists）

相关子查询指需要引用主查询列表的子查询语句，通过exists谓词实现。对主查询的每条记录都需执行一次子查询来测试是否匹配。若子查询返回结果为空，则主查询的Where子句返回值为true，否则返回值为false。

例：查询在纽约（NEW YORK）工作的雇员姓名、工种、工资和奖金。

Select ename,job,sal,comm

From emp

Where exists ( select *

              From dept

              Where emp.deptno = deptno and loc = ‘NEW YORK’);

4、1from子句中使用子查询

在from子句中使用子查询时，必须给子查询指定别名。

例：显示工资高于部门平均工资的雇员姓名、工作、工资和部门号

Select ename,job,sal,emp.deptno

From dmp,(select deptno,avg(sal) avgsal

From emp

Group by deptno) s

Where emp.deptno = s.deptno and sal > s.avgsal