Oracle查询优化-03操作多个表

3.1 记录集的叠加

问题

要将来自多个表的数据组织到一起，就像将一个结果集叠加到另外一个上面一样。 这些表不必有相同的关键字，但是他们对应列的数据类型必须相同。

解决方案

使用union all 把多个表中的行组合到一起。

select ename, deptno  from emp where deptno = 10union allselect '-----', deptno  from deptunion allselect dname, deptno from dept;

结论

1. UNION ALL将多个来源的行组合起来，放到一个结果集中。 所有select列表中的项目数和对应项目的数据类型必须要匹配。

2. UNION ALL会包括重复的项目，如果要筛选掉重复项，可以使用UNION运算符。

3. 如果使用UNION而不是UNION ALL，很可能是为了去除重复项而进行排序操作。 在处理大结果集时要记住，使用UNION子句大致相当于下面的查询，对UNION ALL子句的查询结果使用DISTINCT子句

SQL> select distinct deptno  2    from (select deptno  from dept  3          union all  4          select deptno from dept);DEPTNO------    30    20    40    10SQL> SQL> select deptno  2    from dept  3  union  4  select deptno from dept  5  ;DEPTNO------    10    20    30    40SQL> 

1. 通常，查询中不要使用distinct，除非确定有必要这样做； 对于UNION而言也是如初，除非确定有必要，一般使用UNION ALL，而不适用UNION。

---

---

3.2 组合相关的行

问题

多表有一些相同的列，或者有些列的值相同，需要通过关联这些列得到结果。

解决方案

select a.ename ,b.dname  from emp a ,dept b where a.deptno = b.deptno and  a.deptno  = 10   ;select a.ename ,b.dname  from emp a inner  join dept b  on   a.deptno = b.deptno  where a.deptno  = 10 ;

结论

第二种解决方式是利用显示的ＪＯＩＮ子句（inner 关键字可省略），如果希望将联接逻辑关系放在from子句中，而不是在where 子句中，可以使用JOIN子句， 这两种方式都符合ANSI标准。

---

3.4 IN、EXISTS 和 INNER JOIN

问题

先创建一个表EMP2

create table emp2 as       select ename, job, sal, comm          from emp where job = 'CLERK';

要求返回与emp2(ename, job, sal)中数据相匹配的emp(ename, job, sal,deptno)信息

有in , exists 和 inner join 三种写法，为了加强理解，我们来看下三种写法及其对应的执行计划。

解决方案

ORACLE VERSION ： Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

IN

SQL> explain plan  for  2  select ename, job, sal, deptno  3    from emp  4   where (ename, job, sal) in (select ename, job, sal from emp2);ExplainedSQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4039873364---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 ||*  1 |  HASH JOIN SEMI    |      |     4 |   260 |     6   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")Note-----   - dynamic sampling used for this statement (level=2)19 rows selectedSQL> 

EXISTS

SQL> explain plan  for  2  select ename, job, sal, deptno  3    from emp a  where exists (select * from emp2 b  4    where b.ename= a.ename  5    and b.job = a.job  6    and b.sal = a.sal) ;ExplainedSQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4039873364---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 ||*  1 |  HASH JOIN SEMI    |      |     4 |   260 |     6   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND              "B"."SAL"="A"."SAL")Note-----   - dynamic sampling used for this statement (level=2)20 rows selectedSQL> 

INNER JOIN

因为子查询的join列（emp2.ename ,emp2.job ,emp2.sal）没有重复行，说这个查询可以直接改写为inner join

SQL>   explain plan for  2    select a.ename,  a.job,  a.sal,  a.deptno  3    from emp a join emp2 b on  4    (a.ename = b.ename  5    and  a.job= b.job  6    and a.sal =b.sal);ExplainedSQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 166525280---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 ||*  1 |  HASH JOIN         |      |     4 |   260 |     6   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("A"."ENAME"="B"."ENAME" AND "A"."JOB"="B"."JOB" AND              "A"."SAL"="B"."SAL")Note-----   - dynamic sampling used for this statement (level=2)20 rows selectedSQL> 

