[ SQL ] JOIN in T-SQL

最近在某神祕專案裡常常會看到現有的Code在抓報表資料的時候，會讓資料庫一次、一次、一次地取大量資料，然後在server-side程式裡做轉換、統整、用LINQ查詢、統計以後再輸出給client端。
這樣的做法雖然在程式的閱讀上會很明確，哪個欄位代表什麼意義或過濾什麼條件都會很清楚，但是資料量超過一定程度以後，每一次從資料庫取出來的資料量本身就是一個負擔，再加上把資料做型態上的轉換、不斷宣告新物件裝資料、再用LINQ做查詢整合，從下命令到得到結果中間時間會變得非常久。

LINQ很方便，可以用，但不要過度地依賴。把SQL Script的基本功打好，一次把要所需的資料全部精準地過濾、計算，回傳到server-side程式的時候就是一個完整的結果，再來就只要輸出就行了，雖然可能中間會JOIN好幾張表，但只要Index建好，查詢效率就不會太差。

一個案子裡的程式開發人員能力有高有低，不是每個人都能夠寫出DBA等級的查詢句，再加上時程壓力，很多開發人員都會不願意動腦子想SQL該如何下比較好，而是急著把成果生出來而以最直覺的方式去下指令，然後就會寫出在迴圈裡把資料庫的連線開開關關，然後抓出來的資料還要轉成特別的物件再用LINQ去下過濾條件的程式，按下查詢之後可能天都黑了才生出報表（前提是不會跑出奇奇怪怪的Exception）。

這樣的情況一路開發到後期，或是再下一期兩期，後面的每一隻程式都是複製貼上，大部分的查詢都是用同樣的方式在開開關關建建放放，系統負擔不大才怪。

根據這樣的假設和跳躍式思考，我認為如果能夠活用各種JOIN的方式，把所需的資料一口氣全找出來，能夠減少資料庫的負擔，進而增加系統處理的效能。

聽說這篇原本是要寫JOIN？

--廢話終於講完了的分隔線--

簡單來說，JOIN就是把兩個集合（資料表），透過不同的方式得到不同的集合。而在T-SQL（SQL Server）中，常用到的JOIN方式不外乎那幾種：

1. INNER JOIN：取出來的資料為兩個集合都有的資料。
2. LEFT (RIGHT) OUTER JOIN：以左邊（或右邊）的集合為基底，查詢與另一張表相對應的欄位。
3. FULL OUTER JOIN：左外關聯和右外關聯的聯集。
4. CROSS JOIN ：兩個集合的每一筆資料都會被取出來。

來用範例讓這中間的差異變得更明顯。

---

現在，我們有一些資料表和資料....

這是會員，裡面只有簡單的ID、姓名和職業，其中職業是以代碼來表示，來源是另一種表。

在這裡，我特別設計了一個會員，假設他用了一些特殊的方式寫入這個系統，使他允許在「職業」那一欄沒有輸入東西。

職業，就只有ID和名稱而已，超級基本的代碼表。

產品，內容有ID、名稱、類型代碼和庫存量，和會員那裡一樣的是：類型代碼的來源也是另一張表。

老梗的代碼表，這是產品類型。

我的例子還需要一些「交易記錄」才完整，長得就像上面一樣。我要記錄的資料並不多，只要知道「哪個會員在什麼時間買了什麼東西，買了幾個」就可以了。所以設計出來就如圖所示。
會員就存會員ID、產品就存產品ID、交易時間就存寫入資料表當下的系統時間，還有一個整數的數量。

就這樣，我們就建好了簡單的環境，再來就是仔細的看一下各種JOIN方法會呈現什麼結果。

---

INNER, LEFT, RIGHT JOIN：

假設今天我要找出會員及其職業名稱，那我可以這樣下句子，並且得到以下的結果.....

我也可以這樣下....

我還可以這樣下....

差異在哪裡？

INNER JOIN所抓出來的，會是JOIN左右兩邊資料表都有互相關聯的資料。這個環境裡，沒有會員是「無業」，也有一個戳戳的傢伙沒有輸入職業，這兩種東西就不會顯示出來。

LEFT JOIN就是LEFT OUTER JOIN。它就會以JOIN左邊的Member當底，然後把JOIN右邊的Job相對應的資料撈出來顯示。重點來囉！JOIN左邊的Member資料，只要沒有下特別的條件去過慮，那每一筆資料都會被取出來，即使Job裡沒有相對應的欄位，它也會弄一筆資料出來，空的欄位就用null來表示。

RIGHT JOIN和LEFT JOIN不一樣的地方，只有方向而已，把上面那段的左邊改成右邊就行了。範例就是…沒有無業的人，它還是會把無業抓出來，然後把前面的欄位放null。

還有一種特別的JOIN叫作FULL OUTER JOIN，其實就是....LEFT JOIN和RIGHT JOIN有出現過的資料全放一起就對了，專業的名詞叫作「聯集」。瞧，例子在下面。LEFT JOIN和RIGHT JOIN有出現過的資料全出現在下面。

CROSS JOIN：

說起來CROSS JOIN是一種滿特別的JOIN方式：它不管JOIN左右兩邊資料表的資料有沒有任何關聯，它都會把每一組配對組合資料都顯示出來。下面那張圖，得到的結果就是職業和產品類型的各種配對。

「這種沒有關聯的兩張表，配對出來的結果有什麼用？」

如果你想知道「各種職業購買各種產品的記錄筆數總和」之類的東西，這就有用了。

如此一來，我就不用在程式裡跑迴圈下查詢查到死。

--好睏哦....--

善用JOIN，你的人生將會一片光明 (超敷衍)。

至少在面對統計報表或是神祕的實體關係時比較不會有所畏懼。

