主題: PostgreSQL

JOIN 之前先算關係：別把正常列數當成重複資料

先決定一列結果代表什麼、沒有匹配資料要不要保留，再選 INNER JOIN 或 LEFT JOIN。這能避開最常見的 JOIN fanout 誤判。

一條 JOIN 可以完全正確，結果列數卻比預期多。這時很容易先把它們當成重複資料。

假設一位客戶有三筆訂單。把 customers 接到 orders 後，客戶姓名出現三次很正常。結果的一列現在代表「一筆訂單及其客戶」，不再代表「一位客戶」。這個差別沒先講清楚，後面的 DISTINCT、GROUP BY 或補 index 都可能變成在掩蓋問題。

PostgreSQL 把 JOIN 定義成依條件配對兩張資料表的列。每一個左側列，會和所有符合 ON 的右側列各形成一列。近期的 r/learnSQL 討論 記錄了一個例子：三筆訂單因為關係沒算清楚，結果被放大成十四列。

先決定結果的粒度

先用兩張資料表：

customers(id, name)
orders(id, customer_id, total_cents, status)

orders.customer_id 指向 customers.id。這是一對多關係：一筆訂單只屬於一位客戶，一位客戶可以有很多筆訂單。

所以這條 query 的粒度是一筆訂單：

SELECT c.name, o.id, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id;

客戶 Ada 有三筆訂單，Ada 會出現三次，因為三筆訂單各自匹配到她。這不是資料庫複製了客戶，而是 query 要回傳訂單明細。

寫 JOIN 前，先把需求改成一句話：

• 一列要代表一筆訂單，還是一位客戶？
• 沒有訂單的客戶要不要保留？
• 右側一筆資料可能配到幾筆左側資料？

前兩題會決定 JOIN type，第三題會決定你該期待幾列。想做「每位客戶的訂單數」是另一種粒度，需要在明細列正確產生後再聚合；不能靠 DISTINCT 把多筆訂單藏掉。

INNER JOIN：只留下真的配對

JOIN 省略 INNER 時，預設就是 INNER JOIN。兩邊都滿足 ON 條件的列才會留在結果中：

SELECT c.name, o.id, o.total_cents
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

它適合訂單明細、已經有作者的書籍清單，或任何「沒有右側資料就不構成一列結果」的需求。沒有訂單的客戶不會出現，因為沒有可配對的 orders 列。

ON 是配對規則，建議把欄位寫完整。c.id 與 o.customer_id 清楚告訴讀者兩個 id 分別來自哪張資料表，也避免未來新增同名欄位時變得含糊。

LEFT JOIN：保留左側，再接上匹配資料

客戶列表通常有不同需求：即使還沒下單，也要出現在後台或 CRM。這時讓 customers 放左側：

SELECT c.name, o.id, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

LEFT JOIN 會保留每一列 customers。找不到訂單時，o.id、o.total_cents 等右側欄位是 NULL。想找從未下單的客戶，就接著寫：

WHERE o.id IS NULL

這不是「查不到資料」的錯誤，而是 query 有意保留左側資料表後得到的結果。

更容易踩到的是右側條件的位置。需求若是「保留所有客戶，只接上已付款訂單」，付款條件應留在 ON：

SELECT c.name, o.id, o.total_cents
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid';

若改成 WHERE o.status = 'paid'，沒有訂單的客戶會因為右側值是 NULL 而被篩掉。結果看起來像 left join，實際上又只留下有匹配訂單的客戶。

FULL OUTER JOIN 與 CROSS JOIN 有各自的工作

FULL OUTER JOIN 保留兩側所有列。它適合比對兩份匯入清單：兩邊都有的 email、只在來源 A 的 email、只在來源 B 的 email 都能留在結果裡。

CROSS JOIN 則刻意產生每一種組合。兩個會議室乘上七天，會得到十四個時段格。它適合建立尺寸乘顏色、日期乘房間的矩陣；在一般關聯查詢裡誤用，列數會直接相乘。

RIGHT JOIN 是 LEFT JOIN 的鏡像。它能用，但多數 query 把資料表順序換過來後改寫成 LEFT JOIN，比較容易看出哪一側必須保留。

讀 JOIN 的小檢查

每次 review 一條 JOIN，我會先確認：

1. foreign key 在哪一側，這是 one-to-one、one-to-many 還是 many-to-many？
2. 一列結果應代表什麼？
3. 沒有匹配資料時，哪一側仍要出現？
4. 每個篩選條件是在定義配對，還是在篩掉最終結果？

這四題能把「結果怎麼多了」變成可以驗證的問題，不必靠 DISTINCT 試到剛好看起來正確。

我學到什麼

• 我會先說明結果的一列代表什麼，再判斷一對多關係產生的多列是否合理。
• INNER JOIN 只保留匹配資料；LEFT JOIN 保留左側資料，沒有右側匹配時用 NULL 補上。
• ON 定義如何配對，WHERE 篩選配對後的結果。右側條件放錯位置，會讓 LEFT JOIN 丟掉原本要保留的資料。
• FULL OUTER JOIN 適合對帳，CROSS JOIN 適合刻意建立所有組合，兩者都不是處理重複列的萬用工具。

結語

JOIN 的語法不長，結果列的意思才是難處。先畫出關係、決定結果粒度、回答未匹配資料要不要保留，SQL 就會自然收斂成合適的 JOIN。資料表變多以後，這套檢查仍然能用。

外部參考連結

• PostgreSQL：Joins Between Tables
• PostgreSQL：Table Expressions
• Reddit：JOIN doubled my revenue, not in a good way

延伸學習

• CS50 SQL Week 1：Relating：Harvard CS50 的官方影片課程，介紹關聯、key、基數與 JOIN。
• PGExercises：Joins and Subqueries：先預測每一列代表什麼，再用小型資料集驗證。