Oracle数据库_子查询的 概念与用法

一、什么是子查询？

  子查询是在sql语句内的另外一条select语句，在select、insert、update、delete命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以嵌套于另一个子查询中。

例如：在员工表emp中，查询部门名称dname为RESEARCH的员工信息

SQL> select * from emp where deptno=(select deptno from dept where dname='RESEARCH');

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                    20
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20
      7788 SCOTT                ANALYST                  7566 13-7月 -87           3000                    20

二、单行子查询

单行子查询是指只有一行返回结果的子查询，使用比较运算符的子查询，用到的比较运算符是=、<>、<、>、<=、>=

例如：请查询出既不是最高工资，也不是最低工资的员工信息

这里面select min(sal) from emp) 和select max(sal) from emp就是两个单行子查询。

SQL> select empno,ename,sal from emp where sal>(select min(sal) from emp) and sal <(select max(sal) from emp）;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7499 ALLEN                      1600
      7521 WARD                       1250
      7566 JONES                      2975
      7654 MARTIN                     1250
      7698 BLAKE                      2850
      7782 CLARK                      2450
      7788 SCOTT                      3000
      7844 TURNER                     1500

已选择 8 行。

三、多行子查询

多行子查询是指返回多行查询结果的子查询，使用in(子查询)运算符

（1）in

例如：emp表中，查询出不是销售部门SALES的员工信息

SQL> select empno,ename,job from emp where deptno in(select deptno from dept where dname <> 'SALES');

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      7369 SMITH                CLERK
      7566 JONES                MANAGER
      7782 CLARK                MANAGER
      7788 SCOTT                ANALYST
      7839 KING                 PRESIDENT
--等效于如下实现

SQL> select empno,ename,job from emp where deptno not in(select deptno from dept where dname = 'SALES');

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      7782 CLARK                MANAGER
      7839 KING                 PRESIDENT
      7369 SMITH                CLERK
      7566 JONES                MANAGER
      7788 SCOTT                ANALYST

（2）any必须与比较运算符一起使用

例如，在emp表中，查询工资大于10号部门的任意一个员工工资的其他部门的员工信息

SQL> select deptno,ename,sal from emp where sal>any（select sal from emp where deptno=10) and deptno<>'10';

    DEPTNO ENAME                       SAL
---------- -------------------- ----------
        20 JONES                      2975
        30 BLAKE                      2850
        20 SCOTT                      3000

（3）all

例如：在emp表中，查询工资大于部门编号为30的所有员工工资的员工信息

SQL> select deptno,ename,sal from emp where sal >all(select sal from emp where deptno='30');

    DEPTNO ENAME                       SAL
---------- -------------------- ----------
        20 JONES                      2975
        20 SCOTT                      3000
        10 KING                       5000

四、关联子查询

外查询和子查询的执行互相依赖对方的执行结果。

例如：在emp表中，使用关联子查询检索工资大于同职位job的平均工资的员工信息

SQL> select deptno,ename,sal from emp x where sal > (select avg(sal) from emp where job=x.job) order by job;

    DEPTNO ENAME                       SAL
---------- -------------------- ----------
        20 JONES                      2975
        30 BLAKE                      2850
        30 TURNER                     1500
        30 ALLEN                      1600

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                    20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10
      7788 SCOTT                ANALYST                  7566 13-7月 -87           3000                    20
      7839 KING                 PRESIDENT                     17-11月-81           5000                    10
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30

已选择 10 行。