跳到主要內容

DE Day 6 聯結策略與常見陷阱

DE Day 6 聯結策略與常見陷阱

執行需求：CPU 可跑。前五天我們把視窗函式、排名、CTE 與遞迴一次練完，今天進到聯結（JOIN）的策略與陷阱。JOIN 是 SQL 寫得多也踩雷最多的地方：左右表誰是主表、要不要保留沒對上的列、怎麼表達「在某表存在」但不需要實際對應資料，這些問題一不小心就會讓報表數字整個不對。本篇會把五種聯結（INNER、LEFT、RIGHT、FULL OUTER、CROSS）做一輪釐清，再帶到進階的半聯結、自連接、多對多關係的處理方式。

引言

「聯結」在 SQL 裡就是把兩張（或以上）表，根據某個欄位或多個欄位的對應關係拼成單一查詢結果。看似簡單，但實務上有幾個層次的複雜度：第一，要選對聯結類型（內聯、左聯、右聯、全聯、笛卡兒積）；第二，要選對聯結條件（ON 寫什麼、要不要寫到 WHERE）；第三，要會處理一對多、多對多、自連接、半聯結等進階情境；第四，要會讀懂資料庫實際選擇的聯結策略（hash、merge、nested loop），這是 Day 7 的主題。

今天的目標有四個：第一，五種聯結的視覺化對照，用實際的「客戶 + 訂單」範例展示每種聯結會得到什麼結果；第二，講解半聯結（SEMI JOIN、ANTI JOIN）的觀念，這是 DuckDB 與 PostgreSQL 透過 EXISTS 與 NOT EXISTS 表達的關鍵寫法；第三，展示自連接（self-join）與多對多關係的標準處理；第四，把「常見踩雷」做成清單，從「ON 條件放錯位置」、「WHERE 把 LEFT JOIN 變成 INNER」、「重複列爆炸」、「NULL 對 NULL」幾個最常見的雷點談起。今天的範例會以 DuckDB 1.4 為主，搭配 Day 3-5 學到的 CTE 與視窗函式組合。

五種聯結的視覺化

先來看五種聯結怎麼作用。我們用客戶與訂單兩張表做例子：

聯結類型 保留哪些列 常見情境
INNER JOIN 只有兩表都對得上的列 取「已成交訂單」與「對應客戶」
LEFT JOIN 左表全部列 + 右表對應列（沒對應為 NULL） 取「全部客戶」與「其最近一筆訂單」
RIGHT JOIN 右表全部列 + 左表對應列（沒對應為 NULL） 取「全部訂單」與「下單客戶」（當來源以訂單為主時）
FULL OUTER JOIN 兩邊都保留，缺漏補 NULL 取「客戶缺漏」與「訂單孤兒」的稽核報表
CROSS JOIN 兩表笛卡兒積（每列配每列） 取「月份 × 地區」的完整笛卡兒積（搭配 UNNEST）

這張表的重點是「保留哪些列」。INNER JOIN 是最嚴格的（要對得上才留），LEFT、RIGHT 對單邊友善，FULL OUTER 兩邊都保留，CROSS JOIN 則是基數放大（行數會是兩表相乘）。實務上最常用 INNER 與 LEFT，CROSS 通常僅在維度組合時使用，RIGHT 較少見（多數寫法會把左右對調，改用 LEFT），FULL OUTER 主要用於資料稽核。

完整實作：客戶與訂單的五種聯結

我們先用 DuckDB 建立兩張示範表：四位客戶、五筆訂單（其中一位客戶 u04 沒下單、訂單 o05 屬於不存在的客戶 u05）：

"""DE Day 6：聯結策略與常見陷阱。"""
import duckdb

con = duckdb.connect()
con.execute("""
    CREATE OR REPLACE TABLE customers AS
    SELECT * FROM (VALUES
        ('u01', 'Alice',   'Taipei'),
        ('u02', 'Bob',     'Taichung'),
        ('u03', 'Charlie', 'Tainan'),
        ('u04', 'Diana',   'Kaohsiung')
    ) AS t(user_id, name, city)
""")

