SQL 子查询

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语句的最后一个子句。

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;