结论

或许与大家想象的不一样，以上三个PLAN中join写法利用了hash join（哈希连接）,其他两种运用的是 hash join semi(哈希半连接) 。 说明在这个语句中 in 和 exists的效率是一样的。

所以，在不知道哪种写法高效时应该查看Plan，而不是去记固定的结论。

---

3.5 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析

问题

有人对这几种连接方式，特别是left join 和 right join 分不清楚，下面通过案例来分析一下。

解决方案

SQL>CREATE TABLE L ASSELECT 'left_1' AS str,'1' AS v FROM dual UNION ALLSELECT 'left_2','2' AS v FROM dual UNION ALLSELECT 'left_3','3' AS v FROM dual UNION ALLSELECT 'left_4','4' AS v FROM dual;/*右表*/SQL>CREATE TABLE R ASSELECT 'right_3' AS str,'3' AS v,1 AS status FROM dual UNION ALLSELECT 'right_4' AS str,'4' AS v,0 AS status FROM dual UNION ALLSELECT 'right_5' AS str,'5' AS v,0 AS status FROM dual UNION ALLSELECT 'right_6' AS str,'6' AS v,0 AS status FROM dual;

SQL> select * from l ;STR    V------ -left_1 1left_2 2left_3 3left_4 4SQL> select * from r;STR     V     STATUS------- - ----------right_3 3          1right_4 4          0right_5 5          0right_6 6          0SQL> 

inner join的特点

该方式返回两表相匹配的数据。

inner join写法：select l.str, r.str from l inner join r on l.v = r.v order by 1, 2;where写法：select l.str, r.str from l, r where l.v = r.v order by 1, 2;输出：STR STR------ -------left_3 right_3left_4 right_4

left join的特点

该方式以左表为主表，左表返回所有的数据，右表只返回与左表匹配的数据。

SQL> select l.str, r.str from l left  join r on l.v = r.v order by 1, 2;STR STR------ -------left_1 left_2 left_3 right_3left_4 right_4SQL> 

(+)写法：

SQL> select l.str, r.str from l, r where l.v = r.v(+) order by 1, 2;STR STR------ -------left_1 left_2 left_3 right_3left_4 right_4SQL> 

right join的特点

该方式以右表为主表，右表返回所有的数据，左表只返回与左表匹配的数据。

select l.str, r.str from l right  join r on l.v = r.v order by 1, 2;(+)写法：select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;SQL> select l.str, r.str from l right  join r on l.v = r.v order by 1, 2;STR STR------ -------left_3 right_3left_4 right_4       right_5       right_6SQL>  select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;STR STR------ -------left_3 right_3left_4 right_4       right_5       right_6SQL> 

full join的特点

该方式的左表和右表都返回所有的数据，但只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据。

SQL> select l.str, r.str from l full  join r on l.v = r.v order by 1, 2;STR STR------ -------left_1 left_2 left_3 right_3left_4 right_4       right_5       right_66 rows selectedSQL> 

注意 FULL JOIN没有（+）写法

---

3.6 自关联

问题

表emp中有个字段 mgr，是主管的编码（对应于emp.empno）

如何根据这个信息返回主管的姓名呢？

解决方案

自关联，也就是两次查询表emp,分别取不同的别名，这样就可以当做是两个 表，后面的任务就是将这两个表 join连接起来即可。

为了便于理解，这里我们使用汉字作为别名，并把相关列一起返回。

select  员工.empno as 员工编码 , 员工.ename as 员工name,员工.job as 工作,员工.mgr as 员工表主管编码,主管.empno as 主管表主管编码,主管.ename as 主管姓名from emp 员工left join emp 主管 on  (员工.mgr = 主管.empno)order by 1;

结论

上述的操作，可以理解为我们是在两个不同的数据集中取数据。

create or replace view 员工 as select * from emp ;create or replace view 主管 as select * from emp ;

