在写 SQL JOIN 时ON和WHERE经常一起出现看起来有点像可以互换。但它们的作用并不相同尤其在使用外连接LEFT / RIGHT / FULL JOIN时放错地方甚至会让结果完全变样。本文从以下几个方面解释JOIN、ON、WHERE各自负责什么它们在功能上的差异特别是LEFT / RIGHT JOIN对性能的影响可读性、可维护性及常见坑示例使用标准 SQL大部分数据库MySQL、PostgreSQL、SQL Server、Oracle 等都适用。1. 基本概念1.1 JOIN 是做什么的JOIN用来根据某种关联条件将两个或多个表的行组合在一起。常见的 JOIN 类型INNER JOIN只保留两边都匹配上的行LEFT JOIN或LEFT OUTER JOIN保留左表的所有行右表只保留匹配到的RIGHT JOIN与 LEFT 相反保留右表所有行FULL OUTER JOIN保留两边所有行某些数据库不支持1.2ON是做什么的ON用来定义连接条件join condition告诉数据库两个表的行如何匹配在 JOIN 操作时被使用示例SELECT * FROM orders o JOIN customers c ON o.customer_id c.id;ON告诉数据库“当orders.customer_id等于customers.id时这两行属于同一组。”1.3WHERE是做什么的WHERE用来在所有 JOIN 形成的结果集之后对行进行过滤。示例SELECT * FROM orders o JOIN customers c ON o.customer_id c.id WHERE c.country US;ON定义两表如何连接WHERE在连接后的结果中决定哪些行要保留可以简单理解ON “表之间的关系”WHERE “最终结果要满足的过滤条件”2. 功能上的差异ONvsWHERE2.1 在 INNER JOIN 中通常结果相同对于INNER JOIN把某些条件写在ON或WHERE中很多时候得到的结果是一样的。示例 1过滤条件写在WHERESELECT * FROM orders o INNER JOIN customers c ON o.customer_id c.id WHERE c.status active;示例 2过滤条件写在ONSELECT * FROM orders o INNER JOIN customers c ON o.customer_id c.id AND c.status active;对于INNER JOIN这两种写法在逻辑上通常是等价的内连接只保留满足ON条件的行再用WHERE做进一步过滤把过滤条件合并进ON对内连接来说效果一样仅限简单场景注意如果涉及多个表、复杂的OR条件等ON和WHERE的组合可能影响逻辑。这里说的是简单场景下的常见等价性。2.2 在 LEFT JOIN 中差别非常关键一旦使用外连接LEFT / RIGHT / FULL OUTER JOIN把条件放在ON还是WHERE就会影响结果集结构。假设customers左表所有客户orders右表客户的订单status可能是pending、complete、canceled等场景返回所有客户以及他们的“已完成订单”如果有A) 条件写在ON中保留所有左表行SELECT c.id, c.name, o.id AS order_id, o.status FROM customers c LEFT JOIN orders o ON c.id o.customer_id AND o.status complete;行为每个客户至少出现一次有已完成订单的客户显示这些订单没有已完成订单的客户照样显示但order_id、status为NULLB) 条件写在WHERE中连接后再过滤SELECT c.id, c.name, o.id AS order_id, o.status FROM customers c LEFT JOIN orders o ON c.id o.customer_id WHERE o.status complete;行为先做左连接所有客户都会出现没订单的客户订单列为NULL然后WHERE o.status complete会去掉所有订单状态不是 complete 的行也会去掉完全没有订单的客户因为他们的o.status为NULL不等于complete结果这个查询实质上变成了“只保留有已完成订单的客户”的内连接风格。核心规则对于可选数据不一定存在如果希望“主表行一定要保留”就要把该可选表的限制条件放在ON中。对于必须存在的数据用WHERE来过滤把不符合条件的整行去掉。2.3 RIGHT JOIN 和 FULL OUTER JOIN同样的逻辑可以推广对RIGHT JOIN如果在WHERE中对右表列做过滤会把右表为NULL的行去掉从而可能变成“类似内连接”的效果。对FULL OUTER JOINWHERE中的过滤会把任何一边为NULL的行去掉破坏“全保留”的语义。对FULL OUTER JOIN的典型写法需注意不要随便直接在WHERE中过滤右表或左表为 NULL 的列SELECT ... FROM A FULL OUTER JOIN B ON A.id B.id WHERE (A.status active OR A.status IS NULL) AND (B.status active OR B.status IS NULL);更常见的做法是将只影响匹配关系的条件写在ON中真正要“删行”的过滤写在WHERE中避免破坏外连接的语义。3. 性能方面的考虑3.1 逻辑顺序 vs 实际执行顺序SQL 是声明式语言你写的是“要什么结果”而不是“怎么做”。数据库优化器可以在不改变语义的前提下重写你的 SQL。关键点对于INNER JOIN优化器通常会把一些WHERE条件“推入”到 JOIN 里或者把某些ON条件当作过滤来处理对于OUTER JOIN优化器必须小心不能随意把ON/WHERE条件互换否则会改变结果3.2 在 INNER JOIN 中的性能对于内连接如果在ON和WHERE中放的条件在语义上是等价的那么在多数数据库中它们的执行计划和性能通常差不多因为优化器会重写这些谓词真正影响性能的是连接键是否有索引过滤条件的选择性过滤掉多少数据是否有合适的索引统计信息是否准确例如-- 写法1 SELECT * FROM orders o JOIN customers c ON o.customer_id c.id WHERE c.status active; -- 写法2 SELECT * FROM orders o JOIN customers c ON o.customer_id c.id AND c.status active;在很多数据库中这两条语句会生成同样的执行计划。3.3 在 LEFT / RIGHT / FULL JOIN 中的性能在外连接中首先要保证语义正确然后再谈优化。一般经验想保留外连接的行为主表行必须保留又想减少右表的匹配行数时应将右表的过滤条件放在ON中这样既保留主表所有行又能限制匹配的右表行数如果你本来就不需要未匹配的行逻辑上就是内连接那最好直接用INNER JOIN而不是LEFT JOIN WHERE把行删掉内连接通常更容易被优化器优化3.4 索引与谓词位置不管条件写在ON还是WHERE里只要语义等价影响性能的主要因素都是连接字段、过滤字段是否有合适的索引筛选条件是否具有高选择性是否对列使用了函数如WHERE LOWER(name) bob通常会让索引失效很多时候与其纠结ON和WHERE的性能差异不如更关注正确选择 JOIN 类型正确建立索引写出优化器更容易理解和重写的 SQL4. 其他角度可读性与常见模式4.1 可读性和可维护性为了让 SQL 更容易读懂和维护通常建议ON里写表之间的关系A.id B.a_idA.user_id B.user_idWHERE里写整体结果的过滤条件A.is_deleted 0A.created_at 2024-01-01A.country US在使用外连接时增加一个重要规则限制“可选表右表能否构成匹配”的条件放在ON中AND B.status active限制“最终结果整行是否保留”的条件放在WHERE中WHERE A.country US这样写能清晰表达你的意图ON “关联关系 可选表的匹配约束”WHERE “最终结果必须满足的条件”也更不容易不小心把外连接“写成了”内连接。4.2 常见模式示例模式 1所有主记录 可选的已过滤从记录需求“列出所有商品以及它们正在生效的折扣如有。没有折扣的商品也要显示。”正确写法SELECT p.id, p.name, d.amount FROM products p LEFT JOIN discounts d ON d.product_id p.id AND d.active 1;限制discounts表中active 1的条件写在ON中这样即使没有任何 active 折扣商品也会被显示折扣为 NULL错误写法在本需求下SELECT p.id, p.name, d.amount FROM products p LEFT JOIN discounts d ON d.product_id p.id WHERE d.active 1; -- 会去掉所有没有折扣的商品这相当于“必须有一个 active 折扣才能显示商品”逻辑变了。模式 2只要那些拥有某种关联数据的主记录需求“列出所有至少有一张已完成订单的客户。”可以这样写SELECT DISTINCT c.id, c.name FROM customers c JOIN orders o ON o.customer_id c.id WHERE o.status complete;或者这样写对 INNER JOIN 逻辑等价SELECT DISTINCT c.id, c.name FROM customers c JOIN orders o ON o.customer_id c.id AND o.status complete;因为本来就只要“有完成订单的客户”所以内连接是合理的放在ON还是WHERE差别不大。5.ONvsWHERE对比小结角度ON 子句WHERE 子句主要作用定义表之间如何连接匹配关系在连接完成后过滤最终结果行逻辑评估阶段JOIN 过程中JOIN 之后在 INNER JOIN 中对过滤条件来说通常可以和 WHERE 互换结果相同对过滤条件来说通常可以和 ON 互换结果相同在 OUTER JOIN 中能限制匹配记录但保留外表行不会轻易“变成内连接”对右表或外表的过滤可能把 NULL 行删掉从而变成内连接行为典型内容连接键关系、匹配逻辑、可选表的限制条件整体结果的过滤条件、全局逻辑限制性能对内连接与 WHERE 大多等价外连接需先保证语义正确对内连接与 ON 大多等价外连接滥用会破坏语义可读性建议表达“表之间怎么连、连哪些行”表达“最终结果要留下哪些行”6. 实战建议先考虑正确性再考虑性能。弄清楚自己要的是内连接还是外连接如果不在乎未匹配行 → 用INNER JOIN如果必须保留某一边所有记录 → 用LEFT / RIGHT / FULL JOIN用ON表达表之间的关系以及可选表的匹配限制。尤其在外连接中右表的条件如果写在WHERE很容易把未匹配的行删掉用WHERE表达最终结果的过滤条件。比如业务约束国家、时间、状态等全局条件。对 INNER JOIN 而言ON和WHERE常常结果一样但请保持一致风格以提升可读性。遇到LEFT JOIN却在WHERE中过滤右表字段时要特别警惕。如果WHERE条件要求右表字段非空或等于某值往往在逻辑上已经不再是“左连接”了这是非常常见的 bug 来源