1. Join 語法

1.簡介

T-SQL中的JOIN 這個語法的來源是関係化代數(由E.F.Codd於1970年時所提出),乃利用各種不同的聯結方式來從兩個集合中取出不同的資料，而在 T-SQL理面用的聯結方式包括:

• 自然聯結(INNERJOIN,即A ∩ B, 稱AB的交集)
• 左外聯結(LEFT OUTER JOIN,即A)
• 右外聯結(RIGHT OUTER JOIN,即B)
• 完全外聯結(FULL OUTER JOIN,即A U B,稱 AB的聯集)
• 交叉聯结或稱笛卡兒乘積(CROSS JOIN,即A x B,稱 AB 的積集)
  

左邊表格去 Join 右邊表格來獲得客戶名稱。
兩個表格用欄位: 客戶編號 來作關聯
一般保障交易檔資料全部能出現，所謂的交易檔指交易檔拿出來關聯的欄位是有可能重複的；這裡銷貨單表頭的客戶編號可能會重複，所以稱它為交易檔，但客戶主檔的客戶編號不會，所以稱主檔，一般左邊表格為交易檔，右邊表格為主檔，要保障的是交易檔資料要全部出現。
如圖: 銷貨單上的 F 在客戶主檔找不到跟我說沒有 (NULL) 就好，這筆交易不能不出現，這樣的案例使用 INNER JOIN

如果使用 RIGHT OUTER JOIN 來保障 客戶主檔 時，該不見會不見(銷貨單上的003)，該不想要的也會出現(客戶主檔上的編號B，從來沒有過交易出現在銷貨單表頭)，結論: 為保障 大華科技 要出現，所以多了一筆資料(銷貨單 003)

方向要確認，才不會出問題

1. INNER JOIN: 兩邊都有的資料才會出現。問題: 關聯後有些筆資料會遺失。
2. LEFT OUTER JOIN: 保障左邊(銷貨單表頭)所有資料一定要出現
3. RIGHT OUTER JOIN: 保障右邊(客戶主檔)所有資料一定要出現
4. FULL OUTER JOIN: 保障兩邊(銷貨單表頭、客戶主檔)所有資料一定要出現
5. CROSS JOIN: 左邊 X 右邊

JOIN 類型	概念說明	SQL 範例語法
1. INNER JOIN	兩邊都要有（只取交集）	SELECT * FROM 銷貨單表頭 A INNER JOIN 客戶主檔 B   ON A.客戶編號 = B.客戶編號;
2. LEFT OUTER JOIN	保障左邊所有資料一定要出現（右邊無對應則補 NULL）	SELECT * FROM 銷貨單表頭 A LEFT OUTER JOIN 客戶主檔 B   ON A.客戶編號 = B.客戶編號;
3. RIGHT OUTER JOIN	保障右邊所有資料一定要出現（左邊無對應則補 NULL）	SELECT * FROM 銷貨單表頭 A RIGHT OUTER JOIN 客戶主檔 B   ON A.客戶編號 = B.客戶編號;
4. FULL OUTER JOIN	保障兩邊所有資料一定要出現（無對應者補 NULL）	SELECT * FROM 銷貨單表頭 A FULL OUTER JOIN 客戶主檔 B   ON A.客戶編號 = B.客戶編號;
5. CROSS JOIN	交叉連接（笛卡兒積，左邊筆數 × 右邊筆數）	SELECT * FROM 銷貨單表頭 A CROSS JOIN 客戶主檔 B;

資料表範例

使用 商品基本資料表(Product) 關聯 存放 地點主檔資料表(Place)

一對多的關係，Product 是交易檔, Place 是主檔

SQL

-- 查詢存放地點資料表 Places
SELECT PlaceNo , PlaceName FROM Places ORDER BY PlaceNo
-- 查詢商品資料表 Products
SELECT ProductNo , ProductName , ProductSpec , PlaceNo FROM Products
ORDER BY ProductNo

【結果】

【說明】
如圖，針對兩個資料表都有找不到的資料。

• 在商品資料表中的 PlaceNo = 4，是沒有任何一地點編號是 04。
• 沒有任一個Product商品是放在 低溫 地點的。

交易檔是商品資料表(因可重複)，主檔是 存放地點，交易檔放左邊，主檔放右邊。

【步驟】
產生交易資料: 商品基本資料

SQL

SELECT A.ProductNo , A.ProductName , A.ProductSpec , A.PlaceNo 
FROM Products AS A
ORDER BY A.ProductNo

原: 商品資料表可以查到 10 筆資料

經過 JOIN 關聯:
交易檔(左邊) INNER JOIN 存放主檔(右邊)，透過關聯取得B.PlaceName

【結果】
掉了 3 筆資料 [D01、D09、D10]!

SQL

SELECT A.ProductNo , A.ProductName , A.ProductSpec , A.PlaceNo, B.PlaceName 
FROM Products AS A 
INNER JOIN Places AS B ON B.PlaceNo = A.PlaceNo
ORDER BY A.ProductNo
 
-- 查詢商品資料表 Products
SELECT ProductNo , ProductName , ProductSpec , PlaceNo FROM Products
ORDER BY ProductNo

【解決】
改用OUTER JOIN，因它有方向性，要保障完整交易紀錄(在左側)，就得使用 LEFT OUTER JOIN。

SQL

SELECT A.ProductNo , A.ProductName , A.ProductSpec , A.PlaceNo, B.PlaceName 
FROM Products AS A 
LEFT OUTER JOIN Places AS B ON B.PlaceNo = A.PlaceNo
ORDER BY A.ProductNo
 
-- 查詢商品資料表 Products
SELECT ProductNo , ProductName , ProductSpec , PlaceNo FROM Products
ORDER BY ProductNo

