小編精選 - 技術文章翻譯 · 07月14日

LEFT JOIN 到底什麼時候會被最佳化器偷偷改寫成 INNER JOIN

LEFT JOIN 到底什麼時候會被優化器偷偷改寫成 INNER JOIN

LEFT JOIN 到底什麼時候會被優化器偷偷改寫成 INNER JOIN

寫 LEFT JOIN 的時候，大多數人心裡的預期都很單純：左表的資料無論如何都會保留，右表匹配不上就是 NULL。這個預期在大多數場景下是成立的，但只要 WHERE 子句裡對右表欄位加了一個過濾條件，這個預期就可能悄悄崩掉——SQL 文字裡寫的明明是 LEFT JOIN，執行計畫裡跑出來的卻是一個不折不扣的內連接，結果集也跟著變。

這篇不重現業務場景，直接用最乾淨的兩張表把這個機制的邊界條件一個個測一遍：什麼條件會觸發這種改寫、什麼條件不會、條件放在不同位置結果差多少。環境還是 KES V009R001C010，業務帳號連線：

bash 代碼解讀複製代碼ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

搭一個最小的測試台

兩張表，t_oje_t1 是左表，t_oje_t2 是右表：

sql 代碼解讀複製代碼create table app_schema.t_oje_t1 (
  id1 integer primary key,
  name1 varchar(30) not null
);

create table app_schema.t_oje_t2 (
  id2 integer primary key,
  id1 integer not null,
  name2 varchar(30) not null
);

insert into app_schema.t_oje_t1(id1, name1) values
  (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

insert into app_schema.t_oje_t2(id2, id1, name2) values
  (101, 1, 'aa'),
  (102, 2, 'bb'),
  (103, 2, 'cc'),
  (104, 3, 'cc');

資料故意設計成這樣：t1 有 4 行，id1=4 在 t2 裡完全沒有匹配（模擬「右表缺失」），id1=2 在 t2 裡對應兩筆記錄（bb 和 cc，模擬一對多）。後面會看到，這個「一對多」的設計埋了一個很關鍵的坑，先賣個關子。

第一步：右表條件放 WHERE，外連接直接沒了

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 = 'cc';

where過濾右表欄位外連接被消除

執行計畫第一行是 Hash Join，注意——不是 Hash Left Join。KES 這邊只要真的按外連接執行，計畫裡都會老老實實帶上 Left 這個字樣，這裡沒有，說明這條 LEFT JOIN 在最佳化階段就已經被改寫成了普通內連接。往下看，t_oje_t2 被 Seq Scan 掃描，帶了個 Filter: (name2)::text = 'cc'::text)，Rows Removed by Filter: 2——t2 總共 4 行，過濾後只剩 2 行（id1=2 的 cc、id1=3 的 cc），這 2 行再去跟 t1 做 Hash Join，最終 actual rows=2。

道理很直白：t2.name2 = 'cc' 這個條件，對 id1=1（沒有 cc 匹配）和 id1=4（t2 裡根本沒資料，欄位全是 NULL）來說，NULL = 'cc' 的結果既不是真也不是假，而是「未知」，在 WHERE 裡「未知」就等於被扔掉。既然外連接產生的這些 NULL 行反正都要被過濾掉，那「外連接 + 這個過濾」和「內連接 + 這個過濾」結果完全一樣，優化器一看這筆帳划算，就直接換成成本更小的內連接去跑了。這就是外連接消除。

第二步：換成 IS NULL，結果完全反過來

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t2.name2 is null;

is null條件不會被消除

這次執行計畫顯示的是 Hash Right Join，Filter: (t2.name2 IS NULL)，Rows Removed by Filter: 4，最終 actual rows=1——也就是 id1=4 那一行。

這裡有個細節容易讓人愣一下：計畫裡寫的是 Right Join，不是 Left Join。這不是消除，是優化器把驅動表和探測表的位置換了一下（先掃 t2 建 hash 表，再拿 t1 去探測），但語義上 t1 LEFT JOIN t2 和這裡的 t2 做 build 端、t1 做 probe 端的 Right Join 是完全等價的外連接，只是物理執行順序反過來了，外連接的「保底」語義一點沒丟。跟第一步的 Hash Join（沒有 Left/Right 字樣，純內連接）完全是兩碼事，不要混在一起看。

