SQL Server-外部聯結基礎回顧（十三）

標籤：添加   獨立   謂詞   bsp   diff   返回   use   span   color   

前言

本節我們繼續講講聯結類型中的外部聯結，本節之後我們將講述有關聯結效能以及更深入的知識，簡短內容，深入的理解，Always to review the basics。

外部聯結

外部聯結又分為左方外部聯結和右方外部聯結，使用關鍵字分別是LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN，在這裡OUTER關鍵字時可選的。LEFT關鍵字表示保留左側的行，RIGHT關鍵字表示保留右側的行，FULL關鍵字表示左側和右側的行都保留。外部聯結的第三個邏輯查詢處理階段識別保留表中基於ON謂詞未能與另一個表匹配的行，此階段添加這些行到前兩個聯結階段產生的結果中，在這些外部行中，對於聯結非保留側的屬性將使用NULL作為預留位置。說了這麼多，左方外部聯結就是以左表為基準，若右表滿足條件則返回右側的行，若不滿足則返回NULL而非右側的實際行資料，右方外部聯結同理。我們來看如下一個簡單的例子

USE TSQL2012GOSELECT C.custid, C.companyname, O.orderidFROM Sales.Customers AS C    LEFT JOIN Sales.Orders AS O ON C.custid = O.custid

從上知，Sales.Customers表中的有一個客戶沒有任何訂單，它的訂單Id為22，通過左側客戶Id為22而右側得到訂單Id為NULL而得知。

超越外部聯結基礎知識

通過上述對外部聯結的介紹，我們知道通過外部聯結能夠得到缺失值，也就是不滿足條件則返回NULL。這裡我們假設有如下一情境，我們需要查詢Orders訂單表中所有訂單，要求確保在範圍2006年1月1日至2008年12月31日中每天至少有一行輸出，對於範圍能具有的訂單的日期不做任何操作，但希望輸出中包含沒有訂單的日期，使用NULL標記作為訂單屬性預留位置。我們第一步需要解決的是得到2006年1月1日至2008年12月31日的所有日期，上一篇我們講過交叉聯結，通過交叉聯結我們產生了數字表，這個時候就派上用場了。

首先需要得到2006年1月1日至2008年12月31日之間間隔的天數，然後得到此間隔中的所有日期，如下：

USE TSQL2012GOSELECT DATEADD(DAY, n -1 ,‘20060101‘) AS orderdateFROM dbo.NumsWHERE n <= DATEDIFF(DAY, ‘20060101‘,‘20081231‘) + 1ORDER BY orderdate

接下來通過上述得到的連續日期與Sales.Orders表中的訂單日期進行匹配，從而得到訂單所有資訊，利用左方外部聯結不滿足條件則返回NULL。

USE TSQL2012GOSELECT DATEADD(DAY, n -1 ,‘20060101‘) AS orderdate,O.orderid,    O.custid, O.empidFROM dbo.Nums    LEFT JOIN Sales.Orders AS O    ON DATEADD(DAY, dbo.Nums.n - 1, ‘20060101‘) = O.orderdateWHERE dbo.Nums.n <= DATEDIFF(DAY, ‘20060101‘,‘20081231‘) + 1ORDER BY orderdate

外部聯結注意事項 （1）

（1）WHERE造成外部聯結成為邏輯上的內部聯結

我們看上述圖中在訂單日期等於2006-11-09時，此時orderid為NULL，此時我們首先來進行如下查詢

USE TSQL2012GOSELECT orderdate, orderid, custid, empidFROM Sales.OrdersWHERE orderdate > ‘20061108‘

我們查詢Sales.Orders表中訂單日期大於2006-11-08訂單資訊，我們看下返回結果

此時我們發現訂單日期為2006-11-09的訂單資訊沒有，為何如此呢，因為利用WHERE子句它會過濾掉UNKNOWN即NULL值，為什麼要講這個呢，因為在外部聯結中不滿足條件的右表原本會返回NULL，但是若存在WHERE子句時，此時會導致所有外部行會被過濾掉，換句話說就是抵消了外部聯結實際上在邏輯上就相當於是一個內部聯結，這就在無意中造成了邏輯上的BUG。所以基於此我們可以得出如下結論：

結論：在外部聯結中若利用WHERE子句會過濾掉NULL，會導致所有外部行被過濾掉，實際上會抵消外部聯結，最終外部聯結在邏輯上就成為了一個內部聯結。

（2）多表聯結造成外部聯結成為邏輯上的內部聯結 

在進行多聯結時比如說首先進行兩個表的外部聯結，緊接著後面跟了一個內部聯結的第三個表，如果內部聯結中子句的謂詞對自外部聯結非保留側的屬性和來自第三個表的屬性進行比較，此時所有外部行都會被過濾掉。是什麼意思呢，當利用外部聯結時會可能返回外部行的NULL值，此時再利用內部聯結，因為NULL與任何值進行比較都會產生UNKNOWN，所以此時UNKNOWN會被ON篩選過濾掉。同上也就抵消外部聯結，造成外部聯結成為邏輯上的內部聯結。如下

USE TSQL2012GOSELECT C.custid, O.orderid, OD.productid, OD.qtyFROM Sales.Customers AS C LEFT JOIN Sales.Orders AS O ON O.custid = C.custid INNER JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid

一般來說，無論何種類型的外部聯結後跟一個內部聯結或是外部聯結子查詢，外部行都會被過濾掉，當然，這是假設聯結條件對來自左側的NULL標記和右側的任意值進行比較。為瞭解決這種問題，我們可以通過如下三種方案來解決。

【1】將第二個內部聯結替換為左方外部聯結。

USE TSQL2012GOSELECT C.custid, O.orderid, OD.productid, OD.qtyFROM Sales.Customers AS C LEFT JOIN Sales.Orders AS O ON O.custid = C.custid LEFT JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid

【2】首先使用內部聯結，然後再使用右方外部聯結。

SELECT C.custid, O.orderid, OD.productid, OD.qtyFROM Sales.Orders AS O LEFT JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderid INNER JOIN Sales.Customers AS C ON O.custid = C.custid

【3】在原有基礎上改變，將內部聯結變成一個獨立的邏輯階段 

USE TSQL2012GOSELECT C.custid, O.orderid, OD.productid, OD.qtyFROM Sales.Customers AS C LEFT JOIN     (Sales.Orders AS O INNER JOIN Sales.OrderDetails AS OD ON O.orderid = OD.orderid) ON C.custid = O.custid

外部聯結注意事項 （2）

在外部聯結中使用COUNT(*)進行彙總時，它會考慮內部行和外部行，因為它會計算行數而不管它們的內容，如下：

USE TSQL2012GOSELECT C.custid, COUNT(*) AS numordersFROM Sales.Customers AS C LEFT JOIN  Sales.Orders AS O ON O.custid = C.custidGROUP BY C.custid

由之前所查得知客戶Id為22的orderid為NULL，即沒有訂單數量，所以這裡就產生了BUG，因為COUNT(*)會包括NULL值，所以這裡我們需要替換為COUNT(列名)。

USE TSQL2012GOSELECT C.custid, COUNT(O.orderid) AS numordersFROM Sales.Customers AS C LEFT JOIN  Sales.Orders AS O ON O.custid = C.custidGROUP BY C.custid

總結 

本節我們重點講述了外部聯結基本知識以及注意事項，我們下節將講述聯結綜合知識，簡短的內容，深入的理解，我們下節再會，good night。

SQL Server-外部聯結基礎回顧（十三）