是的，我最近在搞報表，快被搞死了。


[範例Code，含建置和查詢]

[ SQL ] GROUP BY, HAVING

GROUP BY
如果有需求，是要得到某某分類的資料加總起來的數字是多少，可以利用GROUP BY和SUM函式輕鬆得到解法。
比方說，一個資料表裡面有很多各種顏色的椅子，每張椅子都有各自的價錢，那麼我想知道各種顏色椅子的總價...

SELECT chair.color, SUM(chair.price)
FROM chair
GROUP BY chair.color

HAVING
這個語法要跟上面的GROUP BY一起使用。如果要把上述查詢出來的結果再進一步去過濾，就可以利用這個語法設定條件（概念上有點像WHERE)。
承上述例子，我想知道各種顏色椅子總價在30000以上的結果，那麼可以....

SELECT chair.color, SUM(chair.price)
FROM chair
GROUP BY chair.color
HAVING SUM(chair.price) > 30000

這個語法也可以用於過濾文字....

SELECT chair.color, SUM(chair.price)
FROM chair
GROUP BY chair.color
HAVING chair.color = 'Red'

[ C# ] BeginTransaction, Rollback and Commit

C#指令版
http://blog.yam.com/kosaten/article/13648132
http://my.so-net.net.tw/idealist/CS/Basic/transaction.html
SQL指令版
http://msdn.microsoft.com/zh-tw/library/ms181299.aspx
不曉得你們看不看得到這些連結
簡單來說，SQL裡有一個概念叫Rollback，大陸那裡好像翻作「回滾」，這個動作能夠讓資料庫的狀態回到被定義開始交易之前。
SQL有這個指令，C#也有類似的元件可以操作。詳細的操作方式連結裡面都有。
我的想法是，在進行大量修改和寫入(UPDATE和INSERT)的時候，為了避開伺服器中斷而導致輸入資料不齊全的問題，有必要加上這個機制來躲過這個危機。SELECT並不會更動到資料表的狀態，所以不需要。那只有一筆的寫入或更新，我還在考慮要不要使用這個動作，因為沒有經驗和資訊指出這個動作會不會對資料庫的效能和負擔造成影響。
另外，我還不太會寫SP，所以這裡應該會是在C#來實現這個概念。

如果看不到上述的連結，請跟我說一下，我把裡面的資料摘錄下來再另外公佈。

=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:


好了，我說明一下我這裡測試的方法和結論。
方法：
1. 試著把新資料寫進資料表裡，然後再rollback回去，看能不能回到交易前的狀態。結論是可以。
2. 試著把新資料寫進資料表裡，然後再commit。看交易結束之後資料有沒有留著。結論是也可以。
3. 寫入資料之後的指令加入中斷點，看看rollback前和rollback後的結果。結果是發現，我在中斷點的時候去開SQL Server management studio直接敲select指令抓資料，是沒辦法抓的，指令會一直跑跑跑沒有回應。我想應該是資料庫把狀態鎖起來了，除了該連結之外，其它連結不被允許進入資料表進行存取。
4. 試著把新資料寫進資料表裡，然後把SQL server的服務徹底關掉，程式完成之後再打開，看會發生什麼事。因為沒有用try-catch，所以瀏覽器上是會顯示錯誤畫面，但是資料庫的狀態是在交易開始之前，也就是資料沒有被寫入。

結論：
這個方法確實可以達到我們一開始的想法：避開在進行資料大量寫入或修改的時候，資料庫伺服器當機或中斷所引發的資料有多有少的情況。
適度地使用try-catch-finally，就可以實現這個概念。

SqlConnection sqlConn = DB.Conn();
sqlConn.Open();
SqlTransaction sqlTrans = sqlConn.BeginTransaction();
SqlCommand sqlComm.Connection = sqlConn;
sqlComm.Transcation = sqlTrans;

sqlConn = DB.Conn();
sqlConn.Open();
sqlTrans = sqlConn.BeginTransaction();
sqlComm.Connection = sqlConn;
sqlComm.Transcation = sqlTrans;
try{
	for(;;){
		strInsertion = "INSERT INTO xxx (a, b, c, d) VALUE ('A', 'B', 'C', 'D')";
		sqlComm.CommandText = strInsertion;
		sqlComm.ExcuteNonQuery();
	}
	sqlTrans.Commit();
}
catch{
	sqlTrans.Rollback();
}
finally{
	sqlConn.Close();
}

=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:=:

2011/08/20　新增關於BeginTransaction, Rollback, Commit的測試：
問題：在寫入資料表的時候關掉網頁，對資料表的狀態？
測試方式：
寫一隻無限迴圈程式，不斷讓程式對資料表寫不同的資料，然後在執行的過程中關掉網頁。
測試結果：
一開始寫程式的時候沒有用try-catch-finally，所以直接把網頁關掉，資料表會被lock起來，強制把IDE的模擬Server程式關掉後才得以解開。資料沒有寫進去。
後來加入try-catch-finally後，執行的過程把網頁關掉，再去查詢資料表，是可以運作的。資料也沒有寫進去，
由測試結果的推論：
try-catch-finally，是去執行try裡面的動作，遇到Exception的時候再強制跳到catch，無論try完或是catch完，最後都執行finally。直接把網頁關掉，也能夠用這個方式catch出來。
SQL的做法應該是在先把資料表的狀態lock住，然後對資料表的修改和寫入都是寫到一個暫存的地方，等到下commit指令以後，才把結果一次倒進資料表裡。
總之，今天討論到，如果程式執行很久，使用者在不知情（或是手賤）的情況下關掉網頁，以這種做法來說，不用擔心資料表會被持續lock住。