Posted on 2007-01-19 12:47
大大毛 閱讀(309)
評論(0) 編輯 收藏 所屬分類:
SQL
前言：
??????這裡講的外聯指的是 Left/Right Join，這兩個是等效的，出於習慣用 Left Join。在遇到需要使用外聯的地方，大家通常會按照 Inner Join 的思路來進行改造，特別是在原有的內聯邏輯需要轉變到外聯邏輯的時候。雖然有某些時候內聯改外聯只要變關鍵字就好，但遺憾的是絕大部分情況都不會如此簡單，得要小心從事才行。下面就我遇到過的例舉一二：
DDL：
示例用DDL
--
場地
Create
?
Table
?tbPlace?(
????CPlaceCode??
char
(
5
)?
primary
?
key
,
????CIsUse??????
char
(
1
),
????CDelFlag????
char
(
1
)
)
--
合約
Create
?
Table
?tbContractRent?(
????CContractId?
char
(
5
)?
primary
?
key
,
????CPlaceCode??
char
(
5
)?
not
?
null
,
????CEndDate????
char
(
8
),
????CDelFlag????
char
(
1
)
)
--
合約房東
Create
?
Table
?tbContractOwner?(
????CContractId?
char
(
5
),
????COwnerId????
char
(
5
),
????CDelFlag????
char
(
1
),
????
primary
?
key
(CContractId,COwnerId)
)
--
房東
Create
?
Table
?tbOwner?(
????COwnerId????
char
(
5
)?
primary
?
key
,
????CDelFlag????
char
(
1
)
)
???表間關係是：
??????合約－－場地;合約－(合約房東)－房東;
???說明：
??????各表的 CDelFlag 字段是一個刪除標識，CDelFlag='N' 的記錄才是可用的。
??????場地的 CIsUse 字段是個類型標識，下面的示例中會引用到。
示例1：
??? 邏輯：
?????????搜尋在指定時段內結束的合約房東。
??? 代入參數：
??????? 1.PlaceCode:場地的PK，Like匹配;
??????? 2.StartDate:開始日期;
??????? 3.EndDate:結束日期。
???DML：
DML：
????
--
原邏輯：搜尋在指定時段內結束的(有效)合約房東資料
????
Select
????????????
*
?
????????
From
????????????tbContractRent?
Join
?tbContractOwner?
On
?tbContractRent.CContractId
=
tbContractOwner.CContractId
????????????
Join
?tbOwner?
On
?tbContractOwner.COwnerId
=
tbOwner.COwnerId
????????
Where
????????????tbContractRent.CDelFlag
=
'
N
'
????????????
And
?tbContractOwner.CDelFlag
=
'
N
'
????????????
And
?tbOwner.CDelFlag
=
'
N
'
????????????
And
?tbContractRent.CEndDate?
Between
?
@StartDate
?
And
?
@EndDate
????????????
And
?tbContractRent.CPlaceCode?
Like
?
'
%@PlaceCode%
'
???????可以看到，這段DML代碼的邏輯是非常簡單的，構造也很容易，就是將 tbContractRent 與 tbOwner 通過中間表 tbContractOwner來連接，只要代入參數以及實現 CDelFlag = 'N' 就好。
??????邏輯變更:
?????????搜尋在指定時段內結束的所有(有效)合約且帶出房東資料(部分合約無匹配的房東資料)
?????????根據邏輯此時要做的是需要將上面的內聯改成外聯關係。
--
陷阱1：
????
Select
????????????
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbContractOwner?
On
?tbContractRent.CContractId
=
tbContractOwner.CContractId
????????????
Join
?tbOwner?
On
?tbContractOwner.COwnerId
=
tbOwner.COwnerId
????????
Where
????????????tbContractRent.CDelFlag
=
'
N
'
????????????
And
?tbContractOwner.CDelFlag
=
'
N
'
????????????
And
?tbOwner.CDelFlag
=
'
N
'
????????????
And
?tbContractRent.CEndDate?
Between
?
@StartDate
?
And
?
@EndDate
????????????
And
?tbContractRent.CPlaceCode?
Like
?
'
%@PlaceCode%
'
?????????上面SQL的意圖很明顯，是想將合約左聯中間表，再關聯到房東。但是改造的結果是失敗，失敗的原因很明顯，就是3表是逐步關聯起來的，只將合約與中間表實現了左聯，但與房東表的關聯卻沒有改動，現在重新改動一下。
--
陷阱2：
????
Select
????????????
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbContractOwner?
On
?tbContractRent.CContractId
=
tbContractOwner.CContractId
????????????
Left
?
Join
?tbOwner?
On
?tbContractOwner.COwnerId
=
tbOwner.COwnerId
????????
Where
????????????tbContractRent.CDelFlag
=
'
N
'
????????????
And
?tbContractOwner.CDelFlag
=
'
N
'
????????????
And
?tbOwner.CDelFlag
=
'
N
'
????????????
And
?tbContractRent.CEndDate?
Between
?
@StartDate
?
And
?
@EndDate
????????????
And
?tbContractRent.CPlaceCode?
Like
?
'
%@PlaceCode%
'
?????????這一次已經將3張表的關聯都改成了外聯，可是還是失敗，因為你無論怎麼查詢都會發現它與第1條SQL的內聯是等效的，為什麼會這樣呢？原因很簡單，在SQL語法中 Join 是連接關係，它會在 Where 子句之前完成，因此這條SQL的工作鎏程變成這樣：
?????????a.三表之間外聯 --> b.Where
?????????步驟a形成的外聯結果集是包含全部的 tbContractRent 記錄的，未連接上的 tbContractOwner，tbOwner 表記錄的全部字段的值都會是 Null ，再從這個記錄上選取 tbOwner.CDelFalg='N'...，無疑就將這些 Null 值的記錄給排除掉了，產生了與 Inner Join 等效的結果。
?????????原因說清楚，就好解決。首先看 CDelFlag = 'N'，這個條件，它在邏輯中屬於“前置”條件，應該在連接表時進行。
??? 解決方案：
????
Select
????????????
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbContractOwner?
On
?tbContractRent.CContractId
=
tbContractOwner.CContractId?
And
?tbContractOwner.CDelFlag
=
'
N
'
????????????
Left
?
Join
?tbOwner?
On
?tbContractOwner.COwnerId
=
tbOwner.COwnerId?
And
?tbOwner.CDelFlag
=
'
N
'
????????
Where
????????????tbContractRent.CDelFlag
=
'
N
'
????????????
And
?tbContractRent.CEndDate?
Between
?
@StartDate
?
And
?
@EndDate
????????????
And
?tbContractRent.CPlaceCode?
Like
?
'
%@PlaceCode%
'
??????說明：由於合約表是關聯的入口表(即 Left 的左表)，因此它的 CDelFlag='N' 可以放在後部實現篩選。
示例2：
??????邏輯：
?????????搜尋異常數據，查找有合約卻沒有對應房東的記錄，前置條件是該合約所在場所的 CIsUse='N'
???DML：
--
陷阱1：
????
Select
????????????tbContractRent.
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbPlace?
on
?tbContractRent.CPlaceCode
=
tbPlace.CPlaceCode
????????????
Left
?
Join
?tbContractOwner?
on
?tbContractRent.CContractid
=
tbContractOwner.CContractid
????????????
Left
?
Join
?tbOwner?
on
?tbContractOwner.COwnerid
=
tbOwner.COwnerid
????????
Where
????????????tbPlace.CIsUse
=
'
N
'
????????????
And
?tbContractOwner.CContractid?
is
?
null
?????????結果是失敗的，它並沒有按照設定的邏輯來正常工作。參照示例1中的解釋，問題應該是出在 tbPlace.CIsUse='N' 這個Where條件上，該條件將需要搜尋的數據全部給過濾出去了，那麼按照示例1的方法將該條件前置能夠解決問題嗎？
--
陷阱2：
????
Select
????????????tbContractRent.
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbPlace?
on
?tbContractRent.CPlaceCode
=
tbPlace.CPlaceCode?
And
?tbPlace.CIsUse
=
'
N
'
????????????
Left
?
Join
?tbContractOwner?
on
?tbContractRent.CContractid
=
tbContractOwner.CContractid
????????????
Left
?
Join
?tbOwner?
on
?tbContractOwner.COwnerid
=
tbOwner.COwnerid
????????
Where
????????????tbContractOwner.CContractid?
is
?
null
?????????條件前置，這是前例中的解決方案，但它不是萬能的，需要依據邏輯來判斷是否合適。在該示例中如果將 tbPlace.CIsUse='N' 條件前置，雖然可以先排除掉不符合該條件的 tbPlace 記錄，但是 Left Join 關鍵字卻會將它們重新給拉回來，只不過拉回來的結果是除了合約表的記錄外，其它字段全部為 Null 。正好完全符合下面的 tbContractOwner.CContractid is null 條件，很有欺騙性吧。
??? 解決方案：
????
Select
????????????tbContractRent.
*
????????
From
????????????tbContractRent?
Left
?
Join
?tbPlace?
on
?tbContractRent.CPlaceCode
=
tbPlace.CPlaceCode
????????????
Left
?
Join
?tbContractOwner?
on
?tbContractRent.CContractid
=
tbContractOwner.CContractid
????????????
Left
?
Join
?tbOwner?
on
?tbContractOwner.COwnerid
=
tbOwner.COwnerid
????????
Where
????????????tbContractOwner.CContractid?
is
?
null
????????????
And
?(tbPlace.CIsUse
=
'
N
'
????????????????
OR
?tbPlace.CPlaceCode?
is
?
null
????????????)
?????????這個有一點點復雜：根據邏輯 tbPlace.CIsUse='N' 這個條件是後置條件，應該放在後部，但是僅僅這樣處理就會產生陷阱1的效果，對應於合約的 Left Join 關聯，需要加入 OR tbPlace.CPlaceCode is null 來維護外聯的結果集。