SQL 子查询
SQL子查询
子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。
子查询可以嵌入以下SQL子句中：where子句、having子句和from子句。
例：查询工资比编号为7566雇员工资高的雇员姓名。
SQL>selectename
fromemp
wheresal>
(selectsal
fromemp
whereempno=7566)
orderbyename;
说明：
（1）子查询要用括号括起来；
（2）将子查询放在比较运算符的右边；
（3）不要在子查询中使用orderby子句，select语句中只能有一个orderby子句，并且它只能是主select语句的最后一个子句。
1、单行子查询
内部select语句只返回一行结果的查询（单列）。主查询的where子句使用单行子查询返回结果要采用单行比较运算符（=、>、>=、<、<=、<>）。
1.1Where子句中使用单行子查询
例：显示和雇员scott同部门的雇员姓名、工资和部门编号。
SQL>selectename,sal,deptno
fromemp
wheredeptno=
(selectdeptno
fromemp
whereename='SCOTT');
ENAMESALDEPTNO
-------------------------
SMITH800.0020
JONES2975.0020
SCOTT3000.0020
ADAMS1100.0020
FORD3000.0020
练习：显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作和工资。
SQL>selectename,job,sal
fromemp
wherejob=
(selectjobfromempwhereename='SCOTT')
andsal>
(selectsalfromempwhereename='JAMES');
ENAMEJOBSAL
----------------------------
SCOTTANALYST3000.00
FORDANALYST3000.00
1.2单行子查询中使用组函数
例：显示工资最低的雇员姓名、工作和工资。
SQL>selectename,job,sal
fromemp
wheresal=(selectmin(sal)fromemp);
ENAMEJOBSAL
----------------------------
SMITHCLERK800.00
练习1：显示工资最高的雇员姓名、工作和工资。
练习2：显示工资高于平均工资的雇员姓名、工作、工资和工资等级。
SQL>selecte.enameas姓名,
e.jobas工作,
e.salas工资,
s.gradeas工资等级
fromempe,salgrades
wheree.sal>(selectavg(sal)fromemp)
ande.salbetweens.losalands.hisal;
姓名工作工资工资等级
--------------------------------------
JONESMANAGER2975.004
BLAKEMANAGER2850.004
CLARKMANAGER2450.004
SCOTTANALYST3000.004
KINGPRESIDENT5000.005
FORDANALYST3000.004
思考？
e.sal>(selectavg(sal)fromemp)和e.salbetweens.losalands.hisal顺序对调查询效率上有何差异？为什么？
1.3having子句中使用单行子查询
例：显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资。
（1）按部门显示部门编号、部门最低工资
SQL>selectdeptnoas部门编号,
min(sal)as最低工资
fromemp
groupbydeptno;
（2）查询20部门最低工资
selectmin(sal)fromempwheredeptno=20
（3）使用having子句把（2）作为（1）的子查询
SQL>selectdeptnoas部门编号,
min(sal)as最低工资
fromemp
groupbydeptno
havingmin(sal)>(selectmin(sal)
fromemp
wheredeptno=20);
部门编号最低工资
------------------
30950
101300
练习：查询平均工资最低的工种名称及其平均工资。
（1）按工种查询平均工资
SQL>selectjob,avg(sal)fromempgroupbyjob;
（2）按工种查询最低平均工资
SQL>selectmin(avg(sal))fromempgroupbyjob;
（3）使用having子句把（2）作为（1）子查询
SQL>selectjob,avg(sal)
fromemp
groupbyjob
havingavg(sal)=(selectmin(avg(sal))
fromemp
groupbyjob);
JOBAVG(SAL)
-------------------
CLERK1037.5
问题思考：
（1）当单行子查询返回的结果为null时，主查询是否正确？
例：查询和SMITH从事相同工作的雇员姓名和工作
selectename,job
fromemp
wherejob=(selectjob
fromemp
whereename='SMITHS');
如果SMITH误拼写成SMITHS则返回结果为null。
（2）子查询中使用groupby子句，主查询中是否可以使用单行比较符？
例：下面的SQL语句能正确执行吗？
SQL>selectename,job
fromemp
wheresal=(selectmin(sal)
fromemp
groupbydeptno)
ORA-01427:单行子查询返回多个行
2、多行子查询
内部select语句返回多行结果，主查询的where子句使用多行子查询返回的结果要采用多行比较运算符，多行比较运算符可以和一个或多个值进行比较。
多行运算比较符：in、any、all
2.1使用in运算符的多行子查询
In运算符将等于列表中的任意一项。
例1：查询有下属的雇员姓名、工作、工资和部门号。
SQL>selectename,job,sal,deptno
fromemp
whereempnoin(selectmgrfromemp);
ENAMEJOBSALDEPTNO
----------------------------------
JONESMANAGER2975.0020
BLAKEMANAGER2850.0030
CLARKMANAGER1500.0010
SCOTTANALYST3000.0020
KINGPRESIDENT5000.0010
FORDANALYST3000.0020
思考？
如果要查询没有下属的雇员姓名、工作、工资和部门号，如下的SQL语句是否可以获得预期的结果？如果不能，应如何修改？
SQL>selectename,job,sal,deptno
fromemp
whereempnonotin(selectmgrfromemp);
解答：
为把问题说清楚，先看一下子查询的执行结果：
SQL>selectename,mgrfromemp;
ENAMEMGR
---------------
SMITH7902
ALLEN7698
WARD7698
JONES7839
MARTIN7698
BLAKE7839
CLARK7839
SCOTT7566
KING
TURNER7698
ADAMS7788
JAMES7698
FORD7566
MILLER7782
子查询返回的结果中有一个mgr是空值，notin运算符将会用主查询条件（empno）与子查询中的每个结果（mgr）进行逻辑非的比较。因为子查询返回结果中有条空值，任何条件和空值比较都是空值。因此只要空值成为子查询的一部分，就不能用notin运算符。
SQL语句更正如下：
SQL>selectename,job,sal,deptno
fromemp
whereempnonotin(selectnvl(mgr,-1)fromemp);
或
SQL>selectename,job,sal,deptno
fromemp
whereempnonotin(selectmgrfromempwheremgrisnotnull);
例2：查询各部门中工资最低的员工姓名、工作、工资和部门号
SQL>selectename,job,sal,deptno
fromemp
wheresalin(selectmin(sal)
fromemp
groupbydeptno);
ENAMEJOBSALDEPTNO
----------------------------------
JAMESCLERK950.0030
SMITHCLERK800.0020
MILLERCLERK1300.0010
练习1：查询部门中工资最高的雇员姓名、工作、工资和部门号。
SQL>selectename,job,sal,deptno
fromemp
wheresalin(selectmax(sal)
fromemp
groupbydeptno);
练习2：查询与销售部门（SALES）工作相同的其它部门雇员姓名、工作、工资和部门名称。
SQL>selecte.ename,e.job,e.sal,d.dname
fromempe,deptd
wheree.deptno=d.deptno
andd.dname<>'SALES'
andjobin(selectdistincte.job
fromempe,deptd
wheree.deptno=d.deptnoandd.dname='SALES');
ENAMEJOBSALDNAME
------------------------------------------
CLARKMANAGER2450.00ACCOUNTING
JONESMANAGER2975.00RESEARCH
MILLERCLERK1300.00ACCOUNTING
ADAMSCLERK1100.00RESEARCH
SMITHCLERK800.00RESEARCH
2.2使用any运算符的多行子查询
Any运算符将和内部查询返回的结果逐个比较，与单行操作符配合使用。
<any：表示比子查询返回结果中的最大值小；
=any：表示可以是子查询返回结果中的任意一个值；
>any：表示比子查询返回结果中的最小值大。
例1：查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。
SQL>selectempno,ename,job,sal
fromemp
wheresal<any
(selectsal
fromemp
wherejob='CLERK')
andjob<>'CLERK';
EMPNOENAMEJOBSAL
---------------------------------
7521WARDSALESMAN1250.00
7654MARTINSALESMAN1250.00
例2：查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的雇员编号、姓名、工种和工资。
SQL>selectempno,ename,job,sal
fromemp
wheresal>any
(selectsal
fromemp
wherejob='CLERK')
andjob<>'CLERK';
EMPNOENAMEJOBSAL
---------------------------------
7839KINGPRESIDENT5000.00
7788SCOTTANALYST3000.00
7902FORDANALYST3000.00
7566JONESMANAGER2975.00
7698BLAKEMANAGER2850.00
7782CLARKMANAGER2450.00
7499ALLENSALESMAN1600.00
7844TURNERSALESMAN1500.00
7521WARDSALESMAN1250.00
7654MARTINSALESMAN1250.00
练习1：查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资和部门编号。
SQL>selectename,job,sal,deptno
fromemp
wheresal>any
(selectsal
fromemp
wheredeptno=30)
anddeptno<>30;
练习2：查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的雇员姓名、工种、工资、部门编号和部门名称。
SQL>selecte.ename,e.job,e.sal,d.deptno,d.dname
fromempe,deptd
wheree.deptno=d.deptno
andd.dname<>'SALES'
andsal<any
(selectdistincte.sal
fromempe,deptd
wheree.deptno=d.deptnoandd.dname='SALES');
ENAMEJOBSALDEPTNODNAME
------------------------------------------------
SMITHCLERK800.0020RESEARCH
ADAMSCLERK1100.0020RESEARCH
MILLERCLERK1300.0010ACCOUNTING
CLARKMANAGER2450.0010ACCOUNTING
2.3使用all运算符的多行子查询
All运算符将和内部查询返回的每个结果比较。
>all：比最大的大；
<all：比最小的小。
例1：查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号。
SQL>selectename,job,sal,deptno
fromemp
wheresal>all(selectavg(sal)
fromemp
groupbydeptno);
ENAMEJOBSALDEPTNO
----------------------------------
JONESMANAGER2975.0020
SCOTTANALYST3000.0020
KINGPRESIDENT5000.0010
FORDANALYST3000.0020
例2：查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号。
SQL>selectename,job,sal,deptno
fromemp
wheresal<all(selectavg(sal)
fromemp
groupbydeptno);
ENAMEJOBSALDEPTNO
----------------------------------
SMITHCLERK800.0020
WARDSALESMAN1250.0030
MARTINSALESMAN1250.0030
TURNERSALESMAN1500.0030
ADAMSCLERK1100.0020
JAMESCLERK950.0030
MILLERCLERK1300.0010
练习1：查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号
selectename,job,sal,deptno
fromemp
wheresal>all(selectsalfromempwheredeptno=30);
ENAMEJOBSALDEPTNO
----------------------------------
JONESMANAGER2975.0020
SCOTTANALYST3000.0020
KINGPRESIDENT5000.0010
FORDANALYST3000.0020
练习2：查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资。
SQL>selecte.ename,e.job,e.sal,e.deptno,s.grade
fromempe,salgrades
wheres.grade=4
ande.salbetweens.losalands.hisal
ande.sal>all(selectsalfromempwheredeptno=30);
ENAMEJOBSALDEPTNOGRADE
--------------------------------------------
JONESMANAGER2975.00204
SCOTTANALYST3000.00204
FORDANALYST3000.00204
3、多列子查询
多列子查询返回多列结果的内部select语句，多列子查询中的列比较有成对比较与不成对比较两种方法。
多列子查询分为成对比较多列子查询和非成对比较多列子查询。
对emp表的数据进行修改：
SQL>updateempsetsal=1600,comm=300whereename='SMITH';
SQL>updateempsetsal=1500,comm=300whereename='CLARK';
3.1成对比较多列子查询
例：查询与部门编号为30的部门中任意一个雇员的工资和奖金完全相同的雇员姓名、工资、奖金、部门编号，满足该雇员不是来自30号部门。
（1）查询30部门内雇员工资和奖金
SQL>selectsal,nvl(comm,-1)fromempwheredeptno=30;
SALNVL(COMM,-1)
---------------------
1600.00300
1250.00500
1250.001400
2850.00-1
1500.000
950.00-1
（2）查询非30部门内雇员姓名、工资、奖金和部门编号
SQL>selectename,sal,nvl(comm,-1),deptnofromempwheredeptno<>30;
ENAMESALNVL(COMM,-1)DEPTNO
-------------------------------------
SMITH1600.0030020
JONES2975.00-120
CLARK1500.0030010
SCOTT3000.00-120
KING5000.00-110
ADAMS1100.00-120
FORD3000.00-120
MILLER1300.00-110
（3）把（1）作为（2）的子查询
查询（2）中与查询（1）中工资和奖金完全匹配的只有SMITH一个雇员，下面找出该员工。
SQL>selectename,sal,nvl(comm,-1),deptno
fromemp
wheredeptno<>30
and(sal,nvl(comm,-1))in(selectsal,nvl(comm,-1)
fromemp
wheredeptno=30);
ENAMESALNVL(COMM,-1)DEPTNO
-------------------------------------
SMITH1600.0030020
练习1：创建一查询，显示能获得与SCOTT一样工资和奖金的其他雇员的姓名、受雇日期和工资。
练习2：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号。
selecte.ename,e.job,e.sal,s.grade,e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal
and(s.grade,e.deptno)in(
selectmax(s.grade),e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal
groupbye.deptno)
orderbye.deptno;
ENAMEJOBSALGRADEDEPTNO
--------------------------------------------
KINGPRESIDENT5000.00510
FORDANALYST3000.00420
JONESMANAGER2975.00420
SCOTTANALYST3000.00420
BLAKEMANAGER2850.00430
练习3：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称。
selecte.ename,e.job,e.sal,s.grade,d.dname
fromempe,salgrades,deptd
wheree.salbetweens.losalands.hisal
ande.deptno=d.deptno
and(s.grade,e.deptno)in(selectmax(s.grade),e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal
groupbye.deptno)
orderbye.deptno;
ENAMEJOBSALGRADEDNAME
----------------------------------------------------
KINGPRESIDENT5000.005ACCOUNTING
FORDANALYST3000.004RESEARCH
JONESMANAGER2975.004RESEARCH
SCOTTANALYST3000.004RESEARCH
BLAKEMANAGER2850.004SALES
3.2非成对比较多列子查询
例：查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门。
SQL>selectename,sal,nvl(comm,-1),deptno
fromemp
wheredeptno<>30
andsalin(selectsal
fromemp
wheredeptno=30)
andnvl(comm,-1)in(selectnvl(comm,-1)
fromemp
wheredeptno=30);
ENAMESALNVL(COMM,-1)DEPTNO
-------------------------------------
SMITH1600.0030020
CLARK1500.0030010
练习1：查询工资与销售部门（SALES）中任意一个雇员的工资相等，同时奖金也与该部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号、部门名称，但该雇员不是来自销售部门。
SQL>selecte.ename,e.sal,nvl(e.comm,-1),d.deptno,d.dname
fromempe,deptd
where(e.deptno=d.deptno)andd.dname<>'SALES'
ande.salin
(selecte.sal
fromempe,deptd
where(e.deptno=d.deptno)andd.dname='SALES')
andnvl(e.comm,-1)in
(selectnvl(e.comm,-1)
fromempe,deptd
where(e.deptno=d.deptno)andd.dname='SALES');
ENAMESALNVL(E.COMM,-1)DEPTNODNAME
-----------------------------------------------------
CLARK1500.0030010ACCOUNTING
SMITH1600.0030020RESEARCH
练习2：显示与工作在DALLAS的雇员的工资及奖金同时匹配的雇员姓名、部门名称及工资。
3、相关子查询（exists）
相关子查询指需要引用主查询列表的子查询语句，通过exists谓词实现。对主查询的每条记录都需执行一次子查询来测试是否匹配。若子查询返回结果非空，则主查询的where子句返回值为true，否则返回值为false。
例：查询在纽约（NEWYORK）工作的雇员姓名、工种、工资和奖金。
SQL>selectename,job,sal,comm
fromemp
whereexists(select*
fromdept
whereemp.deptno=deptnoandloc='NEWYORK');
ENAMEJOBSALCOMM
-------------------------------------
CLARKMANAGER1500.00300.00
KINGPRESIDENT5000.00
MILLERCLERK1300.00
练习：查询工资等级为4的雇员姓名、工种和工资
SQL>selectename,job,sal
fromemp
whereexists(select*
fromsalgrade
whereemp.salbetweenlosalandhisal
andgrade=4);
ENAMEJOBSAL
----------------------------
FORDANALYST3000.00
SCOTTANALYST3000.00
JONESMANAGER2975.00
BLAKEMANAGER2850.00
4、from子句中使用子查询
在from子句中使用子查询时，必须给子查询指定别名。
例：显示工资高于部门平均工资的雇员姓名、工作、工资和部门号。
SQL>selectename,job,sal,emp.deptno
fromemp,(selectdeptno,avg(sal)avgsal
fromemp
groupbydeptno)s
whereemp.deptno=s.deptnoandsal>s.avgsal;
ENAMEJOBSALDEPTNO
----------------------------------
ALLENSALESMAN1600.0030
JONESMANAGER2975.0020
BLAKEMANAGER2850.0030
SCOTTANALYST3000.0020
KINGPRESIDENT5000.0010
FORDANALYST3000.0020
练习：查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号。
方法一、from子句中使用一个子查询
selecte.ename,e.job,e.sal,s.grade,e.deptno
fromempe,
salgrades,
(selectmax(s.grade)grade,e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal
groupbye.deptno)q
wheree.salbetweens.losalands.hisal
ande.deptno=q.deptnoands.grade=q.grade
orderbye.deptno;
ENAMEJOBSALGRADEDEPTNO
--------------------------------------------
KINGPRESIDENT5000.00510
JONESMANAGER2975.00420
SCOTTANALYST3000.00420
FORDANALYST3000.00420
BLAKEMANAGER2850.00430
方法二、from子句中使用两个子查询
selectp.ename,p.job,p.sal,p.grade,p.deptno
from(
selecte.ename,e.job,e.sal,s.grade,e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal)p,
(
selectmax(s.grade)grade,e.deptno
fromempe,salgrades
wheree.salbetweens.losalands.hisal
groupbye.deptno)q
wherep.deptno=q.deptnoandp.grade=q.grade
orderbyp.deptno;