JOIN 的真相:先算笛卡爾積,再過濾

上一篇把「你寫的順序不是它跑的順序」講清楚了。這篇用同一把鑰匙拆穿 JOIN。很多人把 JOIN 想成「把兩張表黏在一起」,然後死背 INNER/LEFT/RIGHT/FULL 各自的行為。但其實它們共用一個模型:先列出兩表所有列的組合(笛卡爾積),再用 ON 過濾。懂這句,連最常見的 LEFT JOIN 陷阱都會一次看穿。

所有 JOIN 只有一個模型

概念上,A JOIN B ON 條件 做的是兩步:① 把 A 的每一列,配上 B 的每一列(笛卡爾積);② 只留下「條件」成立的組合。 差別只在「過濾後,要不要幫沒配到的列補一筆」:

把左表每列配右表每列(9 格),再用 ON 留下 key 相等的(綠格)。INNER = 只要綠格(3 列);LEFT = 綠格 + 幫沒配到的 C 補一列 NULL(4 列)。順帶注意:A 配到兩筆,結果就出現兩次——這是 join 的「放大」副作用

有了這個模型,四種 JOIN 就不用背了,它們只是「過濾後保留哪側」的差別:

INNER:只留綠格(兩邊都配得到的)。
LEFT:保證左表每列至少出現一次,右邊沒配到就補 NULL。
RIGHT:反過來,保證右表每列都在。
FULL:兩邊都保,誰沒配到誰補 NULL。
CROSS:根本不過濾,就是那張完整的笛卡爾積(9 格全要)。

還有一個一定要記的副作用:一對多會讓列數變多(fan-out)。 上圖 A 配到 2 筆訂單,結果 A 就出現 2 列。這在你 join 完又 SUM 時會重複計算——是報表金額莫名變大的經典元兇。

那笛卡爾積,不會把記憶體撐爆嗎?

這是「笛卡爾積再過濾」最容易嚇到人的地方:兩張各一百萬列的表,乘起來是 10^12 列——真要算出來,哪台機器扛得住?好消息是:那個乘積是「邏輯模型」,是幫你想對答案用的,引擎從不會真的把它物化出來。 優化器會挑一種 join 演算法,邊配對邊把不符的丟掉,整個乘積從頭到尾不落地:

左邊那張 N×M 的表只存在你腦中,幫你推導答案;引擎實際是挑一種演算法(這裡以 Hash Join 為例),邊配對邊丟,乘積從不落地。所以單一個 join,不會吃 N×M 的記憶體

那 join 到底吃不吃記憶體?吃,但成本不在「乘積」,而在這兩個地方。

一、join 演算法本身。 優化器會依表的大小、有沒有索引、有沒有排好序,挑一種做法,而它們的記憶體胃口差很多:

Nested Loop:外表每一列,去內表逐一找。幾乎不吃額外記憶體,但沒索引時很慢——適合小表,或內表剛好有索引可查。
Hash Join:把較小那側建成一個 hash 表塞進記憶體,大表流過來比對。記憶體 ∝ 小表大小,這是 join 最常見的吃記憶體點。
Merge Join:要求兩側已排序;沒排好就得先排,而排序用的 buffer 也吃記憶體。

(這三種怎麼在 EXPLAIN 裡認出來、優化器憑什麼挑,是這個系列後面會專門講的一篇。這裡先抓一句就好:join 的記憶體主要來自 hash 表和排序,不是那個乘積。)

二、fan-out 把結果撐大。 join 本身不物化乘積,但一對多把輸出列數放大是實實在在的:A 配到 100 筆,結果就有 100 列。這個放大後的結果真的存在,接下來你再 ORDER BY、GROUP BY 它,排序和聚合就得處理更多資料。所以 join 的記憶體痛點,常常不在 join 那一步,而在它放大後餵給下游那一步。

在 PostgreSQL,hash 表和排序這些節點能用多少記憶體,由 work_mem 這個參數管。吃超過上限,它會 spill 到磁碟(寫暫存檔)——結果是變慢,不是直接 OOM,這是它的安全閥。要小心的反而是另一頭:work_mem 開太大、又有很多節點平行跑,加起來才可能真的把整台機器的記憶體吃爆。

你其實在 Spark 那篇已經看過這件事的極端版:broadcast join 把小表複製到每一個 executor,本質就是「Hash Join 的 build 側」搬到分散式——小表太大,每台的記憶體會一起爆。單機的 work_mem spill、分散式的 broadcast 上限,是同一個道理的兩種尺度。

那個幾乎人人踩過的 LEFT JOIN 陷阱

你想「列出所有使用者,連同他們已付款的訂單,沒訂單的使用者也要留著」。很自然會這樣寫:

SELECT u.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';   -- ❌ 沒訂單的使用者,在這裡被砍光了

跑出來你會發現:沒訂單的使用者全不見了,LEFT JOIN 好像沒作用。原因正是上一篇那張執行順序圖——WHERE 在 JOIN 之後才跑:

LEFT JOIN 先幫沒訂單的 C 補上 NULL。條件放 WHERE,它在 join 之後跑,NULL = 'paid' 不成立,C 被砍 → 悄悄變回 INNER。放進 ON,過濾在 join 當下發生,C 就留得住

修法是把「對右表的條件」搬進 ON,讓它在 join 當下就參與過濾,而不是等 join 完再用 WHERE 砍:

SELECT u.id, o.amount
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid';  -- ✅ 所有使用者都留著

一句判準記起來:對「要保留全部」那側(LEFT 的左表)的過濾,放 WHERE 沒問題;對「可有可無」那側(右表)的過濾,要放 ON,不然就把 LEFT 打回 INNER。

反思

JOIN 不是「黏表」,是「組合再過濾」

我剛學 SQL 時,INNER/LEFT/RIGHT/FULL 是四條各自要背的規則,還常常搞混 LEFT 到底保留哪邊。真正讓我不再背的,是換成「笛卡爾積再過濾」這個單一模型——四種 JOIN 只是同一件事「過濾後保留哪側」的四種選擇。這跟上一篇的收穫是同一種:把一堆要死記的規則,還原成一個能推導的機制。 一旦模型對了,你不只是「記得」LEFT 怎麼運作,而是「能算出」任何 join 會吐什麼——包括那些奇怪的邊界情況。

LEFT JOIN 退化成 INNER,是我 code review 最常抓到的 bug

這個坑陰險在於:它跑得出來、不報錯、數字看起來也「像對的」,只是悄悄少了一批資料。我 review 別人的報表 SQL,只要看到「LEFT JOIN + 又在 WHERE 篩右表欄位」,幾乎都會停下來問一句「你確定不想留沒配到的那些嗎?」十次有七八次是 bug。而它的根,就是執行順序:WHERE 在 join 之後。這也是為什麼我把執行順序那篇當整個系列的脊椎——很多 SQL bug 不是語法錯,是你對『什麼時候發生』的直覺錯了。

join 完要 SUM 之前,先數一下列數

fan-out(一對多讓列數相乘)是另一個「跑得出來但算錯」的經典。我養成一個習慣:任何 join 之前,先問一句「這個 key 在右表是唯一的嗎?」 不唯一,就代表 join 後左表那側的列會被複製,這時直接 SUM 就會重複計算。解法通常是「先把右表聚合成一列、再 join」,或改用 window function。這個「先確認基數(cardinality)再 join」的習慣,幫我擋掉太多金額對不起來的怪事——在資料的世界,能跑出數字從來不等於數字是對的。