本文深入讲解了SQL子查询的概念及应用场景，包括子查询的书写风格、执行顺序、分类及其与多表查询的区别，并通过实例演示如何进行有效的SQL优化。
1 为什么要有子查询
例：查询工资比scott高的员工信息
方法一：两步求解
(1):
SQL> select * from emp e
2 where e.ename = 'SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
(2):
SQL> select * from emp
2 where emp.sal > 3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
7839 KING PRESIDENT 17-11月-81 5000 10
方法二：子查询，相当于嵌套
SQL> select * from emp
2 where sal > (select sal from emp where emp.ename = 'SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
7839 KING PRESIDENT 17-11月-81 5000 10
子查询的本质：多个select语言的嵌套
2 子查询知识体系搭建
2.1 合理的书写风格
2.2 子查询外面的()不要忘记
2.3 子查询和主查询可以查询的是同一张表,也可以不是同一张表,只要子查询返回的结果,主查询可以用即可...
例：查询部门名称是SALES的员工信息，两种方法。
方法一：使用子查询
SQL> select * from emp where deptno = (select deptno from dept where dept.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
方法二：分开查询
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO -----返回部门编号30
40 OPERATIONS BOSTON
SQL> select * from emp where deptno = 30;
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
方法三：两张表的连接
SQL> select e.* from emp e, dept d
2 where e.deptno = d.deptno and d.dname = 'SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
重点：sql优化
1)：select * / abc......后者效率高
2)：where ... and ... and ... and ...从右向左
3)：having/where...where效率高
4)：子查询和多表查询那个效率高？在网络连接中，最耗时的时间是建立连接的过程！，在子查询中建立了两次连接，所以比较耗时，所以，多表查询效率较高。
2.4 在什么地方放置子查询
select a , b, c ---OK, 只能存放单行子查询(返回结果只能是一行),不能是多行子查询
from tab1 ---OK 可以放置子查询，重点..
where col in (em1, em2) ----OK
col between a1 and a2
col > 222
col > ()
group by ... ---不可以
having ..... ---可以
order by .. ---不可以
2.4.1 select之后只能放置单行子查询，不能放置多行子查询
错误示例：
SQL> select ename,job, sal, (select deptno from emp) from emp;
select ename,job, sal, (select deptno from emp) from emp
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
总结：select后面检索的内容可以是不同的列、不同的表
SQL> select empno, ename,job,sal, (select empno from emp where empno = 7499), sysdate from emp;
EMPNO ENAME JOB SAL (SELECTEMPNOFROMEMPWHEREEMPNO=7499) SYSDATE
---------- ---------- --------- ---------- ----------------------------------- --------------
1 tom_abc 8000 7499 29-3月 -16
7369 SMITH CLERK 800 7499 29-3月 -16
7499 ALLEN SALESMAN 1600 7499 29-3月 -16
7521 WARD SALESMAN 1250 7499 29-3月 -16
7566 JONES MANAGER 2975 7499 29-3月 -16
7654 MARTIN SALESMAN 1250 7499 29-3月 -16
7698 BLAKE MANAGER 2850 7499 29-3月 -16
7782 CLARK MANAGER 2450 7499 29-3月 -16
7788 SCOTT ANALYST 3000 7499 29-3月 -16
7839 KING PRESIDENT 5000 7499 29-3月 -16
7844 TURNER SALESMAN 1500 7499 29-3月 -16
7876 ADAMS CLERK 1100 7499 29-3月 -16
7900 JAMES CLERK 950 7499 29-3月 -16
7902 FORD ANALYST 3000 7499 29-3月 -16
7934 MILLER CLERK 1300 7499 29-3月 -16
2.4.2 from后边放置子查询
考题：查询员工的姓名和薪水(考试题)select * from _____;
分析：from之后的内容无非是一个集合，我们只需要自己构造一个只包含姓名和薪水的集合就好！
SQL> select * from (select ename, sal from emp);
ENAME SAL
---------- ----------
tom_abc 8000
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
2.5 子查询的分类
单行操作符对应单行子查询，多行操作符对应多行子查询。
按照子查询返回的条目数,分为: 单行子查询和多行子查询
单行子查询只能使用单行比较操作符( = > >= < <= <>), --ppt上的例子
多行子查询只能使用多行比较操作符(in any all)
--eg 单行例子ppt例子
--eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法
方法一：
SQL> select * from emp where deptno = (select deptno from dept where dname = 'SALES') or deptno = (select deptno from dept where dname = 'ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
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
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
方法二：
SQL> select * from emp
2 where deptno in(select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7839 KING PRESIDENT 17-11月-81 5000 10
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
7900 JAMES CLERK 7698 03-12月-81 950 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
注：
in：等于列表中的任何一个作比较
any：和子查询返回的任意一个值作比较
all：和子查询返回的所有值比较
--eg 查询薪水 比30号部门 任意一个员工薪高的员工信息， 大于集合中的最小值，any
SQL> select * from emp
2 where sal > any(select sal from emp where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
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
--eg 查询薪水 比30号部门 所有员工 高的员工信息， 大于集合中的最大值，all
错误示例：
SQL> select * from emp
2 where sal > (select sal from emp where deptno = 30);
where sal > (select sal from emp where deptno = 30)
*
第 2 行出现错误:
ORA-01427: 单行子查询返回多个行
注：此时条件出返回多行
正确示例：
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
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
1 tom_abc 8000 10
方法二：
SQL> select * from emp
2 where sal > (select max(sal) from emp where deptno = 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
7566 JONES MANAGER 7839 02-4月 -81 2975 20
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
子查询按照执行的顺序
一般性子查询 相关子查询
2.6 子查询遇见NULL
例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
例2：查询员工表中不是经理的员工信息
SQL> select * from emp
2 where empno not in(select mgr from emp where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1 tom_abc 8000 10
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
<span style="font-size:18px;color:#ff0000;"><strong>分析：
1.select a,b,c from table1 where deptno in(10, 20, null);
相当于======================>deptno = 10 or deptno =20 or deptno =null；
2. select a,b,c from table1 where deptno not in(10, 20, null);
相当于======================>deptno = !10 and deptno !=20 and deptno != null； 矛盾！！！
</strong></span>
2.7 一般情况下,子查询返回的是一个集合..子查询不排序....Top-N问题除外
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
