3.1UNION ALL與空字符串

在第一章中我們多次使用了UNION ALL。UNION ALL通常用于合并多個數(shù)據(jù)集。

Oracle中常常把空字符串當(dāng)做NULL處理:

但空字符串與NULL并不等價。

空字符串本身是varchar2類型，這與NULL可以是任何類型不同，它們不等價。

3.2UNION 與OR

當(dāng)在條件里有or時，經(jīng)常會改寫為UNION，例如，我們在表emp中建立下面兩個索引：

然后執(zhí)行下面的查詢：

如果改寫為UNION ALL，則結(jié)果是錯的：

因為原語句中用的條件時or，是兩個結(jié)果的集合而非并集，所以一般改寫時需要改為UNION來去掉重復(fù)的數(shù)據(jù)。

這樣連個語句分別可以用empno及ename上的索引。

我們對比一下PLAN。

更改前（為了消除bitmap convert的影響，先設(shè)置參數(shù)）：

這時是FULL TABLE。

更改后的PLAN：

可以看到，更改后分別用了兩列中的索引。

3.3UNION與去重

UNION與UNION ALL的區(qū)別就是一個去重，一個不去重：

而UNION的執(zhí)行計劃如下：

通過執(zhí)行計劃可以看出UNION就是在UNION ALL的結(jié)果上再進行去重，模擬語句如下：

這種去重方式一般都不會有問題，但也有少數(shù)例外。例如下面的語句：

改用UNION后：

只剩下了一行數(shù)據(jù)，結(jié)果顯然不對。

從以上實驗可以看出：

1. 不僅兩個數(shù)據(jù)集間重復(fù)的數(shù)據(jù)會被去重，而且單個數(shù)據(jù)集里重復(fù)的數(shù)據(jù)也會被去重。

2. 有重復(fù)數(shù)據(jù)的數(shù)據(jù)集用UNION后得到的數(shù)據(jù)與預(yù)期會不一致。

那像這種數(shù)據(jù)如何用UNION改寫？我們只需要在去重前加入一個可以唯一標(biāo)識各行的列即可。

例如，在這里可以加入“empno”，再利用UNION，效果如下：

加入唯一列empno后，既保證了正確的去重，又防止了不該發(fā)生的去重。在此基礎(chǔ)上，在嵌套一層就是想要的結(jié)果。

3.4組合相關(guān)的行

相對于查詢單表中的數(shù)據(jù)來說，平時更常見的需求就是要在多個表中返回數(shù)據(jù)。比如，顯示部門10的員工編號、姓名及所在部門名稱和工作地址：

另有寫法如下：

其中，JOIN的寫法是SQL-92的標(biāo)準(zhǔn)，當(dāng)有多個表關(guān)聯(lián)時，JOIN方式的寫法更能清楚地看清各表之間的關(guān)系，因此，個人建議大家寫查詢語句時優(yōu)先使用JOIN的寫法。

3.5IN、EXISTS和INNER JOIN

下面先創(chuàng)建一個表emp2。

要求返回與表emp2(empno,job,sal)中數(shù)據(jù)相匹配的emp(empno,ename,job,sal,deptno)信息。

有IN、EXIXTS、INNER JOIN三種寫法。為了加強理解，請大家看一下三種寫法及其PLAN（此處用的是Oracle 11g）。

IN寫法：

EXISTS寫法：

因為子查詢的JOIN列（emp2.ename,emp2.job,emp2.sal）沒有重復(fù)行，所以這個查詢可以直接改為INNER JOIN：

或許與打架想象的不一樣，以上三個PLAN中JOIN寫法利用了HASH JOIN（哈希連接），其他兩種運用的都是HASH JOIN SEMI（哈希半連接），說明在這個語句中的IN與EXISTS效率是一樣的。所以，在不知哪種寫法高效時應(yīng)查看PLAN，而不是去記固定的結(jié)論。

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

有很多人對這幾種連接方式，特別是LEFT JOIN與RIGHT JOIN分不清，下面通過案例來解析一下。

首先建立兩個測試用表。

1. INNER JOIN的特點
   

   該方式返回兩表相匹配的數(shù)據(jù)，左表的“1、2”，以及右表的“5、6”都沒有顯示。

   JOIN寫法：

   
   

加WHERE條件后的寫法：

2.LEFT JOIN的特點

該方式的左表為主表，左表返回所有的數(shù)據(jù)，右表中只返回與左表匹配的數(shù)據(jù)，“5、6”都沒有顯示。

JOIN寫法：

加（+）后的寫法：

3.RIGHT JOIN的特點

該方式的右表為主表，左表中只返回與右表匹配的數(shù)據(jù)“3、4”，而“1、2”都沒有顯示，右表返回所有的數(shù)據(jù)。

JOIN寫法：

加（+）后的寫法：

4.FULL JOIN的特點

該方式的左右表均返回所有的數(shù)據(jù)，但只有相匹配的數(shù)據(jù)顯示在同一行，非匹配的行只顯示一個表的數(shù)據(jù)。

JOIN寫法：

注意：FULL JOIN無（+）的寫法。

3.7外連接與過濾條件

