[ 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，含建置和查詢]