Oracle查询优化改写技巧与案例 第三章 操作多个表
3.1UNION ALL与空字符串
在第一章中我们多次使用了UNION ALL。UNION ALL通常用于合并多个数据集。
Oracle中常常把空字符串当做NULL处理:
但空字符串与NULL并不等价。
空字符串本身是varchar2类型，这与NULL可以是任何类型不同，它们不等价。
3.2UNION 与OR
当在条件里有or时，经常会改写为UNION，例如，我们在表emp中建立下面两个索引：
然后执行下面的查询：
如果改写为UNION ALL，则结果是错的：
因为原语句中用的条件时or，是两个结果的集合而非并集，所以一般改写时需要改为UNION来去掉重复的数据。
这样连个语句分别可以用empno及ename上的索引。
我们对比一下PLAN。
更改前（为了消除bitmap convert的影响，先设置参数）：
这时是FULL TABLE。
更改后的PLAN：
可以看到，更改后分别用了两列中的索引。
3.3UNION与去重
UNION与UNION ALL的区别就是一个去重，一个不去重：
而UNION的执行计划如下：
通过执行计划可以看出UNION就是在UNION ALL的结果上再进行去重，模拟语句如下：
这种去重方式一般都不会有问题，但也有少数例外。例如下面的语句：
改用UNION后：
只剩下了一行数据，结果显然不对。
从以上实验可以看出：
不仅两个数据集间重复的数据会被去重，而且单个数据集里重复的数据也会被去重。
有重复数据的数据集用UNION后得到的数据与预期会不一致。
那像这种数据如何用UNION改写？我们只需要在去重前加入一个可以唯一标识各行的列即可。
例如，在这里可以加入“empno”，再利用UNION，效果如下：
加入唯一列empno后，既保证了正确的去重，又防止了不该发生的去重。在此基础上，在嵌套一层就是想要的结果。
3.4组合相关的行
相对于查询单表中的数据来说，平时更常见的需求就是要在多个表中返回数据。比如，显示部门10的员工编号、姓名及所在部门名称和工作地址：
另有写法如下：
其中，JOIN的写法是SQL-92的标准，当有多个表关联时，JOIN方式的写法更能清楚地看清各表之间的关系，因此，个人建议大家写查询语句时优先使用JOIN的写法。
3.5IN、EXISTS和INNER JOIN
下面先创建一个表emp2。
要求返回与表emp2(empno,job,sal)中数据相匹配的emp(empno,ename,job,sal,deptno)信息。
有IN、EXIXTS、INNER JOIN三种写法。为了加强理解，请大家看一下三种写法及其PLAN（此处用的是Oracle 11g）。
IN写法：
EXISTS写法：
因为子查询的JOIN列（emp2.ename,emp2.job,emp2.sal）没有重复行，所以这个查询可以直接改为INNER JOIN：
或许与打架想象的不一样，以上三个PLAN中JOIN写法利用了HASH JOIN（哈希连接），其他两种运用的都是HASH JOIN SEMI（哈希半连接），说明在这个语句中的IN与EXISTS效率是一样的。所以，在不知哪种写法高效时应查看PLAN，而不是去记固定的结论。
3.6INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN解析
有很多人对这几种连接方式，特别是LEFT JOIN与RIGHT JOIN分不清，下面通过案例来解析一下。
首先建立两个测试用表。
INNER JOIN的特点
该方式返回两表相匹配的数据，左表的“1、2”，以及右表的“5、6”都没有显示。
JOIN写法：
加WHERE条件后的写法：
2.LEFT JOIN的特点
该方式的左表为主表，左表返回所有的数据，右表中只返回与左表匹配的数据，“5、6”都没有显示。
JOIN写法：
加（+）后的写法：
3.RIGHT JOIN的特点
该方式的右表为主表，左表中只返回与右表匹配的数据“3、4”，而“1、2”都没有显示，右表返回所有的数据。
JOIN写法：
加（+）后的写法：
4.FULL JOIN的特点
该方式的左右表均返回所有的数据，但只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据。
JOIN写法：
注意：FULL JOIN无（+）的写法。
3.7外连接与过滤条件
对于前面介绍的左联语句，见下面的数据。
对于其中的Ｌ表，四条数据都反回了。而对于R表，我们只需要显示其中的 status=1，也就是 r.val=4的部分。
结果应为：
对于这种需求，会有人直接在上面的语句中加入条件status=1，写出如下语句。
LEFT JOIN用法：
(+)用法：
这样查询的结果为：
而此时的PLAN为：
很显然，与我们预期得到的结果不一致，这是很多人在写查询或更改查询时常遇到的一种错误。问题就在于所加条件的位置及写法，正确的写法分别如下。
LEFT JOIN用法：
(+)用法：
在以上两种写法中，JOIN的方式明显更容易辨别，这也是本书反复建议使用JOIN的原因。
语句也可以像下面这样写，先过滤，再用JOIN，这样会更加清晰。
看一下现在的PLAN：
发现多了一个"OUTER"关键字，这表示前面已经不是LEFT JOIN了，现在这个才是。
3.8自关联
在表emp中有一个字段mgr，是主管的编码(对应于emp.empno)，如：(EMPNO:7698,ENAME:BLAKE)-->(MGR:7839)-->(EMPNO:7839,ENAME:KING)，说明BLAKE的主管就是KING。
如何根据这个信息返回主管的姓名呢？这里用到的就是自关联。也就是两次查询表emp,分别取不同的别名，这样就可以当作两个表，后面的任务就是将这两个表和JOIN连接起来。
为了便于理解，这里用汉字作为别名，并把相关列一起返回。
可以理解为我们是在两个不同的数据集中取数据。
3.9NOT IN、 NOT EXISTS和 LEFT JOIN
有些单位的部门（如40）中一个员工也没有，只是设了一个部门名字，如下列语句：
如何通过关联查询把这些信息查出来？同样有三种写法：NOT IN、 NOT EXISTS和 LEFT JOIN。
语句及PLAN如下（版本为11.2.0.4.0）。
环境：
NOT IN用法：
NOT EXISTS用法：
根据前面介绍过的左联知识，LEFT JOIN取出的是左表中所有的数据，其中与右表不匹配的就表示左表NOT IN右表。
所以在本节中LEFT JION 加上条件IS NULL，就是LEFT JOIN的写法：
三个PLAN应用的都是MERGE JOIN ANTI，说明这三种方法的效率一样。
如果想改写，就要对比改写前后的PLAN，根据PLAN来判断并测试哪种方法的效率高，而不能凭借某些结论来碰运气。
3.10检测两个表中的数据及对应数据的条数是否相同
我们首先建立试图如下：
要求用查询找出试图V与表 emp中不同的数据。
注意：视图中员工“SCOTT” 有两行数据，而emp表中只有一条数据。
比较两个数据集的不同时，通常用类似下面的FULL JOIN语句：
但是这种语句在这个案例中查不到SCOTT的区别。
这时我们就要对数据进行处理，增加一列显示相同数据的条数，在进行比较。
正确结果如下：
3.11聚集与内连接
首先建立案例用表如下：
员工的奖金根据TYPE计算，TYPE=1的奖金为员工工资的10%， TYPE=2的奖金为员工工资的20%， TYPE=3的奖金为员工工资的30%。
现要求返回上述员工（也就是部门10的所有员工）的工资及奖金。
读者或许会马上想到前面讲到的JOIN语句，先关联，然后对结果做聚集。
那么在做聚集之前，我们先看一下关联后的结果。
对这样的关联结果进行聚集后的数据如下：
这里出现的奖金总额没错，工资总额为10050，而实际工资总额应为8750：
关联后返回的结果多了10050-8750=1300，原因正如前面显示的一样，员工的MILLER工资重复计算了两次。
对于这种问题，我们应该先对emp_bonus做聚集操作，然后关联emp表。
下面分步演示一下。
未汇总前，有两条7934：
把emp_bonus按empno分类汇总，汇总后会只有一条7934，再与emp关联就没有问题了。
这是最终的正确语句，先把奖金按员工（empno）汇总，在于员工表关联。
这样结果就对了。
