1. 這一題為什么會(huì)成為“面試必答”一段真實(shí)報(bào)表開發(fā)經(jīng)歷先說一個(gè)我自己踩過的坑。有一年做經(jīng)營分析報(bào)表需求很樸素統(tǒng)計(jì)每個(gè)用戶的已完成訂單金額并且要列出那些沒有任何已完成訂單的用戶。我當(dāng)時(shí)的直覺是用 LEFT JOIN 把用戶表和訂單表關(guān)聯(lián)起來再在 WHERE 里加一個(gè)o.status completed條件不就完事了嗎結(jié)果一跑沒訂單的客戶全部消失了。檢查半天最后才發(fā)現(xiàn)問題出在我把過濾條件放在了 WHERE而不是 ON。這件事之后我認(rèn)真梳理了一遍 SQL 里 ON 與 WHERE 過濾的差異發(fā)現(xiàn)它不只是個(gè)面試題而是很多人寫關(guān)聯(lián)查詢時(shí)最容易踩的語義陷阱。這道題之所以經(jīng)典是因?yàn)閺膬?nèi)連接的角度看這兩種寫法結(jié)果往往一樣一旦換到外連接行為立刻分道揚(yáng)鑣。很多人記不住結(jié)論其實(shí)是因?yàn)闆]有真正理解 JOIN 的底層語義。這篇文章我會(huì)從數(shù)據(jù)模型、SQL 執(zhí)行順序、優(yōu)化器行為三個(gè)層面拆解再結(jié)合幾個(gè)真實(shí)項(xiàng)目里的常見坑給出一個(gè)可以直接照著用的判斷準(zhǔn)則。適合誰看如果你已經(jīng)能寫出基本的 JOIN 查詢但遇到“為什么 LEFT JOIN 的數(shù)據(jù)少了”“為什么過濾條件寫在 ON 里和 WHERE 里結(jié)果不一樣”這類問題仍然要翻文檔那這篇文章就是給你準(zhǔn)備的。2. 先建一套能復(fù)現(xiàn)差異的數(shù)據(jù)模型很多人講這個(gè)題目喜歡空對(duì)空地講概念我覺得不如先擺一套可以跑的數(shù)據(jù)。下面我用兩張非常簡單的業(yè)務(wù)表用戶表 users 和訂單表 orders配套幾條測試數(shù)據(jù)后面所有實(shí)驗(yàn)都在這套數(shù)據(jù)上做。2.1 表結(jié)構(gòu)和測試數(shù)據(jù)以 PostgreSQL 語法為例你也可以直接在 MySQL、SQL Server 上執(zhí)行基礎(chǔ)思路完全一致。CREATE TABLE users ( id INT PRIMARY KEY, name TEXT, status TEXT ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT REFERENCES users(id), amount NUMERIC(10,2), status TEXT ); INSERT INTO users VALUES (1, 張三, active), (2, 李四, active), (3, 王五, inactive); INSERT INTO orders VALUES (101, 1, 500.00, completed), (102, 1, 200.00, pending), (103, 3, 100.00, completed), (104, 3, 50.00, cancelled);這組數(shù)據(jù)刻意設(shè)計(jì)了幾個(gè)特點(diǎn)張三有兩條訂單一條 completed一條 pending用來觀察過濾條件對(duì)多行匹配的影響。李四一條訂單都沒有是觀察 LEFT JOIN 空值擴(kuò)展的關(guān)鍵角色。王五的用戶狀態(tài)是 inactive但確實(shí)有訂單用來測試“過濾用戶表自身字段”時(shí) ON 和 WHERE 的差異。王五有一條 cancelled 訂單方便觀察多狀態(tài)過濾時(shí)的行為。2.2 從 SQL 執(zhí)行順序推導(dǎo)兩個(gè)過濾位置的意義要理解 ON 和 WHERE 的區(qū)別先要把 SQL 語句的執(zhí)行順序拉出來。邏輯上大多數(shù)數(shù)據(jù)庫的查詢處理順序大致是FROM→ON→JOIN→WHERE→GROUP BY→HAVING→SELECT→ORDER BY這里最關(guān)鍵的是 ON 和 JOIN 發(fā)生在 WHERE 之前。光看這個(gè)順序還不夠因?yàn)?JOIN 類型會(huì)改變“ON 決定什么”。內(nèi)連接INNER JOIN中ON 既決定連接鍵也決定哪些行能進(jìn)入結(jié)果而 ON 里寫的額外條件本質(zhì)上只是連接匹配條件的一部分。外連接LEFT JOIN、RIGHT JOIN、FULL JOIN則引入了“保留表”的概念LEFT JOIN 會(huì)保留左表的全部行右表沒有匹配上的地方補(bǔ) NULL。這時(shí)候 ON 里寫的條件決定了“右表哪些行能和左表匹配”而 WHERE 里寫的條件決定了“JOIN 完成之后最終結(jié)果集里要過濾掉哪些行”。你可以把 JOIN 想象成一道流水線ON 是在“組裝工位”上決定兩個(gè)零件能不能拼在一起WHERE 是組裝完成后質(zhì)檢員根據(jù)最終成品的外觀決定要不要扔掉。組裝時(shí)沒拼上的零件會(huì)在 LEFT JOIN 里用 NULL 填充繼續(xù)送往下游但到了質(zhì)檢環(huán)節(jié)如果 WHERE 條件要求某個(gè)字段不能為 NULL這些“空殼成品”就會(huì)被直接淘汰。這套比喻雖然簡化但能把“為什么 LEFT JOIN 遇到 WHERE 右表過濾會(huì)丟行”這個(gè)現(xiàn)象講得很直觀。后面幾個(gè)實(shí)驗(yàn)會(huì)把這個(gè)過程逐步驗(yàn)證出來。3. 實(shí)驗(yàn)一INNER JOIN 下ON 過濾和 WHERE 過濾“殊途同歸”先處理最簡單、也最容易讓初學(xué)者困惑的情況內(nèi)連接。3.1 同一需求兩種寫法需求找出所有有已完成訂單的用戶及其訂單信息。寫法一把狀態(tài)過濾寫在 ON 后SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u JOIN orders o ON u.id o.user_id AND o.status completed;寫法二把狀態(tài)過濾寫在 WHERE 里SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u JOIN orders o ON u.id o.user_id WHERE o.status completed;兩條 SQL 的結(jié)果完全一致idnameorder_idamountstatus1張三101500.00completed3王五103100.00completed為什么一致因?yàn)閮?nèi)連接只有匹配成功的行會(huì)進(jìn)入結(jié)果集不存在“保留行補(bǔ) NULL”的環(huán)節(jié)。第一種寫法里ON 條件被拆成“連接鍵 狀態(tài)過濾”不滿足狀態(tài)的行不會(huì)參與連接第二種寫法里所有能匹配上 user_id 的訂單先進(jìn)入中間結(jié)果再由 WHERE 把狀態(tài)不等于 completed 的行篩掉。由于內(nèi)連接沒有保留行這兩種過程最終得到的結(jié)果集合是完全相同的。這也正是“寫在哪都一樣”這句謠言的來源。很多初學(xué)者只在內(nèi)連接場景下驗(yàn)證過就以為全場景通用結(jié)果一到 LEFT JOIN 就翻車。3.2 為什么數(shù)據(jù)庫允許這種等價(jià)從關(guān)系代數(shù)角度看內(nèi)連接的 ON 條件本身就是一個(gè)二元謂詞它作用于兩個(gè)表的笛卡爾積選出滿足條件的行。WHERE 過濾同樣是對(duì)中間結(jié)果集合施加謂詞。對(duì)內(nèi)連接而言O(shè)N 里多出來的條件完全可以合并進(jìn) WHERE反之亦然因?yàn)樗鼈兌荚谧鐾患孪拗谱罱K輸出行。從優(yōu)化器角度看絕大多數(shù)現(xiàn)代數(shù)據(jù)庫PostgreSQL、MySQL、SQL Server、Oracle都會(huì)做謂詞下推和連接順序優(yōu)化。也就是說哪怕你把過濾條件寫在 WHERE 里優(yōu)化器也極有可能把它“下推”到表掃描階段在真正執(zhí)行 JOIN 之前先把訂單表里 status completed 的行篩出來哪怕你寫在 ON 里優(yōu)化器也可能在語義不變的前提下重新調(diào)整連接順序。因此對(duì)于內(nèi)連接兩者的執(zhí)行計(jì)劃最終往往非常接近甚至一模一樣。不過要注意JOIN和INNER JOIN是同一個(gè)意思但有些人會(huì)寫FROM users u, orders o WHERE u.id o.user_id AND o.status completed這種舊式逗號(hào)連接在語義上也等價(jià)于內(nèi)連接只是可讀性和可維護(hù)性更差。不屬于本文重點(diǎn)但看到時(shí)要知道它和內(nèi)連接是一回事。4. 實(shí)驗(yàn)二LEFT JOIN 下ON 和 WHERE 的結(jié)果開始分道揚(yáng)鑣現(xiàn)在進(jìn)入真正的重點(diǎn)。LEFT JOIN 引入了“保留左表全部行”的語義ON 和 WHERE 的差異開始變得致命。4.1 過濾右表經(jīng)典陷阱LEFT JOIN 被改成了 INNER JOIN需求查詢所有用戶并列出他們的已完成訂單如果某個(gè)用戶沒有已完成訂單也仍然要出現(xiàn)在結(jié)果里。正確寫法是把狀態(tài)過濾放在 ON 中SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status completed ORDER BY u.id, o.id;結(jié)果是idnameorder_idamountstatus1張三101500.00completed2李四NULLNULLNULL3王五103100.00completed注意看李四這一行他沒有已完成訂單也沒有任何訂單所以右表字段全部是 NULL。這正是 LEFT JOIN 該有的效果——左表的人必須都在。如果把這個(gè)需求誤寫成 WHERE 過濾SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status completed ORDER BY u.id, o.id;結(jié)果是idnameorder_idamountstatus1張三101500.00completed3王五103100.00completed李四不見了。為什么會(huì)這樣LEFT JOIN 先把所有用戶和所有能匹配上 user_id 的訂單拼在一起李四拼出來的那行o.status 是 NULL。隨后 WHERE 要求o.status completed而 NULL 與任何值比較的結(jié)果都是“未知”最終該行被過濾掉。換句話說WHERE 條件把 LEFT JOIN 強(qiáng)行變成了 INNER JOIN 的效果而且是在你毫無察覺的情況下發(fā)生的。這一點(diǎn)我要特別強(qiáng)調(diào)很多數(shù)據(jù)分析師寫報(bào)表時(shí)經(jīng)常犯這個(gè)錯(cuò)誤。表面上 SQL 里寫著 LEFT JOIN邏輯上卻變成了 INNER JOIN導(dǎo)致“沒有數(shù)據(jù)的用戶”被默默丟掉報(bào)表上的數(shù)字怎么對(duì)都對(duì)不上。4.2 過濾左表ON 子句影響匹配規(guī)則WHERE 子句影響保留行上面測的是過濾右表字段下面換個(gè)角度過濾 USER 表自身的字段。需求查詢所有用戶及其訂單但希望“不活躍用戶”即使有訂單在結(jié)果里也顯示為無訂單同時(shí)不活躍用戶本身仍要保留在結(jié)果里。聽起來有點(diǎn)繞但實(shí)際場景是你不想讓某些用戶的訂單參與報(bào)表統(tǒng)計(jì)但又不能把這些人從名單里抹掉。寫成 ON 過濾SELECT u.id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND u.status active ORDER BY u.id, o.id;結(jié)果是idnameorder_idamount1張三101500.001張三102200.002李四NULLNULL3王五NULLNULL王五雖然存在但他的訂單沒有關(guān)聯(lián)上因?yàn)?ON 條件里的u.status active對(duì)王五這一行不成立。李四依然保留因?yàn)樗緛砭蜎]有訂單。如果改成 WHERE 過濾SELECT u.id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.status active ORDER BY u.id, o.id;結(jié)果是idnameorder_idamount1張三101500.001張三102200.002李四NULLNULL王五直接消失。這就是我前面說的“ON 決定匹配規(guī)則WHERE 決定最終結(jié)果集包含哪些行”的具體體現(xiàn)。同樣一個(gè)條件寫在 ON 里影響的是“王五的訂單能不能匹配上”寫在 WHERE 里影響的是“王五這行用戶數(shù)據(jù)還在不在”。這個(gè)細(xì)節(jié)很多人沒意識(shí)到因?yàn)樗蝗纭癓EFT JOIN 變 INNER JOIN”那么常見。但在做用戶維度的留存分析、生命周期分析時(shí)很容易踩中。4.3 多表鏈?zhǔn)?JOIN一個(gè) WHERE 條件引發(fā)連鎖反應(yīng)再進(jìn)一步三張表關(guān)聯(lián)時(shí)問題會(huì)變得更隱蔽。假設(shè)我們?cè)偌右粡堉Ц侗?paymentsCREATE TABLE payments ( id INT PRIMARY KEY, order_id INT REFERENCES orders(id), amount NUMERIC(10,2) ); INSERT INTO payments VALUES (1001, 101, 500.00), (1002, 102, 150.00);現(xiàn)在需求是列出所有用戶以及他們已完成訂單的支付記錄沒有已完成訂單的用戶也要保留。如果把狀態(tài)過濾放在第一個(gè) JOIN 的 ON 里SELECT u.id, u.name, o.id AS order_id, o.status, p.id AS payment_id FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status completed LEFT JOIN payments p ON p.order_id o.id ORDER BY u.id, o.id, p.id;結(jié)果是三行idnameorder_idstatuspayment_id1張三101completed10012李四NULLNULLNULL3王五103completedNULL張三只關(guān)聯(lián)了已完成訂單 101訂單 102 雖然是 pending 但被 ON 條件擋在連接之外王五的訂單 103 是 completed但沒有對(duì)應(yīng)支付所以 payment_id 是 NULL。如果把過濾條件誤放到 WHERESELECT u.id, u.name, o.id AS order_id, o.status, p.id AS payment_id FROM users u LEFT JOIN orders o ON u.id o.user_id LEFT JOIN payments p ON p.order_id o.id WHERE o.status completed ORDER BY u.id, o.id, p.id;結(jié)果是兩行idnameorder_idstatuspayment_id1張三101completed10013王五103completedNULL李四又沒了。而且注意這個(gè) WHERE 條件還順帶把張三的 pending 訂單 102 過濾掉了。雖然第一步 LEFT JOIN 把 102 關(guān)聯(lián)進(jìn)來了但 WHERE 在最后統(tǒng)一清理導(dǎo)致你既丟了李四也看不到張三的 pending 訂單。多表 JOIN 場景下WHERE 的過濾是“全局的”它作用于整條鏈路產(chǎn)生的所有中間行而 ON 的過濾只影響當(dāng)前 JOIN 這一步的匹配行為。理解這一點(diǎn)很多報(bào)表數(shù)據(jù)對(duì)不上的問題都能迎刃而解。5. 再從優(yōu)化器角度審視為什么“執(zhí)行順序”沒那么簡單講完語義很多人會(huì)問那性能呢把過濾條件寫在 ON 里是不是一定比寫在 WHERE 里快答案沒那么簡單。5.1 優(yōu)化器重寫很多數(shù)據(jù)庫會(huì)把 WHERE 右表過濾的 LEFT JOIN 重寫成 INNER JOINPostgreSQL、MySQL 等主流數(shù)據(jù)庫都有一個(gè)優(yōu)化手段如果 WHERE 條件對(duì)右表字段施加了“非空”限制優(yōu)化器會(huì)認(rèn)為 NULL 擴(kuò)展行不可能通過過濾條件于是把 LEFT JOIN 等價(jià)轉(zhuǎn)換為 INNER JOIN再去生成執(zhí)行計(jì)劃。舉個(gè)例子之前那句“錯(cuò)誤”的 LEFT JOIN WHERE 寫法SELECT ... FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status completed;在 PostgreSQL 中查看執(zhí)行計(jì)劃時(shí)你會(huì)發(fā)現(xiàn)它實(shí)際執(zhí)行的很可能已經(jīng)不是 Left Join而是 Inner Join。為什么因?yàn)閛.status completed這個(gè)條件排除了所有 NULL 行保留的只有真正能匹配上的訂單這與 INNER JOIN 的結(jié)果完全一致。優(yōu)化器天生就會(huì)做這種等價(jià)變換。這里有個(gè)諷刺的后果執(zhí)行計(jì)劃上那個(gè)“語法上寫錯(cuò)”的 LEFT JOIN 可能和內(nèi)連接一樣快甚至更快。但慢不是問題語義錯(cuò)才是問題。你本來想保留李四結(jié)果優(yōu)化器也認(rèn)為“你不想保留李四”于是幫你把李四刪了。這恰恰說明靠執(zhí)行計(jì)劃去判斷“對(duì)不對(duì)”是沒有意義的得先看業(yè)務(wù)語義要求什么。5.2 執(zhí)行計(jì)劃里的 Filter 位置變化把 ON 過濾和 WHERE 過濾的執(zhí)行計(jì)劃放在一起看能發(fā)現(xiàn)明顯的結(jié)構(gòu)差異。ON 過濾的 LEFT JOIN狀態(tài)條件會(huì)出現(xiàn)在連接條件中例如Nested Loop Left JoinSeq Scan on usersIndex Scan using orders_user_id_idx on ordersIndex Cond: user_id u.idFilter: status completedWHERE 過濾的 LEFT JOIN優(yōu)化器如果沒做等價(jià)改寫狀態(tài)條件會(huì)出現(xiàn)在連接之后的 Filter 上Nested Loop Left JoinSeq Scan on usersIndex Scan using orders_user_id_idx on ordersIndex Cond: user_id u.idFilter: status completedFilter 在 Join 節(jié)點(diǎn)外部意味著它要處理的是連接后產(chǎn)生的行包括 NULL 擴(kuò)展出來的那些。雖然大多數(shù)情況下優(yōu)化器會(huì)把這個(gè)條件進(jìn)一步下推或改寫但在某些復(fù)雜查詢、視圖嵌套、窗口函數(shù)疊加的場景里優(yōu)化器不一定能做出最優(yōu)選擇。這時(shí)多一個(gè)連接后 Filter 就意味著多一層行處理代價(jià)也會(huì)上去。5.3 何時(shí) ON 過濾會(huì)顯著影響性能不過真正讓性能產(chǎn)生巨大差異的是下面這類情況過濾條件非常強(qiáng)能把右表從 1000 萬行縮到 100 行并且這個(gè)條件是外連接的一部分。如果你把條件寫在 ON 里連接時(shí)右表只需要掃描極小一部分?jǐn)?shù)據(jù)如果寫在 WHERE 里理論上優(yōu)化器也可能做下推但它必須先證明這個(gè)下推不改變外連接語義而“右表字段非空”就是它的證明依據(jù)不一定總能成立尤其是條件里還帶 OR、函數(shù)或子查詢時(shí)。舉個(gè)具體例子SELECT u.id, u.name, o.id FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status IN (completed, pending);這個(gè) ON 條件里沒有IS NULL優(yōu)化器無法把 LEFT JOIN 簡化成 INNER JOIN但可以在連接階段用索引只讀這兩類狀態(tài)的訂單。如果把同樣的條件放到 WHERESELECT u.id, u.name, o.id FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status IN (completed, pending);結(jié)果里所有沒有訂單的用戶全被過濾了這與上一個(gè)查詢的語義完全不同。如果右表還有大量不滿足條件的行WHERE 版本就可能在連接后產(chǎn)生海量中間行再統(tǒng)一過濾性能可能差出幾個(gè)數(shù)量級(jí)。所以性能上的結(jié)論是對(duì)于內(nèi)連接ON 和 WHERE 通?？梢曰Q優(yōu)化器會(huì)幫你兜底對(duì)于外連接寫在哪直接決定了查詢語義和潛在成本唯一穩(wěn)妥的做法是先想清楚你到底需要“保留左表全部行”還是“只要右表中滿足條件的行”。6. 日常項(xiàng)目中踩過的坑和我的編碼約定理論講完了最后落到工程實(shí)踐。下面幾個(gè)坑是我在實(shí)際代碼評(píng)審和數(shù)據(jù)核對(duì)中反復(fù)遇到的每一個(gè)都對(duì)應(yīng)著真實(shí)故障。6.1 坑一報(bào)表匯總時(shí) LEFT JOIN 悄悄變 INNER JOIN這是最常見的。某人寫“每個(gè)用戶的已完成訂單總金額”為了把沒有訂單的用戶也顯示出來用了 LEFT JOIN但過濾條件寫在了 WHERE 里SELECT u.id, u.name, SUM(o.amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status completed GROUP BY u.id, u.name;結(jié)果表現(xiàn)為沒有已完成訂單的用戶根本不出現(xiàn)而不是以 0 金額出現(xiàn)。報(bào)表上用戶數(shù)對(duì)不上復(fù)盤時(shí)才發(fā)現(xiàn)問題。正確處理是SELECT u.id, u.name, SUM(CASE WHEN o.status completed THEN o.amount ELSE 0 END) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name;或者把狀態(tài)條件放進(jìn) ON再配合 GROUP BY 統(tǒng)計(jì)SELECT u.id, u.name, COALESCE(SUM(o.amount), 0) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status completed GROUP BY u.id, u.name;這種方式更干凈因?yàn)?ON 已經(jīng)限定“只關(guān)聯(lián)已完成訂單”之后 SUM 拿到的就是已完成金額匯總沒有訂單的用戶則是 NULL再用 COALESCE 兜底成 0。6.2 坑二把過濾條件都放 ON 里導(dǎo)致錯(cuò)誤保留歷史無效數(shù)據(jù)有人學(xué)會(huì)“ON 里過濾”這一招后容易用力過猛把所有業(yè)務(wù)過濾都塞進(jìn) ON。比如查詢“本月活躍用戶及其訂單”明明想過濾掉超過 30 天沒登錄的用戶卻把用戶表的過濾條件寫進(jìn) ONSELECT u.id, u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id o.user_id AND u.last_login_at NOW() - INTERVAL 30 days;這樣寫會(huì)產(chǎn)生什么超過 30 天未登錄的用戶仍在結(jié)果里但他們的訂單全部變成 NULL。如果業(yè)務(wù)方關(guān)心的是“只看活躍用戶”這種寫法就是錯(cuò)的它把需要過濾的用戶“留住”了只讓他們的訂單消失這通常不是想要的結(jié)果。所以判斷原則不是“一律寫 ON”而是分清條件作用對(duì)象如果是右表維度過濾且你要保留左表全部行寫 ON如果是左表維度過濾且你要從維度表中剔除某些人應(yīng)該寫在 WHERE 里如果想保留左表全部行又希望左表?xiàng)l件參與匹配那才考慮 ON。6.3 坑三