Oracle-09-子查询

如果在select子句中嵌套有select子句，那么被嵌套的select子句称为子查询，如果子查询返回一行，那么我们叫做单行子查询，如果返回多行，我们叫做多行子查询

单行子查询

select * from emp where sal>(select sal from emp where ename='ALLEN');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

6 rows selected.

Elapsed: 00:00:00.00

在子查询中使用函数

select * from emp where sal=(select min(sal) from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Elapsed: 00:00:00.00

每个部门的最高工资（子查询用到了分组函数）

select * from emp e where sal in (select max(sal) from emp where deptno=e.deptno);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

Elapsed: 00:00:00.01

在having子句中的子查询

select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        10       1300

Elapsed: 00:00:00.00

查询是领导的人员信息

in子句

select * from emp where empno in(select mgr from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

6 rows selected.

Elapsed: 00:00:00.01

exists子句

select * from emp e where exists(select 3 from emp where mgr=e.empno);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

6 rows selected.

Elapsed: 00:00:00.00

相对于in子句来说，exists子句只要检索到一条信息就返回给主查询，所以效率上比in要快很多