為什麼這次不能消除：IS NULL 這個條件本身就是專門用來抓外連接產生的 NULL 行的。如果把外連接改成內連接，t2 裡沒匹配的行根本進不了結果集，t2.name2 is null 就永遠不可能為真，這已經不是「用更快的方式得到同樣結果」，是徹底改變了查詢語義，所以優化器不會碰這種條件。

判斷標準到這裡就很清楚了：能不能消除，看這個 WHERE 條件對外連接產生的 NULL 值判定成什麼——一定是假或未知（比如普通等值比較），消除是安全的；有可能判定成真（比如 IS NULL），消除就會改變結果，優化器不會做。

第三步：條件換到左表，跟外連接消除沒關係

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2 on t1.id1 = t2.id1
where t1.name1 = 'b';

條件在左表不涉及外連接消除

這次執行計畫是 Nested Loop Left Join，Left 字樣老老實實地在，是真正的外連接。Join Filter: (t1.id1 = t2.id1)，t_oje_t1 先按 Filter: (name1)::text = 'b'::text 篩出 1 行（id1=2），再拿這一行去跟 t2 做外連接，最終 actual rows=2——因為 id1=2 在 t2 裡有兩筆匹配（bb、cc），一筆左表資料關聯出兩筆結果，這跟外連接消除完全不搭邊。

這一步的過濾本質上是「先決定左表要哪些行，再拿這些行去外連接」，跟右表的 Nullable 特性沒有任何關係。很多人容易把「WHERE 裡出現的任何條件」都當成外連接消除的誘因，這一步就是用來打破這個誤解的對照組。

第四步：把左表條件挪進 ON，結果比想像中多了一行

到這一步本來想驗證的是：左表條件放進 ON，會不會跟「右表條件放 WHERE」一樣有什麼隱藏效應。

sql 代碼解讀複製代碼select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t1.name1 = 'b';

左表條件放on裡不過濾左表行數

原本設想的結果是 4 行——t1 全量保留，只是 name1 <> 'b' 的行 t2 欄位全是 NULL。跑出來一看，是 5 行，多了一行。仔細看輸出：id1=2 這一行出現了兩次，一次對應 id2=102/bb，一次對應 id2=103/cc；id1=1、3、4 各出現一次，t2 相關欄位全是 NULL。

想明白這事之後覺得挺合理的：t1.name1='b' 這個條件放進 ON，只是告訴優化器「只有 name1='b' 的左表行才允許去匹配右表」，它管的是「允不允許連」，不管「連上以後右邊能出幾行」。id1=2 滿足 name1='b'，於是它就拿著 id1=2 去 t2 裡找所有 id1=2 的記錄——而 t2 裡 id1=2 本來就有兩筆，兩筆全都會被連出來，一筆也不會因為「外連接只保底一行」就被合併掉。左連接的「保底」只保證左表這一行至少出現一次，從沒保證只出現一次。

這跟前面認為的「4 行」錯在哪裡：想當然地把「左表 4 行」和「結果 4 行」劃了等號，卻忘了右表本來就有一對多的資料。這個坑其實挺常見，很多人寫業務 SQL 的時候，只要 JOIN 的右表存在一對多關係，加不加條件、條件放哪，行數都可能跟「左表有幾行」對不上，得先確認清楚右表對應關係，再看結果對不對得上。

第五步：右表條件挪進 ON，才是真正保住外連接語義的寫法

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1
left join app_schema.t_oje_t2 t2
  on t1.id1 = t2.id1 and t2.name2 = 'cc';

條件下推到on後保留左表全量

