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;