select  员工.empno as 员工编码 , 员工.ename as 员工name,员工.job as 工作,员工.mgr as 员工表主管编码,主管.empno as 主管表主管编码,主管.ename as 主管姓名from  员工left join  主管 on  (员工.mgr = 主管.empno)order by 1;

---

3.7 NOT IN、NOT EXISTS 和 LEFT JOIN

问题

有些单位的部门如40中一个员工也没有，只是设置了一个部门名字，如何通过关联查询把这些信息查询出来呢？

解决方案

数据库版本 11.2.0.4.0

alter table dept add constraints pk_dept primary key(deptno)

执行以下SQL并查询执行计划

not in

explain plan for select * from deptwhere deptno not in (select deptno from emp where deptno is not null);

not exists

explain plan for select * from dept where not exists (select null from emp where emp.deptno = dept.deptno)

left join

Left join 取出的是左表中所有的数据，其中右表不匹配的就表示左表not in 右表

explain plan for select dept.*  from deptleft join emp on emp.deptno = dept.deptnowhere emp.deptno is null ;select * from table(dbms_xplan.display());

结论

三个PLAN应用的都是 MERGE ＪＯＩＮＡＮＴＩ，说明这三种方法的效率是一样的。　

若果想改写，那么就要比对前后的ＰＬＡＮ，根据ＰＬＡＮ来判断并测试哪种方法的效率高，而不是凭借某些结论来碰运气。

---

3.8 外连接中的条件不要乱放

问题

对于左连语句，见下面的数据

SQL> select l.str, r.str ,r.status from l     left  join r     on l.v = r.v     order by 1, 2;STR     STR     STATUS------ -------  ------- left_1 left_2 left_3 right_3    1left_4 right_4    0SQL> 

对于L表，4条数据全部返回了，而对于R表，我们如果 只需要显示 status=1的部分，该如何写SQL呢？

常见的错误写法， 会有人直接在上面的语句中加入条件 status=1

 select l.str, r.str ,r.status from l     left  join r     on l.v = r.v     where r.status=1    order by 1, 2;

我们来看下返回结果：

STR     STR     STATUS------ -------  ------- left_3 right_3    1

很明显这不是我们想要的数据集。这是很多人写查询或者改查询时常遇到的一种错误， 问题在于所加条件的位置及写法。

那该如何做呢？

解决方案

left join写法

select l.str, r.str, r.status  from l  left join r    on (l.v = r.v and r.status = 1) order by 1, 2;

(+)写法

select l.str, r.str, r.status  from l, r where l.v = r.v(+)   and r.status(+) = 1 order by 1, 2;

---

3.9 检测两个表中的数据及对应数据的条数是否相同

问题

查找视图V 和 emp表中不同的数据

我们先创建一个视图

create or replace view v  as  select * from emp where deptno !=10 union all  select * from emp where ename='SCOTT';

我们可以知道 视图V中，SCOTT有两条记录， EMP中有一条

SQL> select * from v where ename='SCOTT';EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20SQL> select * from emp where ename='SCOTT';EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20SQL> 

比较两个数据集的不同时，通常类似下面的FULL JOIN 语句。

SQL> select v.EMPNO, v.ENAME, emp.empno, emp.ename  2    from v  3    full join emp  4      on v.EMPNO = emp.empno  5   where (v.EMPNO is null or emp.empno is null);EMPNO ENAME EMPNO ENAME----- ---------- ----- ----------                  7782 CLARK                  7839 KING                  7934 MILLERSQL> 

但是这种语句查不到 SCOTT的区别 。

解决方案

增加一列显示相同数据的条数，再进行比较。

select v_new.empno,       v_new.ENAME,       v_new.cnt,       emp_new.empno,       emp_new.ename,       emp_new.cnt  from (select v.empno, v.ENAME, count(1) as cnt          from v         group by v.empno, v.ENAME) v_new  full join (select a.empno, a.ENAME, count(1) as cnt               from emp a              group by a.empno, a.ENAME) emp_new    on (v_new.EMPNO = emp_new.empno and v_new.cnt = emp_new.cnt) where (v_new.EMPNO is null or emp_new.empno is null);

结果集：

