跳至主要內容
日沐 SunBathe

Appearance

JOIN 完全攻略 ​

當資料分散在多張表時，JOIN 是將它們連接起來的關鍵。本篇將完整介紹各種 JOIN 類型，幫助你選擇正確的連接方式。

一、 範例資料 ​

為了展示各種 JOIN，我們先建立範例資料：

sql
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product TEXT,
    amount NUMERIC(10, 2)
);

INSERT INTO customers (id, name) VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie');

INSERT INTO orders (id, customer_id, product, amount) VALUES
    (101, 1, 'iPhone', 999.00),
    (102, 1, 'AirPods', 199.00),
    (103, 2, 'MacBook', 1999.00),
    (104, NULL, 'Keyboard', 99.00);  -- 沒有對應客戶

目前資料：

  • Alice (id=1) 有 2 筆訂單
  • Bob (id=2) 有 1 筆訂單
  • Charlie (id=3) 沒有訂單
  • 有 1 筆訂單沒有客戶

二、 INNER JOIN ​

只返回兩邊都匹配的列。

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

結果：

nameproductamount
AliceiPhone999.00
AliceAirPods199.00
BobMacBook1999.00
  • ✅ Alice 和 Bob 有訂單，出現在結果中
  • ❌ Charlie 沒有訂單，不會出現
  • ❌ 無客戶的訂單 (Keyboard) 不會出現

三、 LEFT JOIN (LEFT OUTER JOIN) ​

返回左表所有列，右表沒匹配的填 NULL。

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

結果：

nameproductamount
AliceiPhone999.00
AliceAirPods199.00
BobMacBook1999.00
CharlieNULLNULL
  • ✅ 所有客戶都會出現
  • ✅ Charlie 沒有訂單，右邊填 NULL
  • ❌ 無客戶的訂單 (Keyboard) 不會出現

常見用法：找出沒有訂單的客戶 ​

sql
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- 結果：Charlie

四、 RIGHT JOIN (RIGHT OUTER JOIN) ​

返回右表所有列，左表沒匹配的填 NULL。

sql
SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

結果：

nameproductamount
AliceiPhone999.00
AliceAirPods199.00
BobMacBook1999.00
NULLKeyboard99.00
  • ✅ 所有訂單都會出現
  • ✅ Keyboard 沒有客戶，左邊填 NULL
  • ❌ Charlie 沒有訂單，不會出現

> **實務上 RIGHT JOIN 很少用**

可以把表的順序交換，改用 LEFT JOIN，可讀性更好。

五、 FULL OUTER JOIN ​

返回兩邊所有列，沒匹配的填 NULL。

sql
SELECT c.name, o.product, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

結果：

nameproductamount
AliceiPhone999.00
AliceAirPods199.00
BobMacBook1999.00
CharlieNULLNULL
NULLKeyboard99.00
  • ✅ 所有客戶都會出現
  • ✅ 所有訂單都會出現

六、 CROSS JOIN ​

笛卡爾積：左表每一列與右表每一列配對。

sql
SELECT c.name, o.product
FROM customers c
CROSS JOIN orders o;

結果：3 個客戶 × 4 筆訂單 = 12 列

nameproduct
AliceiPhone
AliceAirPods
AliceMacBook
AliceKeyboard
BobiPhone
...（共 12 列）...

> **注意效能**

CROSS JOIN 會產生 M × N 列。1000 × 1000 = 100 萬列，使用前務必確認。

實用場景：生成日期表 ​

sql
SELECT date::DATE
FROM generate_series('2024-01-01', '2024-12-31', '1 day'::INTERVAL) AS date;

七、 SELF JOIN ​

表與自己連接，常用於處理層級資料。

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'Developer', 2);

-- 查詢每個員工和他的主管
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

結果：

employeemanager
CEONULL
CTOCEO
DeveloperCTO

八、 NATURAL JOIN 與 USING ​

NATURAL JOIN ​

自動根據同名欄位連接（不推薦）：

sql
-- 假設兩表都有 customer_id 欄位
SELECT * FROM orders NATURAL JOIN order_items;

> **避免使用 NATURAL JOIN**

容易因表結構變更而出錯，且可讀性差。

USING ​

當連接欄位同名時，可簡化語法：

sql
-- 原本
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

-- 使用 USING
SELECT * FROM orders o JOIN customers c USING (customer_id);

九、 多表 JOIN ​

可以連續連接多張表：

sql
SELECT
    c.name AS customer,
    o.id AS order_id,
    p.name AS product,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

執行順序 ​

> **JOIN 順序的影響**

PostgreSQL 優化器會自動選擇最佳的 JOIN 順序，但在極端情況下可以用 SET join_collapse_limit = 1 強制按書寫順序執行。

十、 JOIN 條件與 WHERE 的差異 ​

sql
-- 寫法 A：條件放在 ON
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 500;

-- 寫法 B：條件放在 WHERE
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 500;

結果不同！

  • 寫法 A：Charlie 會出現（值為 NULL），因為 LEFT JOIN 保留所有客戶
  • 寫法 B：Charlie 不會出現，因為 WHERE 過濾掉了 NULL

> **OUTER JOIN 時要特別注意**

想過濾右表的條件放在 ON；想過濾整個結果的條件放在 WHERE。

十一、 JOIN 效能技巧 ​

1. 確保有索引 ​

sql
-- 連接欄位需要索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

2. 避免 SELECT * ​

sql
-- 不好：傳輸不必要的欄位
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

-- 好：只選需要的欄位
SELECT o.id, o.amount, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

3. 先過濾再 JOIN ​

sql
-- 好：先用子查詢過濾，減少 JOIN 的資料量
SELECT c.name, o.product
FROM customers c
JOIN (
    SELECT * FROM orders WHERE amount > 1000
) o ON c.id = o.customer_id;

十二、 JOIN 類型總覽 ​

JOIN 類型左表無匹配右表無匹配
INNER不出現不出現
LEFT出現，右邊填 NULL不出現
RIGHT不出現出現，左邊填 NULL
FULL OUTER出現出現
CROSS全部配對全部配對

總結 ​

  • INNER JOIN：只要交集，最常用
  • LEFT JOIN：保留左表所有，找「沒有訂單的客戶」這類需求
  • RIGHT JOIN：等同於交換順序的 LEFT JOIN
  • FULL OUTER JOIN：兩邊都保留
  • CROSS JOIN：笛卡爾積，謹慎使用
  • SELF JOIN：處理層級結構

進階挑戰 ​

  1. 使用 SELF JOIN 查詢「購買過相同產品的客戶對」
  2. 比較 LEFT JOIN + WHERE IS NULL 和 NOT EXISTS 的執行計畫，哪個更有效率？
  3. 設計一個 3 層以上的組織架構表，用遞迴查詢列出完整的管理鏈

延伸閱讀與資源 ​

← 上一章：SELECT 深入解析 | 返回專題首頁 | 下一章：子查詢與 CTE →