SQL子查询
子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。
子查询可以嵌入以下SQL子句中：where子句、having子句和from子句。
例：查询工资比编号为7566雇员工资高的雇员姓名。
SQL>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.1 Where子句中使用单行子查询
例：显示和雇员scott同部门的雇员姓名、工资和部门编号。
SQL>select ename,sal,deptno
from emp
where deptno=
(select deptno
from emp
where ename='SCOTT');
ENAME SAL DEPTNO
---------- --------- ------
SMITH 800.00 20
JONES 2975.00 20
SCOTT 3000.00 20
ADAMS 1100.00 20
FORD 3000.00 20
练习：显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作和工资。
SQL>select ename,job,sal
from emp
where job=
(select job from emp where ename='SCOTT')
and sal>
(select sal from emp where ename='JAMES');
ENAME JOB SAL
---------- --------- ---------
SCOTT ANALYST 3000.00
FORD ANALYST 3000.00
1.2单行子查询中使用组函数
例：显示工资最低的雇员姓名、工作和工资。
SQL> select ename,job,sal
from emp
where sal=(select min(sal) from emp);
ENAME JOB SAL
---------- --------- ---------
SMITH CLERK 800.00
练习1：显示工资最高的雇员姓名、工作和工资。
练习2：显示工资高于平均工资的雇员姓名、工作、工资和工资等级。
SQL>select e.ename as 姓名,
e.job as 工作,
e.sal as 工资,
s.grade as 工资等级
from emp e,salgrade s
where e.sal>(select avg(sal) from emp)
and e.sal between s.losal and s.hisal;
姓名 工作 工资 工资等级
---------- --------- --------- ----------
JONES MANAGER 2975.00 4
BLAKE MANAGER 2850.00 4
CLARK MANAGER 2450.00 4
SCOTT ANALYST 3000.00 4
KING PRESIDENT 5000.00 5
FORD ANALYST 3000.00 4
思考？
e.sal>(select avg(sal) from emp)和e.sal between s.losal and s.hisal顺序对调查询效率上有何差异？为什么？
1.3 having子句中使用单行子查询
例：显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资。
（1）按部门显示部门编号、部门最低工资
SQL>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）的子查询
SQL>select deptno as 部门编号,
min(sal) as 最低工资
from emp
group by deptno
having min(sal)>(select min(sal)
from emp
where deptno=20);
部门编号 最低工资
-------- ----------
30 950
10 1300
练习：查询平均工资最低的工种名称及其平均工资。
（1）按工种查询平均工资
SQL>select job,avg(sal) from emp group by job;
（2）按工种查询最低平均工资
SQL>select min(avg(sal)) from emp group by job;
（3）使用having子句把（2）作为（1）子查询
SQL>select job,avg(sal)
from emp
group by job
having avg(sal)=(select min(avg(sal))
from emp
group by job);
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
问题思考：
（1）当单行子查询返回的结果为null时，主查询是否正确？
例：查询和SMITH从事相同工作的雇员姓名和工作
select ename,job
from emp
where job=(select job
from emp
where ename='SMITHS');
如果SMITH误拼写成SMITHS则返回结果为null。
（2）子查询中使用group by子句，主查询中是否可以使用单行比较符？
例：下面的SQL语句能正确执行吗？
SQL>select ename,job
from emp
where sal=(select min(sal)
from emp
group by deptno)
ORA-01427: 单行子查询返回多个行
2、多行子查询
内部select语句返回多行结果，主查询的where子句使用多行子查询返回的结果要采用多行比较运算符，多行比较运算符可以和一个或多个值进行比较。
多行运算比较符：in、any、all
2.1 使用in运算符的多行子查询
In运算符将等于列表中的任意一项。
例1：查询有下属的雇员姓名、工作、工资和部门号。
SQL>select ename,job,sal,deptno
from emp
where empno in (select mgr from emp);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
JONES MANAGER 2975.00 20
BLAKE MANAGER 2850.00 30
CLARK MANAGER 1500.00 10
SCOTT ANALYST 3000.00 20
KING PRESIDENT 5000.00 10
FORD ANALYST 3000.00 20
思考？
如果要查询没有下属的雇员姓名、工作、工资和部门号，如下的SQL语句是否可以获得预期的结果？如果不能，应如何修改？
SQL>select ename,job,sal,deptno
from emp
where empno not in (select mgr from emp);
解答：
为把问题说清楚，先看一下子查询的执行结果：
SQL> select ename,mgr from emp;
ENAME MGR
---------- -----
SMITH 7902
ALLEN 7698
WARD 7698
JONES 7839
MARTIN 7698
BLAKE 7839
CLARK 7839
SCOTT 7566
KING
TURNER 7698
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
子查询返回的结果中有一个mgr是空值，not in运算符将会用主查询条件（empno）与子查询中的每个结果（mgr）进行逻辑非的比较。因为子查询返回结果中有条空值，任何条件和空值比较都是空值。因此只要空值成为子查询的一部分，就不能用not in运算符。
SQL语句更正如下：
SQL>select ename,job,sal,deptno
from emp
where empno not in (select nvl(mgr,-1) from emp);
或
SQL>select ename,job,sal,deptno
from emp
where empno not in (select mgr from emp where mgr is not null);
例2：查询各部门中工资最低的员工姓名、工作、工资和部门号
SQL>select ename,job,sal,deptno
from emp
where sal in (select min(sal)
from emp
group by deptno);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
JAMES CLERK 950.00 30
SMITH CLERK 800.00 20
MILLER CLERK 1300.00 10
练习1：查询部门中工资最高的雇员姓名、工作、工资和部门号。
SQL>select ename,job,sal,deptno
from emp
where sal in (select max(sal)
from emp
group by deptno);
练习2：查询与销售部门（SALES）工作相同的其它部门雇员姓名、工作、工资和部门名称。
SQL>select e.ename,e.job,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno
and d.dname<>'SALES'
and job in (select distinct e.job
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES');
ENAME JOB SAL DNAME
---------- --------- --------- --------------
CLARK MANAGER 2450.00 ACCOUNTING
JONES MANAGER 2975.00 RESEARCH
MILLER CLERK 1300.00 ACCOUNTING
ADAMS CLERK 1100.00 RESEARCH
SMITH CLERK 800.00 RESEARCH
2.2 使用any运算符的多行子查询
Any运算符将和内部查询返回的结果逐个比较，与单行操作符配合使用。
<any：表示比子查询返回结果中的最大值小；
=any：表示可以是子查询返回结果中的任意一个值；
>any：表示比子查询返回结果中的最小值大。
例1：查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。
SQL>select empno,ename,job,sal
from emp
where sal<any
(select sal
from emp
where job='CLERK')
and job<>'CLERK';
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7521 WARD SALESMAN 1250.00
7654 MARTIN SALESMAN 1250.00
例2：查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。
SQL>select empno,ename,job,sal
from emp
where sal>any
(select sal
from emp
where job='CLERK')
and job<>'CLERK';
EMPNO ENAME JOB SAL
----- ---------- --------- ---------
7839 KING PRESIDENT 5000.00
7788 SCOTT ANALYST 3000.00
7902 FORD ANALYST 3000.00
7566 JONES MANAGER 2975.00
7698 BLAKE MANAGER 2850.00
7782 CLARK MANAGER 2450.00
7499 ALLEN SALESMAN 1600.00
7844 TURNER SALESMAN 1500.00
7521 WARD SALESMAN 1250.00
7654 MARTIN SALESMAN 1250.00
练习1：查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资和部门编号。
SQL>select ename,job,sal,deptno
from emp
where sal>any
(select sal
from emp
where deptno=30)
and deptno<>30;
练习2：查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资、部门编号和部门名称。
SQL>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');
ENAME JOB SAL DEPTNO DNAME
---------- --------- --------- ------ --------------
SMITH CLERK 800.00 20 RESEARCH
ADAMS CLERK 1100.00 20 RESEARCH
MILLER CLERK 1300.00 10 ACCOUNTING
CLARK MANAGER 2450.00 10 ACCOUNTING
2.3 使用all运算符的多行子查询
All运算符将和内部查询返回的每个结果比较。
>all：比最大的大；
<all：比最小的小。
例1：查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号。
SQL>select ename,job,sal,deptno
from emp
where sal>all (select avg(sal)
from emp
group by deptno);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
JONES MANAGER 2975.00 20
SCOTT ANALYST 3000.00 20
KING PRESIDENT 5000.00 10
FORD ANALYST 3000.00 20
例2：查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号。
SQL>select ename,job,sal,deptno
from emp
where sal<all (select avg(sal)
from emp
group by deptno);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
SMITH CLERK 800.00 20
WARD SALESMAN 1250.00 30
MARTIN SALESMAN 1250.00 30
TURNER SALESMAN 1500.00 30
ADAMS CLERK 1100.00 20
JAMES CLERK 950.00 30
MILLER CLERK 1300.00 10
练习1：查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号
select ename,job,sal,deptno
from emp
where sal>all (select sal from emp where deptno=30);
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
JONES MANAGER 2975.00 20
SCOTT ANALYST 3000.00 20
KING PRESIDENT 5000.00 10
FORD ANALYST 3000.00 20
练习2：查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资。
SQL>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);
ENAME JOB SAL DEPTNO GRADE
---------- --------- --------- ------ ----------
JONES MANAGER 2975.00 20 4
SCOTT ANALYST 3000.00 20 4
FORD ANALYST 3000.00 20 4
3、多列子查询
多列子查询返回多列结果的内部select语句，多列子查询中的列比较有成对比较与不成对比较两种方法。
多列子查询分为成对比较多列子查询和非成对比较多列子查询。
对emp表的数据进行修改：
SQL> update emp set sal=1600,comm=300 where ename='SMITH';
SQL> update emp set sal=1500,comm=300 where ename='CLARK';
3.1成对比较多列子查询
例：查询与部门编号为30的部门中任意一个雇员的工资和奖金完全相同的雇员姓名、工资、奖金、部门编号，满足该雇员不是来自30号部门。
（1）查询30部门内雇员工资和奖金
SQL>select sal,nvl(comm,-1) from emp where deptno=30;
SAL NVL(COMM,-1)
--------- ------------
1600.00 300
1250.00 500
1250.00 1400
2850.00 -1
1500.00 0
950.00 -1
（2）查询非30部门内雇员姓名、工资、奖金和部门编号
SQL>select ename,sal,nvl(comm,-1),deptno from emp where deptno<>30;
ENAME SAL NVL(COMM,-1) DEPTNO
---------- --------- ------------ ------
SMITH 1600.00 300 20
JONES 2975.00 -1 20
CLARK 1500.00 300 10
SCOTT 3000.00 -1 20
KING 5000.00 -1 10
ADAMS 1100.00 -1 20
FORD 3000.00 -1 20
MILLER 1300.00 -1 10
（3）把（1）作为（2）的子查询
查询（2）中与查询（1）中工资和奖金完全匹配的只有SMITH一个雇员，下面找出该员工。
SQL>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);
ENAME SAL NVL(COMM,-1) DEPTNO
---------- --------- ------------ ------
SMITH 1600.00 300 20
练习1：创建一查询，显示能获得与SCOTT一样工资和奖金的其他雇员的姓名、受雇日期和工资。
练习2：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号。
select e.ename,e.job,e.sal,s.grade,e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal
and (s.grade,e.deptno) in (
select max(s.grade),e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by e.deptno)
order by e.deptno;
ENAME JOB SAL GRADE DEPTNO
---------- --------- --------- ---------- ------
KING PRESIDENT 5000.00 5 10
FORD ANALYST 3000.00 4 20
JONES MANAGER 2975.00 4 20
SCOTT ANALYST 3000.00 4 20
BLAKE MANAGER 2850.00 4 30
练习3：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称。
select e.ename,e.job,e.sal,s.grade,d.dname
from emp e,salgrade s,dept d
where e.sal between s.losal and s.hisal
and e.deptno=d.deptno
and (s.grade,e.deptno) in (select max(s.grade),e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by e.deptno)
order by e.deptno;
ENAME JOB SAL GRADE DNAME
---------- --------- --------- ---------- --------------
KING PRESIDENT 5000.00 5 ACCOUNTING
FORD ANALYST 3000.00 4 RESEARCH
JONES MANAGER 2975.00 4 RESEARCH
SCOTT ANALYST 3000.00 4 RESEARCH
BLAKE MANAGER 2850.00 4 SALES
3.2非成对比较多列子查询
例：查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门。
SQL>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);
ENAME SAL NVL(COMM,-1) DEPTNO
---------- --------- ------------ ------
SMITH 1600.00 300 20
CLARK 1500.00 300 10
练习1：查询工资与销售部门（SALES）中任意一个雇员的工资相等，同时奖金也与该部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号、部门名称，但该雇员不是来自销售部门。
SQL>select e.ename,e.sal,nvl(e.comm,-1),d.deptno,d.dname
from emp e,dept d
where (e.deptno=d.deptno) and d.dname<>'SALES'
and e.sal in
(select e.sal
from emp e,dept d
where (e.deptno=d.deptno) and d.dname='SALES')
and nvl(e.comm,-1) in
(select nvl(e.comm,-1)
from emp e,dept d
where (e.deptno=d.deptno) and d.dname='SALES');
ENAME SAL NVL(E.COMM,-1) DEPTNO DNAME
---------- --------- -------------- ------ --------------
CLARK 1500.00 300 10 ACCOUNTING
SMITH 1600.00 300 20 RESEARCH
练习2：显示与工作在DALLAS的雇员的工资及奖金同时匹配的雇员姓名、部门名称及工资。
3、相关子查询（exists）
相关子查询指需要引用主查询列表的子查询语句，通过exists谓词实现。对主查询的每条记录都需执行一次子查询来测试是否匹配。若子查询返回结果非空，则主查询的where子句返回值为true，否则返回值为false。
例：查询在纽约（NEW YORK）工作的雇员姓名、工种、工资和奖金。
SQL>select ename,job,sal,comm
from emp
where exists (select *
from dept
where emp.deptno=deptno and loc='NEW YORK');
ENAME JOB SAL COMM
---------- --------- --------- ---------
CLARK MANAGER 1500.00 300.00
KING PRESIDENT 5000.00
MILLER CLERK 1300.00
练习：查询工资等级为4的雇员姓名、工种和工资
SQL>select ename,job,sal
from emp
where exists (select *
from salgrade
where emp.sal between losal and hisal
and grade=4);
ENAME JOB SAL
---------- --------- ---------
FORD ANALYST 3000.00
SCOTT ANALYST 3000.00
JONES MANAGER 2975.00
BLAKE MANAGER 2850.00
4、from子句中使用子查询
在from子句中使用子查询时，必须给子查询指定别名。
例：显示工资高于部门平均工资的雇员姓名、工作、工资和部门号。
SQL>select ename,job,sal,emp.deptno
from emp, (select deptno,avg(sal) avgsal
from emp
group by deptno)s
where emp.deptno=s.deptno and sal>s.avgsal;
ENAME JOB SAL DEPTNO
---------- --------- --------- ------
ALLEN SALESMAN 1600.00 30
JONES MANAGER 2975.00 20
BLAKE MANAGER 2850.00 30
SCOTT ANALYST 3000.00 20
KING PRESIDENT 5000.00 10
FORD ANALYST 3000.00 20
练习：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号。
方法一、from子句中使用一个子查询
select e.ename,e.job,e.sal,s.grade,e.deptno
from emp e,
salgrade s,
(select max(s.grade) grade,e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by e.deptno)q
where e.sal between s.losal and s.hisal
and e.deptno=q.deptno and s.grade=q.grade
order by e.deptno;
ENAME JOB SAL GRADE DEPTNO
---------- --------- --------- ---------- ------
KING PRESIDENT 5000.00 5 10
JONES MANAGER 2975.00 4 20
SCOTT ANALYST 3000.00 4 20
FORD ANALYST 3000.00 4 20
BLAKE MANAGER 2850.00 4 30
方法二、from子句中使用两个子查询
select p.ename,p.job,p.sal,p.grade,p.deptno
from (
select e.ename,e.job,e.sal,s.grade,e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal)p,
(
select max(s.grade) grade,e.deptno
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by e.deptno)q
where p.deptno=q.deptno and p.grade=q.grade
order by p.deptno;
