V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
JinTianYi456
V2EX  ›  MySQL

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

  •  
  •   JinTianYi456 · 2022-11-03 04:00:23 +08:00 · 3837 次点击
    这是一个创建于 763 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

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

    第 1 条附言  ·  2022-11-03 19:44:56 +08:00

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

    19 条回复    2022-11-03 17:25:53 +08:00
    akira
        1
    akira  
       2022-11-03 04:36:24 +08:00
    先 用 on 拉出 每一对匹配的数据
    在用 where 过滤出 需要的数据
    Aplusplus
        2
    Aplusplus  
       2022-11-03 05:04:36 +08:00
    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
        3
    mizuhashi  
       2022-11-03 06:01:48 +08:00 via iPhone
    同樓上,沒有區別
    opengps
        4
    opengps  
       2022-11-03 07:01:54 +08:00 via Android
    我总觉得 on 似乎是先用,where 是后用,虽然不知道底层实际执行计划,但我宁愿优先用 on
    L0L
        5
    L0L  
       2022-11-03 07:20:05 +08:00
    实际上一棍子打死还是不行的,总得看是什么类型的数据库,然后看其优化的逻辑;我干了 6 年一直用 Oracle ,实际用下来,很多场景中,其实 CBO 优化器都已经帮忙计算好了执行计划,两者基本没有差距;但是在复杂的语句(动辄百行以上),CBO 分析不过来的时候,on 能更好的帮助确定过滤集。
    optional
        6
    optional  
       2022-11-03 07:34:12 +08:00 via iPhone
    他俩的差别没有索引选择差别大
    xuanbg
        7
    xuanbg  
       2022-11-03 07:53:01 +08:00
    on 只是 join 的关系条件,当 join 是内连接的时候,由于内连接的特性,造成了和 where 一致的效果而已。
    Aplusplus
        8
    Aplusplus  
       2022-11-03 09:19:37 +08:00
    @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
        9
    ma836323493  
       2022-11-03 09:38:15 +08:00
    性能差别不一样,但是对程序原来说,能把过滤条件按 块 分,join 2 张表 ,where 条件 一大堆 过会儿别名你就看花眼了
    yufpga
        10
    yufpga  
       2022-11-03 09:40:53 +08:00
    op 引用的链接说的很清楚, 在于过滤的时机在生成临时表前还是生成临时表后。那么在某些情况下, 使用 on 能够极大的减小临时表的大小. 临时表太大,如果数据库服务器磁盘,内存等资源不充裕时,就会导致 sql 执行失败(我曾经在 mysql5.6 上出现过这个问题, 后来改用 on 就可以了).
    liprais
        11
    liprais  
       2022-11-03 10:00:14 +08:00 via iPhone
    说没有区别的你们肯定没写过 left join
    chunworkhard
        12
    chunworkhard  
       2022-11-03 10:09:40 +08:00   ❤️ 1
    inner join 是一样的, 但是 left join 会不一样, 即使 ON 条件不符合的 左表数据也会查询出来, 但是 where 会过滤掉
    ymlluo
        13
    ymlluo  
       2022-11-03 10:11:51 +08:00
    极客时间 林晓斌 《 MySQL 实战 45 讲》第 44 章 “如果需要 left join 的语义,就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断,必须都写在 on 里面. 那如果是 join 语句 join 将判断条件是否全部放在 on 部分就没有区别”
    wushigejiajia01
        14
    wushigejiajia01  
       2022-11-03 10:29:50 +08:00
    A join B, on 与 where 效果是一致的
    A left join B, on 与 where 就有差别了,on 是过滤临时表的,B 表中的数据不满足 on 条件时会只返回 A 表数据(B 表数据为 null),where 是对整个结果进行过滤
    wxf666
        15
    wxf666  
       2022-11-03 10:34:39 +08:00
    反正 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
        16
    NeroKamin  
       2022-11-03 14:13:51 +08:00
    @liprais 那你肯定连 OP 的提问是啥都没有看完就来回复了
    lookStupiToForce
        17
    lookStupiToForce  
       2022-11-03 14:16:44 +08:00
    最简单的测试方法就是看两种情况下的执行计划就行了,inner join 条件下 on 和 where 在优化器不出岔子的情况下理论上没任何区别,但如果涉及到的表统计信息不对 /不及时,导致优化器出的执行计划两者不一样,或者优化器很笨不知道你写在 on 里的条件能让它选择更好的 join 方式,那肯定还是有区别的

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

    outer join 就不谈了,从 on 挪到 where 里结果都可能会不同
    simonlu9
        18
    simonlu9  
       2022-11-03 16:43:43 +08:00
    很简单,你试下用 left join 试试,where 会保留左边,但 on 左边也没有
    leeraya
        19
    leeraya  
       2022-11-03 17:25:53 +08:00
    on 是连接条件
    where 是过滤条件
    二者有很大的区别
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5717 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 03:29 · PVG 11:29 · LAX 19:29 · JFK 22:29
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.