對于前面介紹的左聯(lián)語句，見下面的數(shù)據(jù)。

對于其中的Ｌ表，四條數(shù)據(jù)都反回了。而對于R表，我們只需要顯示其中的?status=1，也就是 r.val=4的部分。

結(jié)果應(yīng)為：

對于這種需求，會有人直接在上面的語句中加入條件status=1，寫出如下語句。

LEFT JOIN用法：

(+)用法：

這樣查詢的結(jié)果為：

而此時的PLAN為：

很顯然，與我們預(yù)期得到的結(jié)果不一致，這是很多人在寫查詢或更改查詢時常遇到的一種錯誤。問題就在于所加條件的位置及寫法，正確的寫法分別如下。

LEFT JOIN用法：

(+)用法：

在以上兩種寫法中，JOIN的方式明顯更容易辨別，這也是本書反復(fù)建議使用JOIN的原因。

語句也可以像下面這樣寫，先過濾，再用JOIN，這樣會更加清晰。

看一下現(xiàn)在的PLAN：

發(fā)現(xiàn)多了一個"OUTER"關(guān)鍵字，這表示前面已經(jīng)不是LEFT JOIN了，現(xiàn)在這個才是。

3.8自關(guān)聯(lián)

在表emp中有一個字段mgr，是主管的編碼(對應(yīng)于emp.empno)，如：(EMPNO:7698,ENAME:BLAKE)-->(MGR:7839)-->(EMPNO:7839,ENAME:KING)，說明BLAKE的主管就是KING。

如何根據(jù)這個信息返回主管的姓名呢？這里用到的就是自關(guān)聯(lián)。也就是兩次查詢表emp,分別取不同的別名，這樣就可以當(dāng)作兩個表，后面的任務(wù)就是將這兩個表和JOIN連接起來。

為了便于理解，這里用漢字作為別名，并把相關(guān)列一起返回。

可以理解為我們是在兩個不同的數(shù)據(jù)集中取數(shù)據(jù)。

3.9NOT IN、?NOT EXISTS和 LEFT JOIN

有些單位的部門（如40）中一個員工也沒有，只是設(shè)了一個部門名字，如下列語句：

如何通過關(guān)聯(lián)查詢把這些信息查出來？同樣有三種寫法：NOT IN、?NOT EXISTS和?LEFT JOIN。

語句及PLAN如下（版本為11.2.0.4.0）。

環(huán)境：

NOT IN用法：

NOT EXISTS用法：

根據(jù)前面介紹過的左聯(lián)知識，LEFT JOIN取出的是左表中所有的數(shù)據(jù)，其中與右表不匹配的就表示左表NOT IN右表。

所以在本節(jié)中LEFT JION 加上條件IS NULL，就是LEFT JOIN的寫法：

三個PLAN應(yīng)用的都是MERGE JOIN ANTI，說明這三種方法的效率一樣。

如果想改寫，就要對比改寫前后的PLAN，根據(jù)PLAN來判斷并測試哪種方法的效率高，而不能憑借某些結(jié)論來碰運氣。

3.10檢測兩個表中的數(shù)據(jù)及對應(yīng)數(shù)據(jù)的條數(shù)是否相同

我們首先建立試圖如下：

要求用查詢找出試圖V與表 emp中不同的數(shù)據(jù)。

注意：視圖中員工“SCOTT” 有兩行數(shù)據(jù)，而emp表中只有一條數(shù)據(jù)。

比較兩個數(shù)據(jù)集的不同時，通常用類似下面的FULL JOIN語句：

但是這種語句在這個案例中查不到SCOTT的區(qū)別。

這時我們就要對數(shù)據(jù)進行處理，增加一列顯示相同數(shù)據(jù)的條數(shù)，在進行比較。

正確結(jié)果如下：

3.11聚集與內(nèi)連接

首先建立案例用表如下：

員工的獎金根據(jù)TYPE計算，TYPE=1的獎金為員工工資的10%， TYPE=2的獎金為員工工資的20%， TYPE=3的獎金為員工工資的30%。

現(xiàn)要求返回上述員工（也就是部門10的所有員工）的工資及獎金。

讀者或許會馬上想到前面講到的JOIN語句，先關(guān)聯(lián)，然后對結(jié)果做聚集。

那么在做聚集之前，我們先看一下關(guān)聯(lián)后的結(jié)果。

對這樣的關(guān)聯(lián)結(jié)果進行聚集后的數(shù)據(jù)如下：

這里出現(xiàn)的獎金總額沒錯，工資總額為10050，而實際工資總額應(yīng)為8750：

關(guān)聯(lián)后返回的結(jié)果多了10050-8750=1300，原因正如前面顯示的一樣，員工的MILLER工資重復(fù)計算了兩次。

對于這種問題，我們應(yīng)該先對emp_bonus做聚集操作，然后關(guān)聯(lián)emp表。

下面分步演示一下。

未匯總前，有兩條7934：

把emp_bonus按empno分類匯總，匯總后會只有一條7934，再與emp關(guān)聯(lián)就沒有問題了。

這是最終的正確語句，先把獎金按員工（empno）匯總，在于員工表關(guān)聯(lián)。

這樣結(jié)果就對了。