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