Posted on 2007-01-19 12:47
大大毛 阅读(272)
评论(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 來維護外聯的結果集。