這次執行計畫是 Hash Left Join，Left 字樣穩穩地在，actual rows=4。跟第四步不一樣的地方在於：這次的過濾條件 t2.name2='cc' 作用在右表上，它會先把 t2 過濾成只剩滿足 name2='cc' 的行（id1=2 的 cc、id1=3 的 cc，id1=2 的 bb 被擋在外面），過濾完的 t2 每個 id1 最多只剩一筆，再拿去跟 t1 做外連接，t1 的 4 行每行正好對應 1 行結果，id1=1、id1=4 的 t2 欄位是 NULL，id1=2、id1=3 有值。

對比第一步就很清楚了：同樣是想找「關聯到 name2='cc' 的記錄」，條件放 WHERE 會把 t1 裡沒匹配上的行連帶殺掉（外連接被消除），條件放 ON 才能既過濾右表又保住左表全量。這是這篇最該記住的一條規則：對右表的過濾，只要目的是保留外連接語義，就該放 ON，不要放 WHERE。而對左表的過濾（第四步），放 ON 和放 WHERE 效果是不一樣的——放 WHERE 會先篩左表再連接，放 ON 只決定「篩出來的左表行允不允許連」，右表該出幾行還是出幾行，兩者不能混著記。

第六步：Oracle `(+)` 寫法，同樣的規則換個皮

KES 相容 Oracle 風格的 (+) 外連接寫法，實測看它是不是遵循同一套規則：

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2 = 'cc';

plus語法where不帶plus同樣被消除

執行計畫是 Hash Join，沒有 Left/Right 字樣，actual rows=2，跟第一步的 WHERE 寫法結果一模一樣——過濾條件不帶 (+)，外連接照樣被消除。再看過濾條件也帶上 (+) 的寫法：

sql 代碼解讀複製代碼explain analyze
select * from app_schema.t_oje_t1 t1, app_schema.t_oje_t2 t2
where t1.id1 = t2.id1(+)
  and t2.name2(+) = 'cc';

plus語法過濾條件也帶plus的結果

這次是 Hash Left Join，actual rows=4，和第五步條件下推到 ON 的結果完全一致。這說明 (+) 寫法底層走的是同一套判斷邏輯，(+) 只是外連接的另一種語法糖，不代表寫了它外連接就一定被保留——過濾條件要不要跟著寫 (+)，效果跟「條件放 WHERE 還是放 ON」是對應的。這一點對從 Oracle 遷移過來的讀者尤其要注意：老程式碼裡如果只在連接條件上寫了 (+)，後面又單獨加了一個不帶 (+) 的過濾條件，一樣會被判定為可以安全消除成內連接。

收個尾：怎麼在自己的 SQL 裡稽核這個問題

跑一遍下來，判斷標準可以歸成幾條：

看 SQL 文字裡有沒有 LEFT/RIGHT JOIN 或 (+)，這是稽核的起點。
跑一遍 EXPLAIN ANALYZE，盯住連接節點是不是明確帶 Left/Right 字樣。不帶的話基本可以確定被消除了。
對照 WHERE 子句，看是不是對右表（Nullable 一側）欄位做了等值、範圍、IN 之類的過濾，且沒用 IS NULL/IS NOT NULL——這類條件是觸發消除的典型信號。
右表過濾條件想保住外連接語義，下推到 ON；(+) 寫法下也是同樣道理，過濾條件要不要帶 (+)，跟對應關係走。
左表條件放 ON 和放 WHERE 不是一回事：放 WHERE 是先篩左表再連接，放 ON 只決定這行左表能不能參與連接，右表該出幾行還是出幾行——尤其右表存在一對多關係時，千萬別拿左表行數直接套右表行數。

這幾條規則說到底都是一件事：外連接消不消除，從來不取決於你寫沒寫 LEFT，而是取決於這個查詢的整體邏輯，跟內連接放在一起算，結果是不是完全一樣。只要有一點點不一樣（哪怕只是多保留一個 NULL 行的可能性），優化器就不會去消除；只要完全一樣，它就一定會去消除，圖的就是內連接更便宜這點執行開銷。寫 SQL 的時候腦子裡想的是「業務要不要保底」，資料庫執行的時候看的是「邏輯上能不能劃等號」，這中間的落差，就是這一類坑的根源。

原文出處：https://juejin.cn/post/7660896500881981492