SQL語法 - TABLE JOIN

※ 何時該使用 JOIN？

JOIN 使用的時機是：當你需要同時查詢一張以上的資料表的時候。

※ SQL有哪些TABLE JOIN的方式？

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• SELF JOIN

※ 使用 JOIN 的時候，我們需要考慮到：

1. 我要使用哪一種 JOIN？
2. JOIN 資料表跟資料表之間的參考點是什麼？

※ 所謂的參考點：

舉例來說，就是 orders 資料表的 drink_id 會對應到 drinks 資料表的 id。

※ INNER JOIN是什麼？

簡單來說，就是「我有你也有」。

這張示意圖就是我們熟悉的集合論裡面的「交集」。

※ 語法

※ 解析順序

我們先從FROM跟INNER JOIN找到T1和T2，因為INNER JOIN 是用來結合兩個或多個表格的行，並返回符合指定條件（Join Condition）的所有行。這些被選出來的行會被結合成一個新的表格。在這個新的表格中，你可以選擇你想要的欄位。

※ 場景

它只會選出參考點上「兩者皆有」的資料，如果參考點對不上的話，這筆資料就不會被查詢出來：

SELECT orders.*, drinks.`name`
FROM drinks
JOIN orders
ON drinks.`id` = orders.`drink_id`;

這段指令可以看到：

1. FROM 是原本的資料表。
2. JOIN 是我們要加入查詢的另一張資料表。 （雖然在 INNER JOIN 中沒有差異，反過來也行）
3. ON 則是參考點。

在 SELECT 欄位的部分，我們不只寫了欄位名稱，連資料表名稱都寫上了，這是因為 JOIN 牽涉到多張資料表，有可能出現欄位一樣的情形。例如 drinks 有 name 代表「飲料名稱」，customers 也有 name 代表「顧客姓名」，只寫欄位名稱的話，會造成衝突，因此將資料表名稱也寫出來以免這種錯誤。

※ LEFT JOIN是什麼？

當我們使用 LEFT JOIN 時，我們會將兩個表格分成左表（left table）和右表（right table）。LEFT JOIN 會返回左表中的所有記錄，即使在右表中沒有匹配的記錄。這意味著即使左表中的某些記錄在右表中找不到對應的記錄，這些記錄仍然會出現在結果中，並且右表中對應的欄位會顯示為 NULL。

簡單來說，LEFT JOIN 會包含左表中的所有記錄，以及右表中能夠匹配的記錄。這與 INNER JOIN 不同，INNER JOIN 只會返回兩個表中都有匹配記錄的部分。

※ 語法

※ 解析順序

1. 識別表格：從 FROM 和 LEFT JOIN 中看到有 T1 和 T2，這兩個表格需要合併。
2. 區分表格：T1 是左表（left table），T2 是右表（right table）。
3. 檢查合併條件：查看合併的條件（JOIN condition），滿足條件的記錄會被合併進來。
4. 處理未匹配的記錄：如果左表（T1）中的某些記錄在右表（T2）中找不到對應的記錄，這些記錄仍然會出現在結果中，但右表（T2）中的欄位會顯示為 NULL。
5. 形成假想表：合併後形成一個包含 T1 和 T2 欄位的假想表，符合條件的記錄會被包含進來。
6. 選擇所需欄位：從假想表中選擇我們需要的欄位，得到最終結果。

※ LEFT JOIN範例：

1. 查詢所有客戶及其訂單，包含沒有訂單的某些客戶。

SELECT customers.customer_id, customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

2. ⽤ LEFT JOIN 找差集。

透過 LEFT JOIN 操作來找出存在在左表中，但不存在右表中的記錄。這對於查找缺失數據或進行數據清理時非常有用。

例如，假設有兩個表：customers 和 orders。我們想找出所有沒有訂單的客戶。可以使用以下查詢：

SELECT customers.customer_id, customers.name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

※ RIGHT JOIN是什麼？

當我們使用 RIGHT JOIN 時，我們會將兩個表格分成右表（right table）和左表（left table）。RIGHT JOIN 會返回右表中的所有記錄，即使在左表中沒有匹配的記錄。這意味著即使右表中的某些記錄在左表中找不到對應的記錄，這些記錄仍然會出現在結果中，並且左表中對應的欄位會顯示為 NULL。

簡單來說，RIGHT JOIN 會包含右表中的所有記錄，以及左表中能夠匹配的記錄。這與 INNER JOIN 不同，INNER JOIN 只會返回兩個表中都有匹配記錄的部分。

※ 語法

※ 解析順序

使用 RIGHT JOIN 時，右表（Right Table）的所有記錄都會包含在結果中。這個結果可以分成兩部分：

1. Inner Join 部分：這部分包含在左表（T1）和右表（T2）中找到滿足 JOIN 條件的記錄。
2. 右表獨有部分：這部分包含右表中無法與左表匹配的記錄。

總結來說，RIGHT JOIN 的特性是確保右表中的所有記錄都會出現在合併結果中。

※ RIGHT JOIN範例：

查詢飲料菜單。

SELECT orders.*, drinks.`name`
FROM orders
RIGHT JOIN drinks
ON drinks.`id` = orders.`drink_id`;

SELECT orders.*, drinks.`name`
FROM drinks
LEFT JOIN orders
ON drinks.`id` = orders.`drink_id`;

• 觀察一下結果，發現後面三種飲料因為沒有出現在 orders 中（也就是沒賣出半杯），因此 drinks.id 對應不到 orders.drink_id ，就只好將 orders 資料表的資料都填上 NULL 了。
• 這裡要注意的是，LEFT 跟 RIGHT 分別是指誰？LEFT 指的是 FROM 後面接的資料表，JOIN 後面指定的資料表一律是 RIGHT。
• 而 LEFT 跟 RIGHT JOIN 是可以互換的。

※ SELF JOIN是什麼？

SELF JOIN 是指在同一個表格中進行的 JOIN 操作。這不是一種新的 SQL 語法，而是使用現有的 JOIN 方式（如 INNER JOIN、LEFT JOIN 等）來合併同一個表格。

SELF JOIN 的主要用途是當一個表格中有多種角色或關係時，我們需要將這些角色或關係進行比較或關聯。使用 SELF JOIN 時，必須要給表格別名（Table Alias），這樣在 JOIN 條件和 SELECT 欄位的地方，才能區分到底是哪一個表格，SQL 才能正確地辨識出來。

※ 使⽤範例：

• 資料表有自己的參考值。例如說：公司的員工通常有「職位代理人」，就是在員工請假時，能夠有一個人來代理自己的業務。若這個欄位叫作 deputy_employee_id ，則我們可以用以下的指令：

SELECT e1.*, e2.name
FROM employee AS e1
JOIN employee AS e2
ON e1.deputy_employee_id = e2.id

查詢的結果將顯示每個員工（e1）的所有資訊，以及他們的「職位代理人」（e2）的名字。這樣可以幫助我們了解每個員工及其職位代理人之間的關係。

• 在同一張資料表中做比較。例如我們想要找到比「冰咖啡」便宜的飲料，就可以用以下指令：

SELECT d1.name, d2.name as cheaper_name, d2.price
FROM drinks as d1
JOIN drinks as d2
ON d1.price > d2.price
WHERE d1.name = '冰咖啡';

查詢的結果將顯示 d1 表格中名為 '冰咖啡' 的飲料，以及所有價格比 '冰咖啡' 便宜的飲料的名字和價格。這樣可以幫助我們找到所有比 '冰咖啡' 便宜的飲料。