1.SELECT select_list
FROM table
WHERE expr operater
(SELECT select_list
FROM table);
1)Expr operater包括比较运算符：
单行运算符（=、>、>=、<、<=、<>）
多行运算符（IN、ANY、ALL）
2)子查询可以嵌于一下SQL子句中：
WHERE 子句
HAVING 子句
FROM 子句
例：查询出比雇员为SCOTT工资高的其他雇员
SELECT ename
FROM emp
WHERE sal>
(SELECT sal
FROM emp
WHERE ename='SCOTT');
结果：
ENAME
KING
注：1)子查询要用括号括起来
2)将子查询放在比较运算符的右边
3)在子查询中，一般不需要ORDER BY子句
4)对于单行子查询要用单行运算符
5)对于多行子查询要用多行运算符
2.子查询的类型：单行子查询；多行子查询；多列子查询
3.单行子查询
1)子查询只返回一行,主查询使用单行运算符(=，>,>=,<,<=,<>)
例1：显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作
SELECT ename,job
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE empno=7369)
AND sal>
(SELECT sal
FROM emp
WHERE empno=7876);
结果：
ENAME JOB
MILLER CLERK
2)子查询中可以使用组函数
例2：显示工资最低的雇员的姓名、工作和工资
SELECT ename,job,sal
FROM emp
WHERE sal=
(SELECT MIN(sal)
FROM emp);
结果：
ENAME JOB SAL
SMITH CLERK 800
3)HAVING子句中的子查询
-Oracle服务器先执行子查询
-Oracle服务器将结果返回给主查询的HAVING子句
例3：SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal)>
(SELECT MIN(sal)
FROM emp
WHERE deptno=20);
结果：
DEPTNO MIN(SAL)
10 1300
30 950
4)判断语句
例1：SELECT ename,job
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE ename='SCOTT');
结果：
ENAME JOB
SCOTT ANALYST
FORD ANALYST
例2：SELECT empno,ename
FROM emp
WHERE sal=
(SELECT MIN(sal)
FROM emp
GROUP BY deptno);
结果：
(SELECT MIN(sal)
*
ERROR 位于第 4 行:
ORA-01427: 单行子查询返回多于一个行
4.多行子查询
1)返回多余一行记录
2)主查询使用多行比较运算符
IN----等于列表中的任意一项
ALL---和内部查询返回的全部结果比较(>ALL:比最大的大;<ALL:比最小的小)
ANY---和内部查询返回的结果逐个比较(>ANY:比最大值小;<ANY:比最小值大;=ANY等效于IN)
例1：
SELECT empno,ename,job
FROM emp
WHERE sal>ALL
(SELECT AVG(sal)
FROM emp
GROUP BY deptno);
结果：
EMPNO ENAME JOB
7566 JONES MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
例2：
SELECT empno,ename,job
FROM emp
WHERE sal>ANY
(SELECT sal
FROM emp
WHERE job='CLERK')
AND job<>'CLERK';
结果：
EMPNO ENAME JOB
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
5.多列子查询
-主查询和来自一个多行多列子查询的返回值进行比较
例：从emp表中找出与部门标号为30的任意一个雇员的薪水和佣金完全相同的雇员，并显示其姓名、部门编号、薪水和佣金。
方法一：SELECT ename,deptno,sal,comm
FROM emp
WHERE (sal,NVL(comm,0)) IN
(SELECT sal,NVL(comm,0)
FROM emp
WHERE deptno=30)
AND deptno<>30;
方法二：SELECT ename,deptno,sal,comm
FROM emp
WHERE sal IN(SELECT sal
FROM emp
WHERE deptno=30)
AND NVL(comm,-1) IN (SELECT NVL(comm,-1)
FROM emp
WHERE deptno=30)
AND deptno<>30;
6.在FROM子句中使用子查询
例：
SELECT a.ename,a.sal,a.deptno,b.salavg
FROM emp a,(SELECT deptno,avg(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno=b.deptno
AND a.sal>b.salavg;
结果：
ENAME SAL DEPTNO SALAVG
KING 5000 10 2916.66667
FORD 3000 20 2175
SCOTT 3000 20 2175
JONES 2975 20 2175
ALLEN 1600 30 1566.66667
BLAKE 2850 30 1566.66667
练习
1.查询emp表，显示与雇员SCOTT在同一个部门工作的所有雇员的ename和hiredate
SELECT ename,hiredate
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE ename='SCOTT');
2.查询emp表，显示所有雇员的ename、job、sal和hiredate信息
SELECT ename,job,sal,hiredate
FROM emp
WHERE sal>
(SELECT AVG(sal)
FROM emp
WHERE ename='SCOTT');
3.查询有下属的雇员信息
SELECT *
FROM emp
WHERE empno IN
(SELECT mgr
FROM emp);
4.查询薪水大于工作岗位是SALESMAN的所有部门平均薪水的雇员编号、雇员名、工作岗位、薪水
SELECT empno,ename,job,sal
FROM emp
WHERE sal>ALL
(SELECT AVG(sal)
FROM emp
WHERE job='SALESMAN'
GROUP BY deptno);
5.根据部门和工作岗位分组，显示最大的薪水合计数以及它们对应的部门及岗位
SELECT deptno,job,t.sum_t
FROM emp e,(SELECT MAX(total) sum_t
FROM (SELECT SUM(sal) total
FROM emp
GROUP BY deptno,job)
)t
GROUP BY deptno,job, t.sum_t
HAVING SUM(sal)=t.sum_t;