PostgreSQL 筆記 - Join 查詢與應用

資料庫中常用的兩大操作分別是 Join 和 Aggregation，透過這兩個方式，我們能有效地合併或分析資料。

接下來的範例，我會用下列這三個資料表作為說明基礎：

1. Joins

• 用於從多個相關資料表合併列（rows），產生新的資料集合。
• 當查詢需求涉及到多個相關資源時，通常會需要使用 Join。

2. Aggregation

• Aggregation 是將多個列（rows）的資料，計算成一個單一數值。
• 當需求中出現「最多」、「最少」、「平均」等描述時，就是使用 Aggregation。

從不同資料表 JOIN 資料

範例查詢：列出每則留言內容及其對應的使用者名稱

執行順序

1. FROM comments：從 comments 表中選取所有資料列。
2. JOIN users ON ...：將來自 comments 的每列資料，根據條件 (users.id = comments.user_id) 與 users 表進行合併。
3. SELECT contents, username：從合併後的結果中選擇所需的欄位。

關於 JOIN 的補充說明

1. 資料表順序對 JOIN 的影響

大多數情況下，JOIN 語句中資料表的順序不會影響結果（特別是 INNER JOIN）：

上述查詢的結果與以下查詢完全相同：

2. 欄位名稱衝突時必須明確指定來源

如果不同的表中存在相同名稱的欄位，查詢時必須清楚指明欄位來源：

解決方式：

3. 使用 AS 重命名資料表（Alias）

透過 AS 可以更簡潔地引用表名，特別是名稱較長時非常實用：

PostgreSQL JOIN 方法介紹

資料表結構

🔗 INNER JOIN

僅回傳 photos.user_id = users.id 的配對成功結果。

示意圖：

結果表格：

JOIN 時的缺失資料問題

當資料表的 Foreign Key 欄位包含 NULL 值時，INNER JOIN 會忽略這些資料，導致查詢結果不完整。

問題場景

假設我們希望列出所有 photos 的 URL 與對應的 username，但若某筆 photos.user_id 是 NULL，如下查詢將無法顯示這筆資料：

由於 INNER JOIN 會過濾掉無法配對的資料，像 user_id = NULL 的紀錄就會被排除在結果之外。

解法：使用其他 JOIN 類型

為了保留 photos 表的所有資料，無論是否能對應 users，我們可以改用 LEFT JOIN：

這樣即使 user_id 是 NULL，該筆資料仍會顯示，username 欄位則為 NULL，確保資料完整呈現。

LEFT JOIN（LEFT OUTER JOIN）

保留 photos 的所有資料，右側（users）若無配對則補 NULL。

示意圖：

結果表格：

RIGHT JOIN（RIGHT OUTER JOIN）

保留 users 所有資料，左側（photos）若無配對則補 NULL。

示意圖：

結果表格：

FULL JOIN（FULL OUTER JOIN）

保留兩邊所有資料，無配對則補 NULL。

示意圖：

結果表格：

參考資料

• SQL and PostgreSQL: The Complete Developer's Guide