当一个查询是另一个查询的条件时，称之为。子查询可以使用几个简单命令构造功能强大的复合命令。
子查询最常用于WHERE子句中。还用在SELECT,FROM子句中，下面分别举例说明。
1. 用WHERE子句。
示例:显示emp表中职位为CLERK和SALESMAN的员工信息
| SQL> SELECT * FROM emp WHERE job in('CLERK','SALESMAN'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 8 rows selected |
2. 子查询用from子句。
示例:显示emp表中5-10条记录。
| SQL> SELECT empno,ename,job,hiredate,sal,comm,deptno 2 FROM (SELECT ROWNUM r,emp.* FROM emp ) T 3 WHERE T.r>=5 AND T.r<10; EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----------- --------- --------- ------ 7654 MARTIN SALESMAN 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 1981/5/1 2850.00 30 7782 CLARK MANAGER 1981/6/9 2450.00 10 7788 SCOTT ANALYST 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 5 rows selected |
3.用select子句
示例: 显示emp表中所员工信息及所在部门名称。
| SQL> SELECT e.*, 2 (SELECT dname FROM dept WHERE deptno=e.deptno) as dname 3 FROM EMP e; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO dname ----- ---------- --------- ----- ----------- --------- --------- ------ ----- 7369 SMITH CLERK 7902 1980/12/17 800.00 20 RESEARCH 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 SALES 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 SALES 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 RESEARCH 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 SALES 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 SALES …… 14 rows selected |