歡迎回來 [SQL 入門與面試實戰系列] ！
在前面章節中，我們學習了基本的查詢語法、WHERE 篩選條件，以及使用 GROUP BY 搭配聚合函數，對資料表進行更細維度的分析。前面章節我們主要都圍繞在單一資料表上進行分析。如果我們希望把不同資料表的資料結合在一起，進行更全面的查詢和分析，這時候就需要用到 SQL 的超級功能：JOIN！
[SQL 入門與面試實戰系列] 3_JOIN.ipynb
- JOIN 的基本概念
- LEFT JOIN 和 INNER JOIN 的差異
- 連接多張資料表
- JOIN 的基本概念
在實際商業資料庫中存放著多張資料表，每張表存儲著特定類型的資料。以我們使用的 chinook.db 為例，customers 表儲存客戶的個人資料，而 invoices 表則儲存交易記錄，每張表都有其特定的用途。當我們需要查找客戶資料時，我們查詢 customers 表；需要了解購買資訊時，我們則查詢 invoices 表。
但在某些情況下，我們需要回答更複雜的問題，比如了解「特定顧客」 (來自 customers 表) 的「購買歷史」 (來自 invoices 表)。此時我們需要使用 JOIN 操作，將這兩個包含相關信息的表「連接」起來。
我們先查看這兩張表的內容：
SELECT * FROM customers
LIMIT 5
SELECT * FROM invoices
LIMIT 5
要怎麼將這兩張表連接起來？我們要尋找這兩張表共同的欄位。我們可以發現這兩張表的共同欄位是 CustomerId，且欄位都代表顧客的唯一 ID。因此，CustomerId 就可以作為連接欄位。
JOIN 的基本語法如下：
SELECT *
FROM "資料表1"
JOIN "資料表2" ON "資料表1"."連接欄位" = "資料表2"."連接欄位"首先我們寫一個基本的查詢語句，然後加上 JOIN 和要連接的第二張表的名稱；並透過 ON 指定兩張表之間的連接欄位，寫法是在連接欄位前面加上資料表名和 “.” (表示哪張資料表的欄位)。這樣就能將兩張表的資料合併在一起。
現在，讓我們嘗試將 customers 表和 invoices 表連接起來：
SELECT *
FROM customers
JOIN invoices ON customers.CustomerId = invoices.CustomerId查詢的結果會顯示一個完整的合併表：左邊是 customers 表的資料 ( CustomerId 到 SupportRepId 欄位)，右邊是 invoices 表的資料 (InvoiceId 到 Total 欄位)。我們用 CustomerId 作為連接點，能夠看到每位客戶的會員編號、名字等個人資料，以及他們相對應的購買記錄。
現在連接的查詢表有太多欄位，我們選擇需要的欄位就好。因為查詢涉及兩張表，為了避免混淆，尤其是當連接的兩張表中有相同名稱的欄位時，最好明確指出每個欄位來自哪張表 (在沒有同名欄位的情況下，明確指明來源表仍然是個好習慣)。寫法如下：
SELECT "資料表1"."欄位名", "資料表2"."欄位名"
FROM "資料表1"
JOIN "資料表2" ON "資料表1"."連接欄位" = "資料表2"."連接欄位"假設我們想知道每位顧客的姓名與會員編號，以及他們的每筆訂單編號、訂單日期與消費金額，可以這麼寫：
SELECT
customers.CustomerId,
customers.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total
FROM customers
JOIN invoices ON customers.CustomerId = invoices.CustomerId這段 SQL 查詢的意思是：我們從連接後的表中，選取了 customers 表的 CustomerId 和 FirstName 欄位，以及 invoices 表的 InvoiceId、InvoiceDate 和 Total 欄位。這樣我們就可以清楚地看到每位顧客的基本資料和他們相關的購買記錄。
以上就是 JOIN 的基本概念！當我們想連接兩張資料表時，需要考慮以下幾個關鍵因素：
- 尋找兩張表共有的欄位：一般來說，如果兩張表中的欄位名稱相同，那麼這些欄位通常在業務上具有相同的意義。例如
customers表與invoices表中，CustomerId都是指會員編號，可以作為連接欄位。然而，有時即使兩個欄位在業務上意義相同，它們在不同表中的名稱可能不同 (例如可能在customers表中，會員編號的欄位名稱為Id)。這時候我們就需要確認這些欄位在業務邏輯上是否相對應 (比如 customers.Id = invoices.CustomerId)。 - 思考兩張表連接的意義：即使兩張表有共同欄位可以連接，也需要考慮它們連接起來是否能回答特定的業務問題。例如將
customers表 (客戶資料) 和invoices表 (購買記錄) 連接起來，可以讓我們了解每位客戶的購買歷史，幫助我們分析客戶的購買行為。 - 確認連接欄位是否有唯一性：眼尖的朋友可能會發現，
customers表的Address欄位與invoices表的BillingAddress在數據上是一樣的。那為何我們不使用地址欄位作為連接點呢？因為在customers表中，我們能確定每一筆客戶資料，都只會對到一個獨特的CustomerId(我們稱CustomerId為customers表的主鍵)。這種唯一性確保當我們使用會員編號欄位，每條客戶資料與購買記錄之間都能正確匹配。假設有顧客共享相同的地址，那我們使用地址進行連接，就可能將不同的客戶資料混淆。因此在進行表連接時，通常選擇某張表的主鍵作為連接欄位，以確保數據匹配的準確性。
- LEFT JOIN 和 INNER JOIN 的差異
在 SQL 中連接兩張表有幾種不同的方式，也就是不同類型的 JOIN。其中我們最常用的類型是 LEFT JOIN 和 INNER JOIN。
LEFT JOIN：返回左邊資料表（主表）的所有記錄，以及右邊資料表中與之相匹配的記錄。如果右邊的資料表中沒有匹配的記錄，則會以空值 (NULL、NaN) 來填充這些欄位。LEFT JOIN 適合用於想要保留左表中所有記錄，即使部分記錄在右表中沒有對應的情況。
INNER JOIN：只返回兩個表中都存在匹配的記錄。如果一個表中的記錄在另一個表中沒有找到匹配，那麼這條記錄就不會出現在最終的連接表。INNER JOIN 適用於當我們只關注兩個表中都有對應記錄的情況。
至於 RIGHT JOIN 和 FULL JOIN，基本上不會使用到。RIGHT JOIN 是 LEFT JOIN 的鏡像版本 (以右表為主表)，由於我們閱讀資料表是由左至右，通常我們會選擇將主表放在左側並使用 LEFT JOIN。而 FULL JOIN 則會返回兩個表中的所有記錄，包括沒有匹配的記錄，因此可能產生大量包含空值的記錄，這在實際應用中會導致連接結果過於複雜，不適合大多數分析需求。
現在，我們一樣透過連接顧客資料表與訂單資料表，來比較 LEFT JOIN 和 INNER JOIN 之間的差異，更好地理解它們各自的應用場景。
LEFT JOIN：
我們想要查詢每位顧客是否曾經購買過產品，包括那些已註冊但還沒有購買的新會員。即使某些顧客在訂單資料表中沒有記錄，我們也想看到他們的信息。假設新增了一位名叫 Brian 的會員，會員編號是 60，且他還沒有進行任何購買。我們使用 LEFT JOIN 來連接新的顧客資料表 customers_new 和 invoices 表：
-- 建立包含所有顧客及未購買會員 Brian 的臨時表 (可以先不用理解這段)
WITH customers_new AS (
SELECT CustomerId, FirstName
FROM customers
UNION
SELECT '60' AS CustomerId, 'Brian' AS FirstName
)
SELECT
customers_new.CustomerId,
customers_new.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total
FROM customers_new
LEFT JOIN invoices ON customers_new.CustomerId = invoices.CustomerId這個查詢會顯示所有顧客的信息，包括沒有購買記錄的 Brian。由於 Brian 沒有購買記錄，所以在 InvoiceId、InvoiceDate 和 Total 這些欄位資料為空值。
進階應用，實務中我們經常透過 WHERE 條件，篩選出未曾購買的會員 (右表欄位為空值)，寫法如下：
SELECT
customers_new.CustomerId,
customers_new.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total
FROM customers_new
LEFT JOIN invoices ON customers_new.CustomerId = invoices.CustomerId
WHERE invoices.InvoiceId IS NULL
INNER JOIN：
假設我們只對曾經購買過的顧客感興趣，也就是在 invoices 表中至少有一筆購買記錄的顧客。我們則使用 INNER JOIN 來連接：
-- 建立包含所有顧客及未購買會員 Brian 的臨時表 (可以先不用理解這段)
WITH customers_new AS (
SELECT CustomerId, FirstName
FROM customers
UNION
SELECT '60' AS CustomerId, 'Brian' AS FirstName
)
SELECT
customers_new.CustomerId,
customers_new.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total
FROM customers_new
INNER JOIN invoices ON customers_new.CustomerId = invoices.CustomerId這個查詢只有那些在 invoices 表中有購買記錄的顧客，才會出現在連接結果。沒有購買記錄的顧客，比如 Brian，將不會被包含在內。
總結來說，當我們想要查詢包含所有左邊表格的資料，即使它們在右邊表格中沒有對應的資料，就應該使用 LEFT JOIN。如果我們只關心那些在兩個表格中都有對應資料的情況，就選擇使用 INNER JOIN。
- 連接多張資料表
當我們需要回答涉及多個資料層面的業務問題時，經常需要連接多張表。例如，我們想探究所有有購買紀錄的顧客，訂單購買了哪些產品 (包括產品名稱)。我們除了連接顧客資料表與訂單資料表，還需要連接訂單明細表 (invoice_items) 與商品表 (tracks) 才能知道顧客買了哪些產品。此外，我們需判斷要使用 LEFT JOIN 還是 INNER JOIN，並選擇適當的連接方法。
讓我們來認識兩張新的資料表 invoice_items 與 tracks ：
invoice_items 資料表記錄了每筆訂單的詳細購買項目，也就是訂單明細。主要欄位包括：
發票編號 (InvoiceId)：標明每項購買屬於哪一個發票 (可以與 invoices 表連接)
歌曲編號 (TrackId)：代表購買的產品，即每首歌曲的唯一識別碼 (可以與 tracks 表連接)
產品單價 (UnitPrice)：一件商品的售價
購買數量 (Quantity)：表示顧客在該筆訂單中購買的商品數量。
SELECT * FROM tracks
LIMIT 5
tracks 資料表則記錄了所有歌曲的詳細資訊，相當於產品目錄表。主要欄位包括：
歌曲編號 (TrackId)：每首歌曲的獨特識別碼
歌曲名稱 (Name)：該首歌的歌名
專輯編號 (AlbumId)：指出該首歌曲收錄於哪一張專輯
作曲家 (Composer)：該首歌的作者
產品單價 (UnitPrice)：該首歌的售價
SELECT * FROM tracks
LIMIT 5
以下是我們連接多張資料表的思考步驟：
customers與invoices：首先通過顧客編號 (CustomerId) 欄位將customers表和invoices表連接起來，找到有購買紀錄顧客的所有訂單資訊。invoices與invoice_items：接著使用發票編號 (InvoiceId) 欄位將invoices表和invoice_items表連接，獲得每筆訂單的具體購買項目。invoice_items與tracks：最後通過歌曲編號 (TrackId) 將invoice_items表和tracks表連接，知道每個購買項目對應的歌曲詳情，如歌曲名稱和作曲家。
此外，我們選擇用 INNER JOIN 連接，以確保我們得到的資訊從顧客身份 (誰購買了)，到具體的購買細節 (他們購買了什麼，以及產品的具體信息) 都是完整匹配的。 完整的連接語法如下：
SELECT
customers.CustomerId,
customers.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total,
invoice_items.UnitPrice,
invoice_items.Quantity,
tracks.TrackId,
tracks.Name AS TrackName,
tracks.Composer
FROM customers
INNER JOIN invoices ON customers.CustomerId = invoices.CustomerId
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId通過這樣的查詢，我們不僅能夠識別出有購買紀錄的顧客，還能了解他們購買了哪些具體的產品與相關資訊。
現在讓我們運用前兩章學到的知識進行一次綜合應用！假設我們想找出曾經購買過產品編號在 1 到 10 之間的顧客，可以透過在查詢的末尾加上 WHERE 條件進行篩選：
SELECT
customers.CustomerId,
customers.FirstName,
invoices.InvoiceId,
invoices.InvoiceDate,
invoices.Total,
invoice_items.UnitPrice,
invoice_items.Quantity,
tracks.TrackId,
tracks.Name AS TrackName,
tracks.Composer
FROM customers
INNER JOIN invoices ON customers.CustomerId = invoices.CustomerId
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId
WHERE tracks.TrackId BETWEEN 1 AND 10
再來，如果我們想篩選出那些購買 25 張以上不同專輯的顧客，大家可以思考一下如何透過 GROUP BY 和 HAVING 來進行篩選：
SELECT
customers.CustomerId,
customers.FirstName,
COUNT(DISTINCT tracks.AlbumId) AS AlbumCnt
FROM customers
INNER JOIN invoices ON customers.CustomerId = invoices.CustomerId
INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId
GROUP BY customers.CustomerId, customers.FirstName
HAVING AlbumCnt >= 25
ORDER BY AlbumCnt DESC這個查詢會將顧客分組，計算每位顧客購買不同歌手的數量，並只顯示那些購買超過 25 位不同歌手歌曲的顧客。
以上！就是 JOIN 的完整介紹！讓我們複習一下這章所學到的內容：
- JOIN 的基本概念：尋找共同欄位來將兩張資料表連接起來，以獲得更全面的資訊。
- LEFT JOIN 和 INNER JOIN 的差異：當我們想要保留左表的所有紀錄，就使用 LEFT JOIN，這時候右表沒有匹配到的欄位會填入空值。而如果我們只關注兩張表都有共同存在的記錄，則使用 INNER JOIN。
- 連接多張資料表：我們可以將多個表連接起來，形成一個大型資料集。記得選擇合適的 JOIN 類型來滿足查詢需求。
大家可以參考下面的 ER 圖，試著連接看看 chinook.db 的其他資料表。比如我們可以再透過 AlbumId 欄位，連接 tracks 表和 albums 表，來看看顧客購買歌曲的專輯資訊。也可以試著調整連接表的順序，觀察結果有何不同。
不過實際工作中，不會有好心人幫我們做這麼貼心的 ER 圖。所以我們在寫 SQL 前，還是要先徹底了解資料表的內容是什麼。理解每張表的結構和主鍵，幫助我們能夠準確地建立表間的關聯。
操作 SQL 時，我們對各張資料表之間共同欄位的直覺很重要。常見的欄位包括顧客編號、訂單編號、商品編號、活動 ID 等，這些會在面試實戰章節有更深入的介紹。也建議各位朋友自己出題，例如查詢特定作曲者的所有專輯、找出購買特定商品最多的顧客等等，徹底了解 JOIN 的使用方式，以及如何搭配篩選條件、聚合等函數。
最後，我們再複習一下前三章所學的內容：
第一章：使用基本查詢語法叫出資料表，並透過 WHERE 設定條件篩選資料
第二章：透過 GROUP BY 對資料進行分組，搭配聚合函數（如計算總和、平均值等）進行更細維度的分析
第三章：透過 JOIN 串接多張表，讓查詢表擁有更多資訊；搭配 WHERE 與 GROUP BY 等函數，回答更複雜的業務問題
恭喜各位已經掌握了 SQL 的核心概念與基礎函數！！這些概念就如同數學中的加減乘除，已經能幫助我們解決大多數的 SQL 問題，差別只是在問題的複雜度以及查詢語句的長度。
後續的章節會是比較進階的函數，等把進階函數學完，我們就可以應對面試考題了！
[補充]
另外可能有朋友看過 JOIN 進行表連接後，在 ON 子句設定條件的寫法。例如我們想篩選顧客編號為 1 的購買紀錄：
SELECT customers.CustomerId, invoices.InvoiceId
FROM customers
INNER JOIN invoices ON customers.CustomerId = invoices.CustomerId
AND customers.CustomerId = 1在這個查詢中，”customers.CustomerId = 1” 被包含在了 INNER JOIN 的過程中。也就是說除了共同欄位的條件，還要滿足顧客編號為 1 的記錄才會進行連接。
在使用 INNER JOIN 的情況下，將條件放在 ON 子句會與放在 WHERE 子句得到的查詢結果一樣，但兩者的意義卻不一樣。將條件放在 WHERE 子句中的意思，是先連接 customers 和 invoices 表，然後從這些連接後的記錄中篩選出顧客編號為 1 的記錄。
從可讀性和標準寫法的角度來看，建議與「連接相關」的條件放在 ON 子句中，而用於「過濾」的條件放在 WHERE 子句中。這樣做可以使查詢的意圖更加明確。以上面的例子說明，如果我們將條件放在 JOIN 子句，表達的意思是：「我們只對在連接時就已經符合 “CustomerId = 1” 的記錄感興趣」；而放在 WHERE 子句則是：「我們對所有連接的記錄感興趣，但想篩選符合 “CustomerId = 1” 的記錄」。而後者會更接近我們的想法。
