學習原因：

上一篇文章主要都是對於單一 Table 的操作，然而 SQL Database 又稱為 Relational Database 有一個很重要的概念是建立 Table 之間的關聯性。這一篇文章主要是講述 Table 的關聯性，如何把多個 Table 的資料作整合，以取得需要的資料。

學習目標:

• 認識 Primary Key 與 Foreign Key
• 學習如何 Join Table 以取得所需要的資料

Primary Key 與 Foreign Key

1. Primary Key 主鍵： 作為 唯一標識 (Unique Identity) 每條記錄，就像是身份證號碼，會員編號，產品編號等代表的資訊。

   • 每個 Table 只會有一個 Primary Key
   • 主要特徵：
     • 是唯一值 （可以是沿用已有資料 ／ 創建一個流水號 ID )
     • 不能為空值 (NOT NULL)
   • 可以是單一欄位，也可以是由多個欄位組成 （組合鍵 Composite Key)

2. Foreign Key 外鍵： 表內的一個欄位，它會對應到另一個表的 Primary Key

   • 擁有 Foreign Key 的 Table，稱為 Child Table.
   • 擁有對應的 Primary Key 的 Table，視為 Parent Table.

以下是建立 2 個有關聯 Table 的例子

為 Person 和 Order Table 建立關係 ，每張 Order 必須屬於某位客人的，因此 Order Table 需要以 Customer 的 Customer ID 作關聯。

# 指定 CustomerID 為 Primary Key
CREATE TABLE Customer (
	customerID int NOT NULL,
	customerName varchar(45), 
	PRIMARY KEY (customerID)
);

# 指定 OrderID 為 Primary Key
# CustomerID 則為 Customer Table 的 Foreign Key
CREATE TABLE Orders(
	orderID int NOT NULL,
	orderAmount int NOT NULL,
	customerID int,
	PRIMARY KEY (orderID),
	FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

接下來，透過一個簡單例子講述 Foreign Key 的作用

一個學生成績記錄表的設計如下：

會發現這樣的設計，對於學生的個人資料會重覆出現，相對浪費空間資源，還會有可能出現資料不一致的問題。

因此會拆成多個 Table，再用 Foreign Key 作關聯 :

• Student Table

  用作存放個人資料，s_id 為 Primary Key

  s_id	name	s_birth	s_sex

  01 | 周雷 | 1990-01-01 | 男 |
  02 | 錢電 | 1990-12-21 | 男 |
  03 | 孫風 | 1990-05-20 | 男 |

• Subject Table:

  用作存儲科目資料，sub_id 為 Primary Key

  sub_id	subject

  01 | Chinese |
  02 | English |
  03 | Maths |

• Score Table：

  記錄學生每科的成績，s_id 和 sub_id 分別為 Student 和 Subject Table 的 Foreign Key

  s_id	sub_id	score

  01 | 01 | 80 |
  01 | 02 | 90 |
  01 | 03 | 60 |

  如果想在 Score Table 加入資料如下，會被拒絕。

  s_id	sub_id	score

  04 | 04 | 100 |

因為不存在 s_id 為 04 的學生，也沒有 sub_id 為 04 的科目，被視為 Invalid 的資料。

又如果想要刪除 Subject Table 的 Maths，同樣會被拒絕。

因為 Score Table 有關連到 Subject 而且有 Maths 這科的成績，若 Maths 被刪除，sub_id 為 03 這些記錄就會成為 孤兒 (orphan) 。

為了 避免一個 Table 有太多重覆性的資料 會拆成多個 Table，以 Foreign Key 作關連。

同時因為 Foreign Key 的資料一定要可以對應到 Parent Table 的 Primary Key，所以可以 約束錯誤的資料存在於 Foreign Key 欄位。

這就是 Primary Key 和 Foreign Key 的存在意義。

SQL Joins

為了減少資料的重複性，我們把資料分 Table 存放，所以在我們想取得全部資料的時候，又需要把資料組裝回來，因此會出現 SQL Joins，將兩個或多個表格中相關的數據進行結合的操作。

SQL Joins 有很多種

圖片來源：https://www.runoob.com/sql/sql-join.html

這個圖非常清楚解釋了 8 種的 SQL Joins，但我們比較常用的主要是 3 種 Inner Join, Left Join 和 Right Join，其他的有興趣再自己看下去喔。

將會使用以下例子作解釋：

Customers Table

Orders Table

1. INNER JOIN

   僅返回同時滿足 A 表和 B 表條件的記錄，用於查詢有關聯性的數據。

   INNER JOIN <B table> ON <A table column> = <B table column>

   # INNER JOIN 同時出現在 Customers 的 CustomerID 和 Orders 的 CustomerID 的資料
   # 取得 Customers Table 的 顧客名稱 和 Orders Table 的 訂單日期
   SELECT Customers.CustomerName, Orders.OrderDate
   FROM Customers
   INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
   

   Result:

   CustomerName	OrderDate

   Mary | 2023/09/01 |
   Mary | 2023/09/12 |

   僅回傳 CustomerID 同時出現在 Customers 和 Orders 的資料，所以沒有下單的客戶資料是不會被查出來

2. LEFT JOIN

   以 A 表為主，一定會返回 A 表中所有的資料，而 B 表若有能匹配的資料會被一同被返回。

   LEFT JOIN <B table> ON <A table column> = <B table column>

   SELECT Customers.CustomerName, Orders.OrderDate
   FROM Customers
   LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
   

   Results:

   CustomerName	OrderDate

   Mary | 2023/09/01 |
   Mary | 2023/09/12 |
   Sam | Null |

   顧客列表有 Mary 和 Sam，而 Order 的只有 Mary 的資料，沒有 Sam，因此相關的資料為 空值 (Null)

3. RIGHT JOIN

   以 B 表為主，一定會返回 B 表中所有的資料，而 A 表若有能匹配的資料會被一同被返回。

   RIGHT JOIN <B table> ON <A table column> = <B table column>

   SELECT Orders.OrderDate, Customers.CustomerName
   FROM Orders
   RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
   

   Results:

   OrderDate	CustomerName

   2023/09/01 | Mary |
   2023/09/12 | Mary |
   null | Sam |

   Order 有 3 筆資料，其中 2 筆是屬於 Mary 的，另 1 筆沒有對應的顧客。沒有顧客的顧客資料則為空值。

今天主要讓你知道：

• 為了避免浪費儲存的空間，不用一個大 Table 把所有資料湊在一起，而製作關聯的 Table。
• 為了讓人了解 Table 之間的關係，應用了 Primary Key 和 Foreign Key，這可避免在新增／ 刪除資料的時候產出錯誤的資料。
• 由於資料被分開存放，所以需要取得所需資料，需要應用 SQL Joins 把 Table 的資料整合。