1、为什么要有子查询
bg：查询工资比scott高的员工信息
<1>查询scott的工资
SQL> select sal from emp where ename = 'SCOTT';
SAL
----------
3000
<2>查询比工资比3000大的员工
SQL> select * from emp where sal > 3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
<3>分两步太麻烦，于是二者结合，子查询诞生
SQL> select * from emp where sal > (select sal from emp where ename = 'SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
<4>透过现象看本质：子查询就是多个select语句的嵌套
2、子查询的游戏规则
（1）良好的书写风格与习惯（这点在哪都很重要）；
（2）子查询外面的()不要忘记；
（3）子查询和主查询查询的可以是同一张表,也可以不是同一张表，只要子查询返回的结果,主查询可用即可；
eg：使用2种方法查询部门名称是SALES的员工信息
<1>方法一
SQL> select * from emp
2 where deptno = (select deptno
3 from dept
4 where dname = 'SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择6行。
<2>方法二
SQL> select e.*
2 from emp e,dept d
3 where e.deptno = d.deptno and d.dname = 'SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
已选择6行。
（4）子查询放置的位置
select a , b, c ---可以，但只能存放置单行子查询,不能放置多行子查询
from tab1 ---可以
where ... ---可以
group by ... ---不可以
having ... ---可以
order by ... ---不可以
<1>多行子查询（错误）
SQL> select ename,empno,(select deptno from emp) deptno
2 from emp;
select ename,empno,(select deptno from emp) deptno
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
<2>单行子查询（正确）
SQL> select ename,empno,(select deptno from emp where empno = 7369)
2 from emp;
ENAME EMPNO (SELECTDEPTNOFROMEMPWHEREEMPNO=7369)
---------- ---------- ------------------------------------
SMITH 7369 20
ALLEN 7499 20
WARD 7521 20
JONES 7566 20
MARTIN 7654 20
BLAKE 7698 20
CLARK 7782 20
SCOTT 7788 20
KING 7839 20
TURNER 7844 20
ADAMS 7876 20
JAMES 7900 20
FORD 7902 20
MILLER 7934 20
已选择14行。
<3>查询员工的姓名和薪水
SQL> select * from (select ename,sal from emp);
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
<4>查询部门名称是SALES和ACCOUNTING的员工信息
SQL> select * from emp
2 where deptno in(select deptno from dept
3 where dname = 'SALES' or dname = 'ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
已选择9行。
（5）子查询分类
<1>按照子查询返回的条目数，分为单行子查询和多行子查询
说明：
1. 单行操作符对应单行子查询，多行操作符对应多行子查询
2. 单行子查询只能使用单行比较操作符( = > >= < <= <>)
3. 多行子查询只能使用多行比较操作符(in any all)
eg：
1>查询薪水比30号部门任意一个员工薪高的员工信息（大于集合中的最小值）
SQL> select * from emp
2 where sal > any(select sal from emp where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
已选择12行。
2>查询薪水比30号部门所有员工高的员工信息（大于集合中的最大值）
SQL> select * from emp
2 where sal > all(select sal from emp where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
<2>按照子查询执行的顺序，分为一般子查询和相关子查询
1.一般子查询:子查询的结果被主查询使用
eg：找到员工表中薪水大于本部门平均薪水的员工
SQL> select e.empno,e.ename,e.sal,d.avgsal,e.deptno from emp e,
2 (select deptno,avg(sal) avgsal from emp group by deptno) d
3 where e.deptno = d.deptno and e.sal > d.avgsal
4 order by e.deptno;
EMPNO ENAME SAL AVGSAL DEPTNO
---------- ---------- ---------- ---------- ----------
7839 KING 5000 2916.66667 10
7566 JONES 2975 2175 20
7788 SCOTT 3000 2175 20
7902 FORD 3000 2175 20
7499 ALLEN 1600 1566.66667 30
7698 BLAKE 2850 1566.66667 30
已选择6行。
2.相关子查询:主查询的参数让子查询用（一般是通过别名技术）
eg：找到员工表中薪水大于本部门平均薪水的员工
SQL> select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal,e.deptno
2 from emp e
3 where e.sal > (select avg(sal) from emp where deptno = e.deptno)
4 order by e.deptno;
EMPNO ENAME SAL AVGSAL DEPTNO
---------- ---------- ---------- ---------- ----------
7839 KING 5000 2916.66667 10
7566 JONES 2975 2175 20
7902 FORD 3000 2175 20
7788 SCOTT 3000 2175 20
7499 ALLEN 1600 1566.66667 30
7698 BLAKE 2850 1566.66667 30
已选择6行。
（6）当子查询遇见null
eg：查询不是经理的员工信息
<1>查询是经理的员工信息
SQL> select * from emp
2 where empno in(select mgr from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择6行。
<2>查询不是经理的员工信息
SQL> select * from emp
2 where empno not in(select mgr from emp);
未选定行
<3>没有任何数据返回，why？
因为not in后面的集合中出现了空值（KING的MGR为空），还记得之前提过的当not in集合中包含了null会使查询结果受到影响的事吗？
<4>解决办法
SQL> select * from emp
2 where empno not in (select mgr from emp
3 where mgr is not null);
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
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
215
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
