oracle之复杂查询(下）：子查询
复杂查询(下）：子查询
8. 1 非关联子查询：返回的值可以被外部查询使用。子查询可以独立执行的（且仅执行一次）。
8.1.1 单行单列子查询，子查询仅返回一个值，也称为标量子查询，采用单行比较运算符（>，<，=，<>,>=，<=)
例：内部SELECT子句只返回一行结果
SQL>select ename,sal
from emp
where sal > (
select sal from emp
where ename='JONES')
/
例：和员工7369从事相同工作并且工资大于员工7876的员工的姓名和工作
SQL>select ename,job，sal
from emp
where job=(
select job
from emp
where empno=7369
)
and
sal > (
select sal
from emp
where empno=7876
)
/
8.1.2 多行单列子查询，采用多行比较运算符（all, any, in，not in）
all （>大于最大的，<小于最小的）
SQL> select ename,sal from emp where sal >all (2000,3000,4000);
ENAME SAL
---------- ----------
KING 5000
例：查找高于所有部门的平均工资的员工（>比子查询中返回的列表中最大的大才行）
SQL> select ename, job, sal from emp where sal > all(select avg(sal) from emp group by deptno);
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
SQL> select avg(sal) from emp group by deptno; //子查询结果
AVG(SAL)
----------
1566.66667
2175
2916.66667
8.1.3 在多行子查询中使用any （>大于最小的，<小于最大的）
>any的意思是：比子查询中返回的列表中最小的大就行, 注意和all的区别，all的条件苛刻，any的条件松阔，
any强调的是只要有任意一个符合就行了，所以>any只要比最小的那个大就行了，没必要比最大的还大。
select ename, sal from emp where sal >any (2000,3000,4000);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
8.1.4 在多行子查询中使用in (逐个比较是否有匹配值）
SQL> select ename, sal from emp where sal in (800,3000,4000);
ENAME SAL
---------- ----------
SMITH 800
SCOTT 3000
FORD 3000
NOT运算操作符可以使用在IN操作上,但不能使用在ANY,ALL操作。
SQL> select ename, sal from emp where sal not in (800,3000,4000);
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
已选择11行。
8.1.5 多行多列子查询，子查询返回多列结果集，有成对比较、非成对比较两种形式。
测试准备
SQL>create table emp1 as select * from emp;
SQL>update emp1 set sal=1600,comm=300 where ename='SMITH'; //SMITH是20部门的员工
SQL>update emp1 set sal=1500,comm=300 where ename='CLARK'; //CLARK是10部门的员工
SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1600 300 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 1500 300 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
查询条件：查找emp1表中是否有与30部门的员工工资和奖金相同的其他部门的员工。
（注意看一下：现在20部门的SIMTH符合这个条件，它与30部门的ALLEN 有相同的工资和奖金）
成对多列子查询：
特点是主查询每一行中的列都要与子查询返回列表中的相应列同时进行比较，只有各列完全匹配时才显示主查询中的该数据行。
分解一下：
第一步，我们可以先找出emp1表中30号部门的工资和奖金的结果集，（此例没有对comm的空值进行处理）
SQL> select sal,comm from emp1 where deptno=30;
SAL COMM
---------- ----------
1600 300
1250 500
1250 1400
2850
1500 0
950
已选择6行。
第二步，列出emp1表中属于这个结果集的所有员工。
SQL> select * from emp1 where (sal,comm) in (select sal,comm from emp1 where deptno=30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1600 300 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
可以这样想：相当于谓词in（...)中有上面这6行内容，所以上面句子相当于：
select * from emp1 where (sal,comm) in(
(1600,300),(1250,500),(1250,1400),(2850,null),(1500,0),(950,null));
第三步， 再去掉30号部门后，就显示出了在emp1表中与30部门中任意一个员工的工资和奖金完全相同的，但不是30部门的那些员工的信息。
SQL>
select ename,deptno,sal,comm from emp1
where (sal,comm) in (select sal,comm from emp1 where deptno=30)
and deptno<>30
/
ENAME DEPTNO SAL COMM
---------- ---------- ---------- ----------
SMITH 20 1600 300
考点：1）成对比较是不能使用>any或>all等多行单列比较符的。2）成对比较时的多列顺序和类型必须一一对应。
8.1.6 与非成对比较（含布尔运算）的区别
例：非成对比较
SQL>select ename,deptno,sal,comm
from emp1
where sal in(
select sal
from emp1
where deptno=30)
and
nvl(comm,0) in (
select nvl(comm,0)
from emp1
where deptno=30)
and deptno<>30
/
ENAME DEPTNO SAL COMM
---------- ---------- ---------- ----------
SMITH 20 1600 300
CLARK 10 1500 300
两个子查询返回的值分别与主查询中的sal和comm列比较，
如果员工的工资与30部门任意一个员工相同，同时，奖金也与30部门的其他员工相同，那么得到了两个员工的信息。
可见，成对比较(使用where （列，列）)比非成对比较（使用where 列 and 列) 更为严苛。
8.1.7 关于布尔运算符not
not 就是否定后面的比较符，基本的形式如下
where empno=7788 where NOT (empno=7788)
where ename LIKE 'S%' where ename NOT LIKE 'S%'
where deptno IN (20,30) where deptno NOT IN (20,30)
where sal BETWEEN 1500 AND 3000 where sal NOT BETWEEN 1500 AND 3000
where comm IS NULL where comm IS NOT NULL
where EXISTS (select子查询） where NOT EXISTS (select子查询）
8.1.8 not in 在子查询中的空值问题：
"in"与"not in"遇到空值时情况不同，对于"not in" 如果子查询的结果集中有空值，那么主查询得到的结果集也是空。
查找出没有下属的员工,即普通员工，（该员工号不在mgr之列的）
SQL>select ename from emp where empno not in (select mgr from emp);
no rows selected
上面的结果不出所料，主查询没有返回记录。这个原因是在子查询中有一个空值，而对于not in这种形式，一旦子查询出现了空值，则主查询记录结果也就返回空了。
注意：not后不能跟单行比较符，只有not in组合，没有not any 和not all的组合，但not后可以接表达式 如：
where empno not in(...）与where not empno in(...)两个写法都是同样结果，前者是not in组合，后者是not一个表达式。
例：排除空值的影响
SQL>select ename from emp where empno not in (select nvl(mgr,0)from emp);
8.1.9 from子句中使用子查询(也叫内联视图）
例：员工的工资大于他所在的部门的平均工资的话，显示其信息。
分两步来考虑：
第一步，先看看每个部门的平均工资，再把这个结果集作为一个内联视图。
SQL> select deptno,avg(sal) salavg from emp group by deptno;
DEPTNO SALAVG
---------- ----------
30 1566.66667
20 2175
10 2916.66667
第二步，把这个内联视图起一个别名b, 然后和emp 别名e 做连接，满足条件即可。
SQL>
select e.ename, e.sal, e.deptno, b.salavg
from emp e, (select deptno,avg(sal) salavg from emp group by deptno) b
where e.deptno=b.deptno and e.sal > b.salavg
/
ENAME SAL DEPTNO SALAVG
---------- ---------- ---------- ----------
ALLEN 1600 30 1566.66667
JONES 2975 20 2175
BLAKE 2850 30 1566.66667
SCOTT 3000 20 2175
KING 5000 10 2916.66667
FORD 3000 20 2175
8.2关联子查询
其子查询（内部，inner)会引用主查询(外部，outer)查询中的一列或多列。在执行时,外部查询的每一行都被一次一行地传递给子查询，子查询依次读取外部查询传递来的每一值，并将其用到子查询上,直到外部查询所有的行都处理完为止，最后返回查询结果。
理论上主查询有n行，子查询被调用n次。
例1，关联查询用于select语句
8.1.9小节的例子，显示员工的工资大于他所在部门的平均工资，也可以使用关联查询。
SQL> select ename,sal,deptno from emp outer where sal> (select avg(sal) from emp inner where inner.deptno=outer.deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
例2，关联查询用于update语句
SQL> create table emp1 as (select e.empno,e.ename,d.loc,d.deptno from emp e,dept d where e.deptno=d.deptno(+));
SQL> update emp1 set loc=null;
SQL> commit;
如何通过关联查询再将emp1表更新回原值。
SQL> update emp1 e set loc=(select d.loc from dept d where e.deptno=d.deptno);
例3. 关联查询中的特殊形式，使用EXISTS或NOT EXISTS
EXISTS关心的是在子查询里能否找到一个行值（哪怕有10行匹配，只要找到一行就行），如果子查询有行值，则立即停止子查询的搜索，然后返回逻辑标识TRUE, 如果子查询没有返回行值，则返回逻辑标识FALSE， 子查询要么返回T，要么返回F，以此决定了主查询的调用行的去留，然后主查询指针指向下一行，继续调用子查询...
EXISTS的例子：显示出emp表中那些员工不是普通员工（属于大小领导的）。
SQL> select empno,ename,job,deptno from emp outer where exists (select 'X' from emp where mgr=outer.empno);
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7566 JONES MANAGER 20
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7902 FORD ANALYST 20
已选择6行。
说明：exists子查询中select 后的‘X'只是一个占位，它返回什么值无关紧要，它关心的是子查询中否‘存在’，即子查询的where条件能否有‘结果’，一旦子查询查到一条记录满足where条件，则立即返回逻辑‘TRUE’，（就不往下查了）。否则返回‘FALSE’。
NOT EXISTS的例子：显示dept表中还没有员工的部门。
SQL> select deptno,dname from dept d where not exists (select 'X' from emp where deptno=d.deptno);
DEPTNO DNAME
---------- --------------
40 OPERATIONS
对于关联子查询，在某种特定的条件下，比如子查询是个大表，且连接字段建立了索引，那么使用exists比in的效率可能更高。
8.10 关于别名的使用
有表别名和列别名, 表别名用于多表连接或子查询中，列别名用于列的命名规范。
如果别名的字面值有特殊字符，需要使用双引号。如："AB C"
8.3 必须使用别名的地方：
1）两表连接后，select 投影中有相同命名的列，必须使用表别名区别标识（自然连接中的公共列则使用相反原则）
select ename,d.deptno from emp e,dept d where e.deptno=d.deptno;
2）使用create * {table |view} as select ...语句创建一个新的对象，其字段名要符合对象中字段的规范，不能是表达式或函数等非规范字符，而使用别名可以解决这个问题。
create table emp1 as select deptno,avg(sal) salavg from emp group by deptno;
create view v as select deptno,avg(sal) salavg from emp group by deptno;
3）使用内联视图时， 若where子句还要引用其select中函数的投影, 使用别名可以派上用场。
select * from (select avg(sal) salavg from emp) where salavg>2000;
4）当以内联视图作为多表连接，主查询投影列在形式上不允许单行字段（或函数）与聚合函数并列，解决这个问题是使在内联视图中为聚合函数加别名，然后主查询的投影中引用其别名。
select e.ename,e.sal,b.deptno,b.salavg
from emp e,(select deptno,avg(sal) salavg from emp group by deptno) b
where e.deptno=b.deptno;
5）rownum列是Oracle的伪列，加别名可以使它成为一个表列，这样才可以符合SQL99标准中的连接和选择。
SQL> select * from (select ename,rownum rn from emp) where rn>5;
6）不能使用别名的地方：
在一个独立的select结构的投影中使用了列别名，不能在其后的where 或having中直接引用该列别名（想想为什么？）。
select ename,sal salary from emp where salary>2000; --错
select deptno,avg(sal) salavg from emp group by deptno having salavg>2000; --错
8.4 简单查询与复杂查询练习题：
1）列出emp表工资最高的前三名员工信息
select * from (select * from emp order by sal desc) where rownum < 4;
关于rownum 伪列使用特别需要注意两点：
1，rownum>时不会返回任何行
2，rownum< 和and并用时，是在另一个条件基础上的rownum< ,而不是两个独立条件的并集（intersect)
体会一下：
SQL> select ename,sal,deptno from emp where deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING 5000 10
MILLER 1300 10
SQL> select ename,sal,deptno from emp where rownum <=1;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20
SQL> select ename,sal,deptno from emp where rownum <=1 and deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
//即在deptno=10的基础上再rownum<=1
2) 列出emp表第5-10名员工（按sal大--小排序）的信息（结果集的分页查询技术）
SQL> select * from (select t1.*, rownum rn from (select * from emp order by sal desc) t1) where rn between 5 and 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 5
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 6
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 3 00.00 30 7
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 8
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 9
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 10
6 rows selected
3）从列出emp表中显示员工和经理对应关系表。(emp自连,利用笛卡尔积）
select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
from emp a, emp b
where a.mgr=b.empno;
4)要求列出emp表中最高工资的员工所在工作地点。(emp+dept左外，如果该员工不属于任何部门则列出员工姓名)
select a.ename, d.loc from
(select * from emp where sal=
(select max(sal) from emp)) a left join dept d on a.deptno=d.deptno ;
5）CTAS方法建立dept1，将dept1表增加一列person_count,要求根据emp表填写dept1表的各部门员工合计数（典型的关联查询）。
SQL> create table dept1 as select * from dept;
SQL> alter table dept1 add person_count int;
SQL> update dept1 d set person_count=(select count(*) from emp e where e.deptno=d.deptno);
SQL> select * from dept1;
DEPTNO DNAME LOC PERSON_COUNT
---------- -------------- ------------- ------------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
6) 复杂select查询，以HR用户的几个表为例(PPT-II-8)，显示欧洲地区员工的平均工资及人数（使用多表连接及内联视图）
SQL>
select avg(salary),count(salary) from
(select e.first_name,e.salary,d.department_id,l.location_id, c.country_id, r.region_id,r.region_name
from employees e,departments d,locations l,countries c,regions r
where e.department_id=d.department_id and d.location_id=l.location_id
and l.country_id=c.country_id and c.region_id=r.region_id and r.region_name='Europe');
AVG(SALARY) COUNT(SALARY)
----------- -------------
8916.66667 36
7）同上题（使用嵌套子查询技术）
SQL>
select avg(salary),count(*) from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id in
(select country_id from countries where region_id=
(select region_id from regions where region_name='Europe')
)
)
);
AVG(SALARY) COUNT(SALARY)
----------- -------------
8916.66667 36
分解如下：
SQL> select region_id from regions where region_name='Europe';
REGION_ID
----------
1
SQL> select country_id from countries where region_id=1;
CO
--
BE
CH
DE
DK
FR
IT
NL
UK
SQL> select location_id from locations where country_id in ('BE','CH','DE','DK','FR','IT','NL','UK');
LOCATION_ID
-----------
1000
1100
2400
2500
2600
2700
2900
3000
3100
SQL> select department_id from departments where location_id in (1000,1100,2400,2500,2600,2700,2900,3000,3100);
DEPARTMENT_ID
-------------
40
70
80
SQL> select avg(salary),count(*) from employees where department_id in(40,70,80);
AVG(SALARY) COUNT(*)
----------- ----------
8916.66667 36
