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