今天要來分享的算是在寫查詢語法時的小小觀念
很不巧小弟也是最近才知道當中的差別(看來以前亂用居然都沒出亂子，真是好險阿)
題目
多張資料表JOIN後將條件式寫在ON以及WHERE的差別
使用範例
DECLARE @Customer TABLE( CustID int primary key,CustName nvarchar(10) )
INSERT INTO @Customer VALUES(1,'A-Customer'),(2,'B-Customer'),(3,'C-Customer'),(4,'D-Customer')
DECLARE @Order TABLE( OrdID int ,CustID int,ProdID int,Qty int,Price int )
INSERT INTO @Order VALUES(1,1,1,10,100),(2,1,3,5,300),(3,1,4,20,70),(4,2,2,5,500),(5,2,3,10,250),(6,3,1,30,90),(7,3,2,15,450),(8,3,3,10,280)
WHERE[條件式]
這部分應該連初學者也都知道，在WHERE後面所加註的所有條件式
是將所選取的資料表(或子查詢)內的資料集合，根據條件式進行過濾的動作
而在多張資料表進行JOIN後，再做WHERE條件式
則是將資料表串連起來後的資料集合整個進行過濾的動作
ON[條件式]
ON是出現在JOIN後方，將兩個資料表進行串接的子句
一般比較常用 A表 join B表 on A.ID=B.ID ，用ON來說明A、B兩表示怎麼進行連結的
但有時候有些人會直接在ON後面加其他的條件式進行過濾的動作
但此時則跟WHERE的效果不一樣
舉例來說 A表 join B表 on A.ID=B.ID and B.Date!=2014
這時寫在ON後面的B表Date為2014的資料將會被過濾掉
注意！事先過濾掉B表內的資料再進行連結
舉例
根據上面所建立的資料表，我們想要知道各個顧客購買ProdID=3這樣產品的消費總金額為多少
- 過濾前
SELECT C.CustID,C.CustName,SUM(O.Qty*O.Price) Amount
FROM @Customer C left join @Order O on C.CustID=O.CustID
GROUP BY C.CustID,C.CustName
ORDER BY CustID
可以看到D-Customer因為沒有Order對應的資料所以後面的欄位顯示NULL
- 過濾後(WHERE)
SELECT C.CustID,C.CustName,SUM(O.Qty*O.Price) Amount
FROM @Customer C left join @Order O on C.CustID=O.CustID
WHERE O.ProdID=3
GROUP BY C.CustID,C.CustName
ORDER BY CustID
這時我們會發現，顧客消費總額有計算出來，但是D-Customer的資料不見了
這是因為再連結資料才過濾剩下ProdID=3的資料列
但一般情形我們會希望能夠保留下所有的顧客資訊以便程式端做處理或顯示
改用
- 過濾後(ON)
SELECT C.CustID,C.CustName,SUM(O.Qty*O.Price) Amount
FROM @Customer C left join @Order O on C.CustID=O.CustID and O.ProdID=3
GROUP BY C.CustID,C.CustName
ORDER BY CustID
這時候我們可以發現D-Customer的資料列保留下來了
而後續我們還是會知道有此名顧客而不會遺漏掉
總結
再多張資料表進行連結時（不包含INNER JOIN)，如果要再進行過濾的動作
過濾條件式寫在ON後面，代表是先針對連結前的指定資料表進行過濾
過濾條件寫在WHERE後面，代表是對連結後取得的資料集合再進行過濾
留言列表
