JOIN、NULL 與集合運算：先確保多表查詢是對的

大多數 join bug 都不是語法錯誤。查詢會執行，也會回傳看似合理的資料，只是資料的意思已經悄悄改變。

先說清楚 cardinality

寫 SQL 前，先寫出關係：

• 一位 customer 有多張 order；
• 一張 order 有多個 item；
• Product 和 order 透過 order item 形成 many-to-many。

十張訂單 join items 後變成八十 rows，可能完全正確。但下一步若加總 orders.total，通常就錯了，因為每張訂單總額會隨 item 數量重複。

白話來說： Join 只負責組合符合條件的 rows，不知道哪些數字可以安全加總。相信 aggregate 前，先預測 join 後應該有幾列。

INNER 與 LEFT JOIN 回答不同問題

SELECT c.id, o.id
FROM customer c
JOIN orders o ON o.customer_id = c.id;

這是在找「有訂單的 customer」。若要保留沒有訂單的人，必須使用 left join：

SELECT c.id, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id;

條件放在哪裡也會改變答案：

-- 保留所有 customer；只有 paid order 能配對。
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid'

-- 移除沒有 paid order 的 customer，效果接近 inner join。
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'

第一個條件決定如何配對；第二個是在配對完成後過濾結果。沒有配對的右側欄位已經是 NULL，自然無法通過 WHERE o.status = 'paid'。

Anti-join 優先使用 NOT EXISTS

下面的查詢看似合理，卻很脆弱：

SELECT * FROM paper
WHERE venue_id NOT IN (SELECT venue_id FROM blacklist);

只要子查詢回傳一個 NULL，比較結果就可能變成 UNKNOWN，最後一列都沒有。直接表達「不存在配對資料」比較安全：

SELECT p.*
FROM paper p
WHERE NOT EXISTS (
  SELECT 1
  FROM blacklist b
  WHERE b.venue_id = p.venue_id
);

EXISTS 與 NOT EXISTS 檢查的是有沒有配對 row，不必把值拿去和可能含有未知值的清單逐一比較。

UNION 不是單純串接

UNION 合併結果後還會去重；UNION ALL 只把兩批資料接起來。

SELECT email FROM current_member
UNION ALL
SELECT email FROM archived_member;

兩邊若代表不同事件，或重複本身有意義，就應使用 UNION ALL。只有結果必須符合 set semantics 時，才值得支付 UNION 的排序或 hash 去重成本。

INTERSECT 保留兩邊都有的 row；EXCEPT 保留左邊有、右邊沒有的 row。所有 set operation 都要求欄位數量與型別相容。

正確性檢查表

複習卡

• Aggregate 前先預測 join cardinality。
• WHERE 的右表條件可能讓 left join 失去 outer 語意。
• 使用 IS NULL，不要寫 = NULL。
• NULL-safe anti-join 優先用 NOT EXISTS。
• UNION ALL 是串接；UNION 還會去重。