【結果】
有三筆存放地點名稱找不到 NULL，但是可以得到的是完整的10筆交易紀錄。

如果換成 FULL OUTER JOIN

左邊 10 個商品會出現，至少是 10 筆，右邊有一筆存放資料是沒有的，一定也要讓它出現共 11 筆。

SQL

SELECT A.ProductNo , A.ProductName , A.ProductSpec , A.PlaceNo, B.PlaceName 
FROM Products AS A 
FULL OUTER JOIN Places AS B ON B.PlaceNo = A.PlaceNo
ORDER BY A.ProductNo
 
-- 查詢商品資料表 Products
SELECT ProductNo , ProductName , ProductSpec , PlaceNo FROM Products
ORDER BY ProductNo

一定要讓 低溫 商品，顯示在交易紀錄查詢，造成多餘無用的資料現象。

INNER JOIN

取得資料表同時都擁有的部分，查詢時才會出現。

SQL 語法

SELECT Products.ProductNo , Products.ProductName , Products.ProductSpec, Products.PlaceNo, Places.PlaceName 
FROM Products INNER JOIN Places
ON Places.PlaceNo = Products.PlaceNo
ORDER BY Products.ProductNo

【結果】
少了三筆

LEFT OUTER JOIN

取左邊資料的全部。

1. 範例一
   取得商品基本資料表(Product)所有的資料列,不管有沒有設定存放地點代號(PlaceNo)或是存放地點代號是否有找到主檔設定。

   

   SQL 語法

   SELECT Products.ProductNo , Products.ProductName , Products.ProductSpec, Products.PlaceNo, Places.PlaceName 
   FROM Products LEFT OUTER JOIN Places
   ON Places.PlaceNo = Products.PlaceNo
   ORDER BY Products.ProductNo

   【結果】

   

2. 範例二
   找出與上面範例一相反的結果資料。
   取得商品基本資料表中未設定存放地點代號或找不到存放地點代號的資料。

   

   SQL 語法

   SELECT Products.ProductNo , Products.ProductName , Products.ProductSpec, Products.PlaceNo, Places.PlaceName 
   FROM Products LEFT OUTER JOIN Places
   ON Places.PlaceNo = Products.PlaceNo
   WHERE Places.PlaceNo IS NULL
   ORDER BY Products.ProductNo

   【結果】

   

FULL OUTER JOIN

保障有兩邊的資料。

1. 範例一
   取得商品基本資料表中所有的資料列,加上未設定存放地點代號或找不到存放地點代號的資料,
   

   SQL 語法

   SELECT Products.ProductNo , Products.ProductName , Products.ProductSpec, Products.PlaceNo, Places.PlaceName 
   FROM Products FULL OUTER JOIN Places
   ON Places.PlaceNo = Products.PlaceNo
   ORDER BY Products.ProductNo

   【結果】

   

2. 範例二
   找出與上面範例一相反的結果資料。
   取得商品基本資料表中未設定存放地點代號或找不到存放地點代號的資料。

   

   SQL 語法

   SELECT Products.ProductNo , Products.ProductName , Products.ProductSpec, Products.PlaceNo, Places.PlaceName 
   FROM Products FULL OUTER JOIN Places
   ON Places.PlaceNo = Products.PlaceNo
   WHERE Products.PlaceNo IS NULL OR Places.PlaceNo IS NULL
   ORDER BY Products.ProductNo

   【結果】
   找中間那塊，兩邊資料表都找不到的，都是PlaceName = NULL

   
   

CROSS JOIN

取得商品基本資料表所有的資料列對照每一筆存放地點的資料列的資料集合。
等同沒有 JOIN，沒有設關聯，結果會是 左邊資料 x 右邊資料

SQL 語法

SELECT * FROM Products
SELECT * FROM Places

【結果】
商品資料 10 筆，存放地點 3 筆， 10 * 3=30 筆。

【完整 SQL】

SQL 語法

SELECT Products.ProductNo , Products.ProductName ,
Products.ProductSpec , Products.PlaceNo , Places.PlaceName
FROM Products CROSS JOIN Places
ORDER BY Products.ProductNo

【結論】
少用，特殊用途。

---

使用 UI 產生 JOIN 指令

1. 進入新增查詢畫面
   資料庫 demodb 滑鼠右鍵 > 新增查詢
   

2. 於查詢視窗，滑鼠右鍵 > 在編輯器中設計查詢
   

3. 先找左邊資料表
   彈出視窗上，先選交易檔 Products，按下 加入
   

4. 在找右邊資料表
   彈出視窗上，選地點檔 Places，按下 加入 後，按下 關閉 視窗。
   
   

5. 查詢設計工具上，SSMS 工具，自動幫忙拉關聯，但這關聯不是我們要的，刪除它
   

6. 滑鼠點選左邊資料表關聯欄位 PlaceNo 後，不要放手拖曳致右邊資料表相同欄位後放手
   

7. 預設設為 INNER JOIN，菱形圖案。

8. 我們要的是左邊交易檔資料都要，所以點擊菱形位置後，在彈出視窗上選擇選取Product中所有的資料列
   
   

9. 如果要的是右邊地點檔資料都要，點擊菱形位置後，在彈出視窗上選擇選取 Places中所有的資料列
   

10. 兩邊若都選取會是FULL OUTER JOIN
    

11. 接著點取要顯示的欄位

    • 左邊
      ProductNo、ProductName、ProductSpec
    • 右邊
      PlaceName
      

12. 排序: 依照商品編號，遞增，所以
    

13. 按下 確定後，就幫忙生成 SQL 語句。

14. 若要再次修改 SQL 內容，只要選取整個 SQL，滑鼠右鍵選取 在編輯器中設計查詢 即可。