寫 LEFT JOIN 的時候，大部分人心里的預(yù)期都很樸素：左表的數(shù)據(jù)無論如何都會保留，右表匹配不上就是 NULL。這個預(yù)期在大多數(shù)場景下是成立的，但只要 WHERE 子句里對右表字段加了一個過濾條件，這個預(yù)期就可能悄悄崩掉——SQL 文本里寫的明明是 LEFT JOIN，執(zhí)行計劃里跑出來的卻是一個不折不扣的內(nèi)連接，結(jié)果集也跟著變。

這篇不復(fù)現(xiàn)業(yè)務(wù)場景，直接用最干凈的兩張表把這個機(jī)制的邊界條件挨個測一遍：什么條件會觸發(fā)這種改寫、什么條件不會、條件放在不同位置結(jié)果差多少。環(huán)境還是 KES V009R001C010，業(yè)務(wù)賬號連接：

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

搭一個最小的測試臺

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

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');

數(shù)據(jù)故意設(shè)計成這樣：t1 有 4 行，id1=4 在 t2 里完全沒有匹配（模擬"右表缺失"），id1=2 在 t2 里對應(yīng)兩條記錄（bb 和 cc，模擬一對多）。后面會看到，這個"一對多"的設(shè)計埋了一個很關(guān)鍵的坑，先賣個關(guān)子。

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

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';

執(zhí)行計劃第一行是 Hash Join，注意——不是 Hash Left Join。KES 這邊只要真按外連接執(zhí)行，計劃里都會老老實實帶上 Left 這個字樣，這里沒有，說明這條 LEFT JOIN 在優(yōu)化階段就已經(jīng)被改寫成了普通內(nèi)連接。往下看，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 里壓根沒數(shù)據(jù)，字段全是 NULL）來說，NULL = 'cc' 的結(jié)果既不是真也不是假，是"未知"，在 WHERE 里"未知"就等于被扔掉。既然外連接產(chǎn)生的這些 NULL 行反正都要被過濾掉，那"外連接 + 這個過濾"和"內(nèi)連接 + 這個過濾"結(jié)果完全一樣，優(yōu)化器一看這筆賬劃算，就直接換成開銷更小的內(nèi)連接去跑了。這就是外連接消除。

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

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;

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

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

為什么這次不能消除：IS NULL 這個條件本身就是專門用來抓外連接產(chǎn)生的 NULL 行的。如果把外連接改成內(nèi)連接，t2 里沒匹配的行根本進(jìn)不了結(jié)果集，t2.name2 is null 就永遠(yuǎn)不可能為真，這已經(jīng)不是"用更快的方式得到同樣結(jié)果"，是徹底改變了查詢語義，所以優(yōu)化器不會碰這種條件。

判斷標(biāo)準(zhǔn)到這兒就很清楚了：能不能消除，看這個 WHERE 條件對外連接產(chǎn)生的 NULL 值判定成什么——一定是假或未知（比如普通等值比較），消除是安全的；有可能判定成真（比如 IS NULL），消除就會改變結(jié)果，優(yōu)化器不會做。

第三步：條件換到左表，跟外連接消除沒關(guān)系

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';

這次執(zhí)行計劃是 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），一行左表數(shù)據(jù)關(guān)聯(lián)出兩行結(jié)果，這跟外連接消除完全不搭邊。

這一步的過濾本質(zhì)上是"先決定左表要哪些行，再拿這些行去外連接"，跟右表的 Nullable 特性沒有任何關(guān)系。很多人容易把"WHERE 里出現(xiàn)的任何條件"都當(dāng)成外連接消除的誘因，這一步就是用來打破這個誤解的對照組。

第四步：把左表條件挪進(jìn) ON，結(jié)果比想象中多了一行

到這一步本來想驗證的是：左表條件放進(jìn) ON，會不會跟"右表條件放 WHERE"一樣有什么隱藏效應(yīng)。

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';

原本設(shè)想的結(jié)果是 4 行——t1 全量保留，只是 name1 <> 'b' 的行 t2 字段全是 NULL。跑出來一看，是 5 行，多了一行。仔細(xì)看輸出：id1=2 這一行出現(xiàn)了兩次，一次對應(yīng) id2=102/bb，一次對應(yīng) id2=103/cc；id1=1、3、4 各出現(xiàn)一次，t2 相關(guān)字段全是 NULL。

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

這跟前面認(rèn)為的"4 行"錯在哪兒：想當(dāng)然地把"左表 4 行"和"結(jié)果 4 行"劃了等號，卻忘了右表本來就有一對多的數(shù)據(jù)。這個坑其實挺常見，很多人寫業(yè)務(wù) SQL 的時候，只要 JOIN 的右表存在一對多關(guān)系，加不加條件、條件放哪，行數(shù)都可能跟"左表有幾行"對不上，得先確認(rèn)清楚右表對應(yīng)關(guān)系，再看結(jié)果對不對得上。

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

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';

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

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

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

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

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';

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

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';

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

收個尾：怎么在自己的 SQL 里審計這個問題

跑一遍下來，判斷標(biāo)準(zhǔn)可以歸成幾條：

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

這幾條規(guī)則說到底都是一件事：外連接消不消除，從來不取決于你寫沒寫 LEFT，而取決于這個查詢的整體邏輯，跟內(nèi)連接放在一起算，結(jié)果是不是完全一樣。只要有一丁點不一樣（哪怕只是多保留一個 NULL 行的可能性），優(yōu)化器就不會去消除；只要完全一樣，它就一定會去消除，圖的就是內(nèi)連接更便宜這點執(zhí)行開銷。寫 SQL 的時候腦子里想的是"業(yè)務(wù)要不要保底"，數(shù)據(jù)庫執(zhí)行的時候看的是"邏輯上能不能劃等號"，這中間的落差，就是這一類坑的根源。

到此這篇關(guān)于LEFT JOIN 到底什么時候會被優(yōu)化器偷偷改寫成 INNER JOIN的文章就介紹到這了,更多相關(guān)left join優(yōu)化替換inner join內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論