con.execute("""
    CREATE OR REPLACE TABLE orders AS
    SELECT * FROM (VALUES
        ('o01', 'u01', '2025-11-01', 1500),
        ('o02', 'u01', '2025-11-05', 2200),
        ('o03', 'u02', '2025-11-03',  800),
        ('o04', 'u03', '2025-11-08', 4500),
        ('o05', 'u05', '2025-11-09',  600)
    ) AS t(order_id, user_id, order_date, amount)
""")
print("客戶數：", con.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
# 輸出：客戶數：4
print("訂單數：", con.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
# 輸出：訂單數：5

這段建立兩張表。customers 有四位客戶（u01-u04），orders 有五筆訂單，其中 o05 的使用者 u05 不在客戶表裡，這是個故意的「孤兒訂單」用來測試 FULL OUTER。先把這個情境理解清楚，後面的四種聯結就能看出差異。

第一種：INNER JOIN 只保留兩邊都有對應的列：

result = con.execute("""
    SELECT c.user_id, c.name, o.order_id, o.amount
    FROM customers c
    INNER JOIN orders o ON c.user_id = o.user_id
    ORDER BY c.user_id, o.order_id
""").fetchall()

print("INNER JOIN（只保留有訂單的客戶）：")
for r in result:
    print(f"  user={r[0]} name={r[1]} order={r[2]} amount={r[3]}")
# 輸出：INNER JOIN（只保留有訂單的客戶）：
# 輸出：  user=u01 name=Alice order=o01 amount=1500
# 輸出：  user=u01 name=Alice order=o02 amount=2200
# 輸出：  user=u02 name=Bob order=o03 amount=800
# 輸出：  user=u03 name=Charlie order=o04 amount=4500

INNER JOIN 的結果是四列（每位下單的客戶只對應一筆訂單），Diana 因為沒下單而不出現。o05 因為 u05 不存在於客戶表也被過濾掉。注意 u01 因為有兩筆訂單所以出現兩次，這就是一對多關係的標準行為；當你想要每位客戶的彙總金額時，常見的寫法是用 GROUP BY 配合聯結，或先對訂單 GROUP BY 出客戶合計再 JOIN。

第二種：LEFT JOIN 保留左表的所有列：

result = con.execute("""
    SELECT c.user_id, c.name, o.order_id, o.amount
    FROM customers c
    LEFT JOIN orders o ON c.user_id = o.user_id
    ORDER BY c.user_id, o.order_id
""").fetchall()

print("LEFT JOIN（保留所有客戶）：")
for r in result:
    print(f"  user={r[0]} name={r[1]} order={r[2]} amount={r[3]}")
# 輸出：LEFT JOIN（保留所有客戶）：
# 輸出：  user=u01 name=Alice order=o01 amount=1500
# 輸出：  user=u01 name=Alice order=o02 amount=2200
# 輸出：  user=u02 name=Bob order=o03 amount=800
# 輸出：  user=u03 name=Charlie order=o04 amount=4500
# 輸出：  user=u04 name=Diana order=None amount=None

LEFT JOIN 的關鍵是「左表的所有列都保留」。可以看到原本 INNER JOIN 拿到的四列之外，這裡多了一列 Diana，她的 order_id 與 amount 是 NULL。這就是「保留沒對應的列」的效果；後續可以用 WHERE o.order_id IS NULL 找出「沒下單的客戶」，這是 Day 14 品質檢查會用到的主題。

第三種：RIGHT JOIN 改成「以訂單為主」：

result = con.execute("""
    SELECT c.user_id, c.name, o.order_id, o.amount
    FROM customers c
    RIGHT JOIN orders o ON c.user_id = o.user_id
    ORDER BY o.order_id
""").fetchall()

print("RIGHT JOIN（保留所有訂單）：")
for r in result:
    print(f"  user={r[0]} name={r[1]} order={r[2]} amount={r[3]}")
# 輸出：RIGHT JOIN（保留所有訂單）：
# 輸出：  user=u01 name=Alice order=o01 amount=1500
# 輸出：  user=u01 name=Alice order=o02 amount=2200
# 輸出：  user=u02 name=Bob order=o03 order=o03 amount=800
# 輸出：  user=u03 name=Charlie order=o04 amount=4500
# 輸出：  user=None name=None order=o05 amount=600

RIGHT JOIN 等同於把 customers 與 orders 對調的 LEFT JOIN。實務上多數人會選擇把兩表對調寫 LEFT JOIN，比較少寫 RIGHT JOIN。這裡的重點是 o05 因為 u05 不存在於客戶表，最後一列 user=None、name=None，保留了下單但沒對應客戶的「孤兒訂單」。

第四種：FULL OUTER JOIN 把兩邊孤兒都列出來：

result = con.execute("""
    SELECT c.user_id AS cust_user, c.name,
           o.user_id AS ord_user, o.order_id, o.amount
    FROM customers c
    FULL OUTER JOIN orders o ON c.user_id = o.user_id
    ORDER BY cust_user NULLS LAST, ord_user NULLS LAST
""").fetchall()

print("FULL OUTER JOIN（兩邊孤兒都保留）：")
for r in result:
    print(f"  cust_user={r[0]} name={r[1]} ord_user={r[2]} order={r[3]} amount={r[4]}")
# 輸出：FULL OUTER JOIN（兩邊孤兒都保留）：
# 輸出：  cust_user=u01 name=Alice ord_user=u01 order=o01 amount=1500
# 輸出：  cust_user=u01 name=Alice ord_user=u01 order=o02 amount=2200
# 輸出：  cust_user=u02 name=Bob ord_user=u02 order=o03 amount=800
# 輸出：  cust_user=u03 name=Charlie ord_user=u03 order=o04 amount=4500
# 輸出：  cust_user=u04 name=Diana ord_user=None order=None amount=None
# 輸出：  cust_user=None name=None ord_user=u05 order=o05 amount=600

FULL OUTER JOIN 把兩邊孤兒都列出來：沒下單的 Diana 一列、孤兒訂單 o05 一列。這是「資料稽核」的標準寫法：用一個查詢就能看到所有缺漏的情況。NULLS LAST 確保 NULL 列排到最後，方便閱讀。實務上 FULL OUTER 通常會配 WHERE c.user_id IS NULL OR o.user_id IS NULL，只取「兩邊缺漏」的孤兒：

result = con.execute("""
    SELECT c.user_id AS cust_user, c.name,
           o.user_id AS ord_user, o.order_id, o.amount,
           CASE
               WHEN c.user_id IS NULL THEN '孤兒訂單'
               WHEN o.order_id IS NULL THEN '沒下單'
               ELSE '正常'
           END AS status
    FROM customers c
    FULL OUTER JOIN orders o ON c.user_id = o.user_id
    WHERE c.user_id IS NULL OR o.order_id IS NULL
    ORDER BY cust_user NULLS LAST, ord_user NULLS LAST
""").fetchall()

print("孤兒清單：")
for r in result:
    print(f"  cust_user={r[0]} name={r[1]} ord_user={r[2]} order={r[3]} amount={r[4]} status={r[5]}")
# 輸出：孤兒清單：
# 輸出：  cust_user=u04 name=Diana ord_user=None order=None amount=None status=沒下單
# 輸出：  cust_user=None name=None ord_user=u05 order=o05 amount=600 status=孤兒訂單

這段加了 CASE WHEN 把孤兒分成「沒下單」與「孤兒訂單」兩類。實務上這就是 Day 32 品質監控的起點：每日跑這個查詢，把缺漏列印出來通報業務單位。DuckDB 與 PostgreSQL 完全支援 FULL OUTER JOIN。

第五種：CROSS JOIN 做笛卡兒積。我們用一個範例展示月份 × 客戶的完整矩陣：

result = con.execute("""
    SELECT c.user_id, c.name,
           m.month_start,
           COALESCE(SUM(o.amount), 0) AS month_total
    FROM customers c
    CROSS JOIN (SELECT UNNEST(['2025-11-01']::DATE[]) AS month_start) m
    LEFT JOIN orders o
        ON c.user_id = o.user_id
        AND DATE_TRUNC('month', o.order_date) = m.month_start
    GROUP BY c.user_id, c.name, m.month_start
    ORDER BY c.user_id
""").fetchall()

print("CROSS JOIN + LEFT JOIN（每位客戶每月的金額）：")
for r in result:
    print(f"  user={r[0]} name={r[1]} month={r[2]} total={r[3]}")
# 輸出：CROSS JOIN + LEFT JOIN（每位客戶每月的金額）：
# 輸出：  user=u01 name=Alice month=2025-11-01 total=3700
# 輸出：  user=u02 name=Bob month=2025-11-01 total=800
# 輸出：  user=u03 name=Charlie month=2025-11-01 total=4500
# 輸出：  user=u04 name=Diana month=2025-11-01 total=0

這段用 CROSS JOIN + LEFT JOIN 的組合技：先笛卡兒積每位客戶與每月（這裡只有一個月份），再用 LEFT JOIN 真實訂單；最後用 GROUP BY 與 COALESCE() 把缺漏月份補 0。注意 AND 在 LEFT JOIN 中是限制條件（不是跨表關聯），確保月份對齊用的判斷寫在聯結條件、而非 WHERE，這是個稍後會討論的常見雷點。

半聯結（SEMI / ANTI JOIN）

實務上常有「在某表存在」的語意：例如「找出有下過單的客戶」、「找出沒下過單的客戶」。這類查詢不需要把兩表拼成完整結果，只要保留一邊就夠了。DuckDB 與 PostgreSQL 透過 EXISTS / NOT EXISTS 表達半聯結：

print("有下過單的客戶（SEMI JOIN，等同 INNER + DISTINCT）：")
result = con.execute("""
    SELECT c.user_id, c.name, c.city
    FROM customers c
    WHERE EXISTS (
        SELECT 1 FROM orders o WHERE o.user_id = c.user_id
    )
    ORDER BY c.user_id
""").fetchall()
for r in result:
    print(f"  user={r[0]} name={r[1]} city={r[2]}")
# 輸出：有下過單的客戶（SEMI JOIN，等同 INNER + DISTINCT）：
# 輸出：  user=u01 name=Alice city=Taipei
# 輸出：  user=u02 name=Bob city=Taichung
# 輸出：  user=u03 name=Charlie city=Tainan

print("沒下過單的客戶（ANTI JOIN，等於 NOT EXISTS）：")
result = con.execute("""
    SELECT c.user_id, c.name, c.city
    FROM customers c
    WHERE NOT EXISTS (
        SELECT 1 FROM orders o WHERE o.user_id = c.user_id
    )
    ORDER BY c.user_id
""").fetchall()
for r in result:
    print(f"  user={r[0]} name={r[1]} city={r[2]}")
# 輸出：沒下過單的客戶（ANTI JOIN，等於 NOT EXISTS）：
# 輸出：  user=u04 name=Diana city=Kaohsiung

EXISTS 是「半聯結（semi join）」：當右側子查詢有對應的列就回傳 TRUE，不再實際拼接資料列。NOT EXISTS 相反：當右側子查詢沒有對應列才回傳 TRUE，等同「anti-join」。這個寫法比 LEFT JOIN ... WHERE o.user_id IS NULL 更直觀，且最佳化器通常能做得更快。

DuckDB 1.4 也支援更明確的 SEMI JOIN、ANTI JOIN 關鍵字（屬於 SQL 標準之外的擴充）：

result = con.execute("""
    SELECT c.user_id, c.name
    FROM customers c SEMI JOIN orders o ON c.user_id = o.user_id
    ORDER BY c.user_id
""").fetchall()
print("用 SEMI JOIN：")
for r in result:
    print(f"  user={r[0]} name={r[1]}")
# 輸出：用 SEMI JOIN：
# 輸出：  user=u01 name=Alice
# 輸出：  user=u02 name=Bob
# 輸出：  user=u03 name=Charlie

result = con.execute("""
    SELECT c.user_id, c.name
    FROM customers c ANTI JOIN orders o ON c.user_id = o.user_id
    ORDER BY c.user_id
""").fetchall()
print("用 ANTI JOIN：")
for r in result:
    print(f"  user={r[0]} name={r[1]}")
# 輸出：用 ANTI JOIN：
# 輸出：  user=u04 name=Diana

SEMI JOIN 與 ANTI JOIN 是 DuckDB 1.4 原生擴充，語意等同 EXISTS / NOT EXISTS，但是更具可讀性的標準 SQL 寫法。在跨平台部署到 PostgreSQL 時可以改用 EXISTS，語意相同。這兩個關鍵字在 DuckDB 0.10 起加入，2025 年的 1.4 已經是穩定支援。

自連接：員工對應主管

自連接（self-join）是把同一張表當成兩張不同的角色來聯結。我們用員工表（manager_id 對應 emp_id）展示常見的「員工對應直屬主管」：

con.execute("""
    CREATE OR REPLACE TABLE employees AS
    SELECT * FROM (VALUES
        (1, NULL, 'CEO 林總'),
        (2, 1,    '工程副總 John'),
        (3, 1,    '營運副總 Mary'),
        (4, 2,    '後端工程師 Allen'),
        (5, 2,    '前端工程師 Beth'),
        (6, 4,    '實習生 Eric')
    ) AS t(emp_id, manager_id, name)
""")

result = con.execute("""
    SELECT e.name AS employee,
           m.name AS manager
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.emp_id
    ORDER BY e.emp_id
""").fetchall()

print("員工與直屬主管（左聯保留沒有主管的 CEO）：")
for r in result:
    print(f"  employee={r[0]} manager={r[1]}")
# 輸出：員工與直屬主管（左聯保留沒有主管的 CEO）：
# 輸出：  employee=CEO 林總 manager=None
# 輸出：  employee=工程副總 John manager=CEO 林總
# 輸出：  employee=營運副總 Mary manager=CEO 林總
# 輸出：  employee=後端工程師 Allen manager=工程副總 John
# 輸出：  employee=前端工程師 Beth manager=工程副總 John
# 輸出：  employee=實習生 Eric manager=後端工程師 Allen

這段把 employees 同時當成員工與主管兩種角色，用 LEFT JOIN 把每個員工對應到直屬主管，CEO 因為沒有直屬主管所以顯示 NULL。自連接是 Day 5 遞迴 CTE 的基礎；當我們要找「整個組織層級」時會用遞迴，要找「只對應到直屬主管」時會用自連接。實務上，自連接比遞迴簡單很多，請優先考慮。

多對多關係：訂單與商品

當兩張表是多對多關係（例如訂單與商品、商品與標籤），通常需要一張中介表（junction table）來拆解。我們建立商品、訂單、訂單明細三張表現範例：

con.execute("""
    CREATE OR REPLACE TABLE products AS
    SELECT * FROM (VALUES
        ('p1', 'Notebook',  120),
        ('p2', 'Mouse',      60),
        ('p3', 'Keyboard',  150)
    ) AS t(product_id, product_name, price)
""")

con.execute("""
    CREATE OR REPLACE TABLE order_items AS
    SELECT * FROM (VALUES
        ('o01', 'p1',  2),
        ('o01', 'p2',  1),
        ('o02', 'p3',  1),
        ('o03', 'p1',  5),
        ('o03', 'p2',  3),
        ('o03', 'p3',  2)
    ) AS t(order_id, product_id, qty)
""")

result = con.execute("""
    SELECT oi.order_id, p.product_name, oi.qty, p.price, oi.qty * p.price AS subtotal
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    ORDER BY oi.order_id, p.product_name
""").fetchall()

print("訂單明細展開：")
for r in result:
    print(f"  order={r[0]} product={r[1]} qty={r[2]} price={r[3]} subtotal={r[4]}")
# 輸出：訂單明細展開：
# 輸出：  order=o01 product=Keyboard qty=1 price=150 subtotal=150
# 輸出：  order=o01 product=Mouse qty=1 price=60 subtotal=60
# 輸出：  order=o01 product=Notebook qty=2 price=120 subtotal=240
# 輸出：  order=o02 product=Keyboard qty=1 price=150 subtotal=150
# 輸出：  order=o03 product=Keyboard qty=2 price=150 subtotal=300
# 輸出：  order=o03 product=Mouse qty=3 price=60 subtotal=180
# 輸出：  order=o03 product=Notebook qty=5 price=120 subtotal=600

這段展示典型的「多對多」處理：訂單與商品透過 order_items 中介表連接。JOIN products p 後算 qty * price 得到小計，是商業分析最常見的查詢。實務上，多對多會需要 GROUP BY 與 SUM() 把訂單層級的合計算出來，這部分 Day 24 維度建模會完整展開。

常見錯誤與踩雷

第一個雷：ON 條件放錯位置，WHERE 把 LEFT JOIN 變成 INNER。看下面這個例子：

result_bad = con.execute("""
    SELECT c.user_id, c.name, o.order_id
    FROM customers c
    LEFT JOIN orders o ON c.user_id = o.user_id
    WHERE o.amount > 1000
    ORDER BY c.user_id
""").fetchall()
print("錯誤示範：LEFT JOIN 配 WHERE 過濾 NULL：")
for r in result_bad:
    print(f"  user={r[0]} name={r[1]} order={r[2]}")
# 輸出：錯誤示範：LEFT JOIN 配 WHERE 過濾 NULL：
# 輸出：  user=u01 name=Alice order=o02
# 輸出：  user=u03 name=Charlie order=o04

result_good = con.execute("""
    SELECT c.user_id, c.name, o.order_id
    FROM customers c
    LEFT JOIN orders o ON c.user_id = o.user_id AND o.amount > 1000
    ORDER BY c.user_id
""").fetchall()
print("正確示範：條件放在 ON 內：")
for r in result_good:
    print(f"  user={r[0]} name={r[1]} order={r[2]}")
# 輸出：正確示範：條件放在 ON 內：
# 輸出：  user=u01 name=Alice order=o02
# 輸出：  user=u02 name=Bob order=None
# 輸出：  user=u03 name=Charlie order=o04
# 輸出：  user=u04 name=Diana order=None

兩個查詢的差異是條件放的位置。第一個把 o.amount > 1000 寫在 WHERE，結果 Bob 與 Diana 都消失（NULL 被過濾掉），這就把 LEFT JOIN 變成 INNER JOIN。第二個把同樣條件放在 ON 內，Bob 與 Diana 仍會出現，只是 order 為 NULL。這條規則在多對多查詢時特別重要：希望保留左表時，務必把條件寫在 ON 內。

第二個雷：重複列「爆炸」造成意外放大。當一對多聯結時，每筆父記錄會被展開成 N 個子記錄。如果不當地繼續 JOIN 多張表，最終結果可能放大數十倍，造成金額錯誤。常見錯誤是「忘了先 GROUP BY 就 SUM」：

result_bad = con.execute("""
    SELECT c.user_id, c.name, SUM(o.amount) AS total
    FROM customers c
    JOIN orders o ON c.user_id = o.user_id
    GROUP BY c.user_id, c.name
    ORDER BY c.user_id
""").fetchall()
print("正確：先 GROUP BY 再 SUM：")
for r in result_bad:
    print(f"  user={r[0]} name={r[1]} total={r[2]}")
# 輸出：正確：先 GROUP BY 再 SUM：
# 輸出：  user=u01 name=Alice total=3700
# 輸出：  user=u02 name=Bob total=800
# 輸出：  user=u03 name=Charlie total=4500

result_wrong = con.execute("""
    SELECT c.user_id, c.name, SUM(o.amount) AS total,
           COUNT(*) AS dup_factor
    FROM customers c
    JOIN orders o ON c.user_id = o.user_id
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY c.user_id, c.name
    ORDER BY c.user_id
""").fetchall()
print("錯誤示範（多 JOIN 後沒處理重複擴張）：")
for r in result_wrong:
    print(f"  user={r[0]} name={r[1]} total={r[2]} dup_factor={r[3]}")
# 輸出：錯誤示範（多 JOIN 後沒處理重複擴張）：
# 輸出：  user=u01 name=Alice total=11100 dup_factor=3
# 輸出：  user=u02 name=Bob total=800 dup_factor=1
# 輸出：  user=u03 name=Charlie total=4500 dup_factor=1

第一段是正確寫法：每位客戶的訂單合計。第二段是踩雷示範：把 orders 與 order_items 多 JOIN 一次後，Alice 的訂單有三筆明細，每筆明細又把訂單金額 SUM 一次，造成 total=11100、dup_factor=3。實務上多對多 JOIN 一定要小心「先聚合再 JOIN」或「最後用 DISTINCT／視窗消除重複」。Day 24 維度建模會用 LATERAL JOIN 與 CTE 處理。

第三個雷：NULL 對 NULL 的相等比較。WHERE a.col = b.col 當兩邊都是 NULL 時永遠不成立（這是 SQL 三值邏輯：三個值：TRUE、FALSE、UNKNOWN）。如果你的資料有 NULL 常見於「欄位未填」，要用 a.col IS NOT DISTINCT FROM b.col（DuckDB 與 PostgreSQL 都支援）：

result = con.execute("""
    SELECT a.user_id AS a_user, b.user_id AS b_user,
           a.user_id = b.user_id AS standard_eq,
           a.user_id IS NOT DISTINCT FROM b.user_id AS null_safe_eq
    FROM (VALUES (NULL), ('u01')) AS a(user_id)
    CROSS JOIN (VALUES (NULL), ('u01')) AS b(user_id)
""").fetchall()

print("NULL 比對差異：")
for r in result:
    print(f"  a={r[0]} b={r[1]} standard_eq={r[2]} null_safe_eq={r[3]}")
# 輸出：NULL 比對差異：
# 輸出：  a=None b=None standard_eq=None null_safe_eq=True
# 輸出：  a=None b=u01 standard_eq=None null_safe_eq=False
# 輸出：  a=u01 b=None standard_eq=None null_safe_eq=False
# 輸出：  a=u01 b=u01 standard_eq=True null_safe_eq=True

這個示意說明：當你希望兩個 NULL 視為相等（這在做資料合併、找重複時常見），請用 IS NOT DISTINCT FROM，它會把 NULL=NULL 視為 TRUE。這是 SQL 標準在 NULL 處理上的關鍵寫法，整個系列會在 Day 14 資料品質章節再次用到。

第四個雷：跨資料庫的 JOIN 細節差異。例如 DuckDB 對 SEMI JOIN、ANTI JOIN 支援完整；PostgreSQL 透過 EXISTS 表達；MySQL 8 透過 IN (SELECT ...) 表達較好。請在跨平台部署時依目標引擎調整。

效能與實務提醒

聯結的效能很大程度受三個因素影響：聯結順序、聯結條件與索引、聯結策略。聯結順序是指「先聯結較小的表」通常比較好，因為後續聯結要處理的列數會大幅下降。聯結條件與索引是 Day 7 的主題：當聯結欄位有索引時，nested loop 聯結可以走索引掃描而不必全表掃描。聯結策略（hash、merge、nested loop）由查詢最佳化器決定，可以用 EXPLAIN 查看。

實務上，當一個查詢需要聯結四張以上表時，建議先在 CTE 內對每張表做必要的彙總與篩選，再做最終聯結。例如：

result = con.execute("""
    WITH active_customers AS (
        SELECT user_id, name FROM customers WHERE city IN ('Taipei', 'Taichung')
    ),
    recent_orders AS (
        SELECT order_id, user_id, amount
        FROM orders
        WHERE order_date >= DATE '2025-11-01'
    )
    SELECT ac.name, SUM(ro.amount) AS total
    FROM active_customers ac
    JOIN recent_orders ro ON ac.user_id = ro.user_id
    GROUP BY ac.name
    ORDER BY total DESC
""").fetchall()

print("先 CTE 篩選、再 JOIN：")
for r in result:
    print(f"  name={r[0]} total={r[1]}")
# 輸出：先 CTE 篩選、再 JOIN：
# 輸出：  name=Alice total=3700
# 輸出：  name=Bob total=800

這個寫法把 customers 先過濾成「台北、台中」的活躍客戶，再把 orders 過濾成「11 月份」的訂單，最後 JOIN。DuckDB 與 PostgreSQL 的查詢最佳化器通常能識別這個模式並做對應的最佳化。把這個技巧當成聯結查詢的標準樣板，遇到多對多或大資料量時就先想「怎麼先縮小邊」。

另一個提醒：當 LEFT JOIN 中右表有多筆對應到左表同一列時（多對一），結果會膨脹成「左表列數 × 右表每群列數」。如果要避免膨脹，常見手法是先用 ROW_NUMBER() 給右表去重再聯結：

result = con.execute("""
    WITH ranked_orders AS (
        SELECT user_id, order_date, amount,
               ROW_NUMBER() OVER (
                   PARTITION BY user_id ORDER BY order_date DESC
               ) AS rn
        FROM orders
    )
    SELECT c.user_id, c.name, ro.order_date, ro.amount
    FROM customers c
    LEFT JOIN ranked_orders ro
        ON c.user_id = ro.user_id AND ro.rn = 1
    ORDER BY c.user_id
""").fetchall()

print("每位客戶最近一筆訂單（避免膨脹）：")
for r in result:
    print(f"  user={r[0]} name={r[1]} date={r[2]} amount={r[3]}")
# 輸出：每位客戶最近一筆訂單（避免膨脹）：
# 輸出：  user=u01 name=Alice date=2025-11-05 amount=2200
# 輸出：  user=u02 name=Bob date=2025-11-03 amount=800
# 輸出：  user=u03 name=Charlie date=2025-11-08 amount=4500
# 輸出：  user=u04 name=Diana date=None amount=None

這段用 ROW_NUMBER() 把每位使用者的訂單編號，再用 rn = 1 過濾出最近一筆；最後用 LEFT JOIN 把結果與客戶表合併，Diana 因為沒下單所以日期與金額是 NULL。這就是「每位客戶最新一筆訂單」這類典型報表的標準寫法，Day 14 與 Day 24 都會大量複用。

小結

今天把五種聯結與半聯結、自連接、多對多一次走完。我們用「客戶與訂單」五筆資料建立示範表，示範了 INNER、LEFT、RIGHT、FULL OUTER、CROSS、SEMI、ANTI 七種聯結在結果上的差異；展示自連接怎麼把員工對應主管；以及多對多關係如何在中介表中正確展開。最後把常見雷點彙整：「WHERE 把 LEFT JOIN 變 INNER JOIN」、「重複列爆炸」、「NULL 對 NULL」、「跨平台語法差異」都是真實工作中最容易踩到的坑。

把今天學到的關鍵詞抄進筆記本：INNER、LEFT、RIGHT、FULL OUTER、CROSS、SEMI、ANTI、EXISTS、NOT EXISTS、自連接、中介表、NULL 比較。明天進入 SQL 進階區塊的最後一篇：EXPLAIN 與索引實戰。我們會用 DuckDB 看查詢計畫怎麼讀、SET 對執行計畫的影響、索引怎麼建、怎麼量測查詢時間。

結語

聯結是 SQL 的核心，但也是最容易「看起來對、數字錯」的地方。我們今天用實際資料展示每種聯結的差異，把模糊的觀念變成能直接判斷的視覺模式。接下來 Day 7 會把這些查詢送到 EXPLAIN 與 EXPLAIN ANALYZE 看查詢計畫，那時你會看到「為什麼 LEFT JOIN 反而比 INNER JOIN 慢」、「為什麼加了索引查詢反而更慢」的迷思破解。請把今天可整段執行的客戶 / 訂單範例保留在工作目錄裡，這份基礎在 Day 7、Day 18、Day 24 都會反覆用到。

明天，我們會進入 SQL 進階區塊的最後一篇：「查詢計畫與索引：EXPLAIN 實戰」。我們會展示怎麼讀 DuckDB 的 EXPLAIN 輸出，怎麼用 EXPLAIN ANALYZE 拿到實際執行時間，怎麼在 DuckDB 上建索引與刪索引，並做一個基準測試看出索引對查詢時間的影響。沒有 EXPLAIN 的 SQL 寫作就像盲人摸象，看完這一篇你會拿到最後一塊拼圖。

延伸資源

  • DuckDB JOIN 與 SEMI/ANTI 語法：https://duckdb.org/docs/sql/query_syntax/from.html。本篇 SEMI JOIN、ANTI JOIN 用法以這份為主。
  • PostgreSQL JOIN 語法文件：https://www.postgresql.org/docs/current/queries-join.html。
  • 《SQL Antipatterns》（書籍）：Karwin，深入討論重複列爆炸、NULL 處理。
  • Use The Index, Luke! 索引教學網站：https://use-the-index-luke.com/。SQL 查詢效能與索引的權威指南。
  • DuckDB CLI 範例：duckdb -c "EXPLAIN ANALYZE SELECT ..." 一次執行並看計畫。
標籤：

留言

張貼留言

這個網誌中的熱門文章

Day 2 變數與資料型別

Day 2 變數與資料型別 引言 寫程式的過程中，變數與資料型別是處理資料的基礎。變數是存放資料的容器，資料型別則決定這筆資料有哪些特性、可以進行哪些操作。學會定義變數、認識各種資料型別，是學好 Python 的關鍵一步。 這篇文章會帶你了解 Python 中變數的觀念、如何定義變數，以及常見的資料型別，包括整數、浮點數、字串、布林值，還有串列、元組、字典與集合等容器型別。我們也會介紹變數的命名規則與撰寫風格建議，以及如何用 type() 檢查資料型別。 什麼是變數？如何在 Python 中定義變數 變數是在程式執行時用來存放資料的名稱。透過定義變數，我們可以給一筆資料一個名字，並在程式的其他地方用這個名字取用該筆資料。在 Python 中，變數不需要事先宣告型別，因為 Python 是動態型別語言，變數的型別由指定給它的值決定。 定義變數的基本語法 在 Python 中定義變數非常簡單，只要用賦值符號 = 把值指定給變數即可。例如： x = 5 # 定義變數 x，並把整數 5 賦值給它 name = "Alice" # 定義變數 name，並把字串 "Alice" 賦值給它 在這裡，x 是一個變數，被賦予整數 5；name 是另一個變數，被賦予字串 "Alice"。 變數的更新與覆寫 變數的值可以修改，也就是說，我們可以在程式的不同地方給同一個變數新的值。例如： x = 10 # x 最初被賦予 10 x = 15 # x 的值現在被更新為 15 這樣就能依照需求，在程式執行過程中靈活調整變數的值。 Python 的動態型別系統 Python 和某些靜態型別語言不同，定義變數時不需要宣告型別。賦值時，Python 會根據值自動判斷變數的型別。例如： x = 5 # x 是整數 x = 3.14 # x 變成浮點數 x = "Hi" # x 變成字串 同一個變數在程式執行過程中可以存放不同型別的值，這是 Python 的彈性之一。 常見資料型別 在 Python 中，資料型別決定我們可以對變數進行哪些操作...
張貼留言
閱讀完整內容

Day 1 Python 簡介與環境設定

Day 1 Python 簡介與環境設定 引言 在現在的科技環境裡，程式設計已經是一項重要技能。無論你是對資料科學有興趣、想成為開發者，或是想踏入人工智慧（AI）領域，學會寫程式都能明顯提升你的競爭力。在眾多程式語言中，Python 因為語法簡單、功能強大、應用範圍廣泛，成為許多人進入程式世界的第一選擇。這篇文章會帶你認識 Python 的背景與優勢，並一步步教你在不同系統上安裝與設定 Python 開發環境，最後寫出第一支 Python 程式。 為什麼選擇 Python？ Python 是一種高階程式語言，由 Guido van Rossum 在 1991 年發布。Python 的設計哲學強調程式碼的可讀性，並用縮排來定義程式區塊，這點和許多使用大括號的語言不同。簡潔的語法讓它成為初學者的理想選擇；就算是經驗豐富的開發者，也能用它完成複雜的專案。 Python 的優勢如下： 簡單易學 ：Python 的語法清楚、結構簡潔，初學者很快就能上手。和其他語言相比，學習曲線相對平緩，不需要先弄懂一堆複雜觀念，就能開始寫程式。 應用範圍廣泛 ：從資料科學、網頁開發、人工智慧、機器學習、自動化測試到網路爬蟲，Python 都有大量開源函式庫與工具支援，而且在這些領域都扮演關鍵角色。 豐富的函式庫與框架 ：Python 的函式庫生態系非常龐大。做資料分析有 NumPy、Pandas；開發網站有 Django、Flask；做深度學習有 TensorFlow、PyTorch。各種需求幾乎都能找到對應的套件，讓開發更有效率。 跨平台支援 ：Python 支援 Windows、macOS、Linux 等作業系統，程式通常不需要太多修改就能跨平台執行，讓開發與部署更有彈性。 活躍的社群 ：Python 擁有龐大的開發者社群。學習或開發上遇到問題，幾乎都能在社群與論壇（例如 Stack Overflow）找到答案，對初學者來說是很強的後盾，也能減少卡關時的挫折感。 Python 的應用領域 Python 的流行與強大功能，讓許多領域都開始大量使用它。以下是幾個常見的應用方向： 資料科學 ：隨著大數據與人工智慧興起，資料科學大量使用 Python。NumPy、Pandas 與 Matplotlib 等工具能處理和分析龐...
張貼留言
閱讀完整內容

Python 從入門到 PyTorch 深度學習：開啟 AI 世界的大門

Python 從入門到 PyTorch 深度學習：開啟 AI 世界的大門 隨著人工智慧（AI）與深度學習（Deep Learning）快速發展，越來越多人對這些技術產生興趣。不論你是想踏入 AI 領域的初學者，還是已經有程式基礎的開發者，學好 Python 與深度學習框架（例如 PyTorch），都能為你打開更多可能。 為什麼選擇 Python？ Python 已經是資料科學與人工智慧領域的首選語言。它的語法簡潔、容易上手，而且擁有龐大的生態系與大量開源函式庫。無論是資料處理、資料視覺化，還是建立機器學習與深度學習模型，Python 都能勝任。對想進入 AI 或資料科學領域的人來說，它幾乎是必備工具。 PyTorch 是什麼？ PyTorch 是由 Meta（原 Facebook）AI 研究團隊開發的開源深度學習框架，以易用、靈活和動態計算圖著稱，是許多 AI 研究人員與開發者的首選。相較於其他框架，PyTorch 的寫法更貼近原生 Python，對初學者相對友善。無論是簡單的實驗，還是複雜的深度學習模型，PyTorch 都能提供強大的支援。 這個系列能帶給你什麼？ 這個系列會從 Python 的基礎開始，帶你一步一步學習，最後能自己用 PyTorch 建立深度學習模型。即使你完全沒有寫過程式，也能跟著文章的節奏累積技能，理解 AI 與深度學習的核心觀念。 本系列涵蓋的主題 Python 基礎：從變數、條件判斷到函式與模組。 資料處理工具：用 NumPy 與 Pandas 有效率地操作資料。 資料視覺化：用 Matplotlib 與 Seaborn 把資料畫成圖表。 深度學習的數學基礎：線性代數、微積分與機率。 PyTorch 入門：理解張量、模型建構與 GPU 加速。 基礎深度學習模型：CNN 與 RNN 的實作應用。 深度學習專案實戰：從資料前處理到模型部署的端到端流程。 誰適合這個系列？ 程式初學者 ：如果你對 AI 充滿好奇，卻還沒寫過程式，系列的第一部分會帶你快速上手 Python，並幫助你理解深度學習的基本觀念。 資料科學愛好者 ：如果你已經熟悉一些資料處理方法，進階部分會教你如何用 PyTorch 建構深度學習模型。 開發者與研究人員 ：想更深入了...
張貼留言
閱讀完整內容