EMPNO ENAME        CNT EMPNO ENAME        CNT----- ---------- ---------- ----- ---------- ----------                             7788 SCOTT          1                             7934 MILLER         1                             7782 CLARK          1                             7839 KING           1 7788 SCOTT          2                  SQL> 

---

3.10 聚集与内连接

问题

解决方案

结论

---

3.11 聚集与外连接

问题

解决方案

结论

---

3.12 从多个表中返回丢失的数据

问题

同时返回多个表中丢失的数据。 要从DEPT中返回EMP不存在的行(所有没有员工的部门)需要做外连接。

首先我们在EMP中增加一行deptno为空的数据，如下：

insert into emp  (empno, ename, job, mgr, hiredate, sal, comm, deptno)  select 6666, 'XGJ', 'JEDI', null, hiredate, sal, comm, null    from emp   where ename = 'KING';commit ;

此时，我们来看下 我们要查询的表中数据：

这时，如果我们使用下面的语句关联查询 ，就会发现少了emp=6666和 deptno=40的数据

select e.empno, e.ename, b.deptno, b.dname  from emp e  join dept b    on e.deptno = b.deptno;

如果想要返回这两条数据该如何写查询语句呢？ 下面介绍两种方法

解决方案

full join

select e.empno, e.ename, b.deptno, b.dname  from emp e  full join dept b    on e.deptno = b.deptno;

union all

select e.empno, e.ename, b.deptno, b.dname  from emp e  left join dept b    on e.deptno = b.deptnounion allselect e.empno, e.ename, b.deptno, b.dname  from emp e right join dept b    on e.deptno = b.deptnowhere e.empno is  null ;

在这里不建议使用union ,因为union会去掉重复记录。 如果确定需要去掉重复记录再使用。

---

3.13 多表查询时的空值处理

问题

NULL值永远不会等于或者不等于任何值，也包括null自己，但是需要像计算真实值一样计算可为空列的返回值。

返回所有比ALLEN提成低的员工， 提成 comm字段 ，有空值 。

数据如下：

SQL> select * from emp;EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK  7902 1980-12-17    800.00               20 7499 ALLEN SALESMAN  7698 1981-02-20   1600.00    300.00     30 7521 WARD SALESMAN  7698 1981-02-22   1250.00    500.00     30 7566 JONES MANAGER  7839 1981-04-02   2975.00               20 7654 MARTIN SALESMAN  7698 1981-09-28   1250.00   1400.00     30 7698 BLAKE MANAGER  7839 1981-05-01   2850.00               30 7782 CLARK MANAGER  7839 1981-06-09   2450.00               10 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20 7839 KING  PRESIDENT       1981-11-17   5000.00               10 7844 TURNER SALESMAN  7698 1981-09-08   1500.00      0.00     30 7876 ADAMS CLERK  7788 1987-05-23   1100.00               20 7900 JAMES CLERK  7698 1981-12-03    950.00               30 7902 FORD ANALYST  7566 1981-12-03   3000.00               20 7934 MILLER CLERK  7782 1982-01-23   1300.00               1014 rows selectedSQL> 

我们来看个错误的写法：

SQL> select  a.ename , a.comm  from emp a where a.comm  < (select comm from emp a where a.ename = 'ALLEN');ENAME      COMM---------- ---------TURNER      0.00SQL> 

只返回了一条 TURNER的数据，comm有许多空值 的并没有被显示出来，原因在于与空值比较后结果还是空值，需要先转换才行

解决方案

使用coalesce函数将null值转换为一个可以用来作为标准值进行比较的真实值。

coalesce函数从值列表中返回第一个非NULL值。当遇到NULL值将其替换为0，这样就可以同ALLEN的提成进行比较了。

SQL> select  a.ename , a.comm  from emp a where   coalesce( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');ENAME      COMM---------- ---------SMITH JONES BLAKE CLARK SCOTT KING  TURNER      0.00ADAMS JAMES FORD MILLER 11 rows selectedSQL> 

或者

select  a.ename , a.comm  from emp a where   nvl( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');

---