SQL 中 on 条件与 where 条件的区别

[JinTianYi456]

https://www.runoob.com/w3cnote/sql-different-on-and-where.html#note-nav

而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

那性能上两者比较呢？或者占内存上两者比较呢？

是我没问清楚，问，既然 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的，那两张写法在inner jion下，有其它方面差异吗？性能上？占内存？

[akira]

先 用 on 拉出 每一对匹配的数据
在用 where 过滤出 需要的数据

[Aplusplus]

No difference. Optimizers can move both to inner join conjuncts or filter project after inner join. Regardless, the underlying expression evaluation is the same.

[mizuhashi]

同樓上，沒有區別

[opengps]

我总觉得 on 似乎是先用，where 是后用，虽然不知道底层实际执行计划，但我宁愿优先用 on

[L0L]

实际上一棍子打死还是不行的，总得看是什么类型的数据库，然后看其优化的逻辑；我干了 6 年一直用 Oracle ，实际用下来，很多场景中，其实 CBO 优化器都已经帮忙计算好了执行计划，两者基本没有差距；但是在复杂的语句（动辄百行以上），CBO 分析不过来的时候，on 能更好的帮助确定过滤集。

[optional]

他俩的差别没有索引选择差别大

[xuanbg]

on 只是 join 的关系条件，当 join 是内连接的时候，由于内连接的特性，造成了和 where 一致的效果而已。

[Aplusplus]

@L0L Do you have an example? I don't think CBO is involve because it's the same cost between inner join conjuncts and join output filter.

[ma836323493]

性能差别不一样，但是对程序原来说，能把过滤条件按 块 分，join 2 张表 ，where 条件 一大堆 过会儿别名你就看花眼了

[yufpga]

op 引用的链接说的很清楚, 在于过滤的时机在生成临时表前还是生成临时表后。那么在某些情况下， 使用 on 能够极大的减小临时表的大小. 临时表太大，如果数据库服务器磁盘，内存等资源不充裕时，就会导致 sql 执行失败(我曾经在 mysql5.6 上出现过这个问题, 后来改用 on 就可以了).

[liprais]

说没有区别的你们肯定没写过 left join

[chunworkhard]

inner join 是一样的， 但是 left join 会不一样， 即使 ON 条件不符合的 左表数据也会查询出来， 但是 where 会过滤掉

[ymlluo]

极客时间 林晓斌 《 MySQL 实战 45 讲》第 44 章 “如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面. 那如果是 join 语句 join 将判断条件是否全部放在 on 部分就没有区别”

[wushigejiajia01]

A join B, on 与 where 效果是一致的
A left join B, on 与 where 就有差别了，on 是过滤临时表的，B 表中的数据不满足 on 条件时会只返回 A 表数据(B 表数据为 null)，where 是对整个结果进行过滤

[wxf666]

反正 SQLite 的 [文档]( https://sqlite.org/lang_select.html#where_clause_filtering_ ) 说过这个问题：

> For a JOIN or INNER JOIN or CROSS JOIN, there is **no difference** between a constraint expression in the WHERE clause and one in the ON clause. However, for a LEFT JOIN or LEFT OUTER JOIN, the difference is very important. ……

我觉得，1 MB 的 SQLite 都能做到无区别，其他数据库肯定至少也可以做到无区别

[NeroKamin]

@liprais 那你肯定连 OP 的提问是啥都没有看完就来回复了

[lookStupiToForce]

最简单的测试方法就是看两种情况下的执行计划就行了，inner join 条件下 on 和 where 在优化器不出岔子的情况下理论上没任何区别，但如果涉及到的表统计信息不对 /不及时，导致优化器出的执行计划两者不一样，或者优化器很笨不知道你写在 on 里的条件能让它选择更好的 join 方式，那肯定还是有区别的

就是前面有一个 v 友说的“在复杂的语句（动辄百行以上），CBO 分析不过来的时候，on 能更好的帮助确定过滤集”。这也是为啥 oracle 支持的 hint 对于数据库工程师来说是超强的利器，你分析不过来工程师自有更好的执行法子限制你不发癫。

outer join 就不谈了，从 on 挪到 where 里结果都可能会不同

[simonlu9]

很简单，你试下用 left join 试试，where 会保留左边，但 on 左边也没有

[leeraya]

on 是连接条件
where 是过滤条件
二者有很大的区别