mysql join on 条件_「看这篇就够了」Mysql join条件是要写在on里还是在where里？

对于join系列语句，大部分开发人员都经常用到。但是对于里面的运行原理，我相信很少人真正认识，下面我们从几个方面介绍下。

为了能够覆盖更多的点，这里复制一位大佬的表和图。我们先建两个表和添加一批数据，注意只有a表的f1有索引，a表和B表的数据不完全一致：

结果集区别

上图可以看出，结果集是不一样的，条件写在ON里，数据有6条，比条件放在where里面多出2条。

算法区别

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2)语句执行顺序是：1、先扫描a表的数据，放到join_buffer中，join_buffer的数据结构是数组。2、顺序扫描b表，每一条数据跟join_buffer的a的数据进行on条件判断，匹配则放入结果集中。最后a中未匹配的补上null，合并到结果集中返回。

以上这种查找方法就是Mysql的Block Nexted Loop Join(简称BNL)算法。b表是没有索引情况下，顺序扫描全表根据驱动表join buffer匹配，进而计算结果集！

select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2)语句执行是：因为where条件中b.f2为null值，且mysql优化器会认为这sql具有优化空间。所以就将这个left join的语句优化为join：select * from a left join b where a.f1=b.f1 and a.f2=b.f2。也就是即使我们用了left join 在这里也不能保证执行顺序，因为优化器会进行优化。现在因为a表f1有索引，优化器优化后变成了b表是驱动表，a表是被驱动表，走的是IndexNested-Loop Join(简称NLJ)算法。

结论

1、如果要结果集包含左表全部数据，则条件写在ON里

2、如果要保证sql性能，可以写在where里面

关注我，下一篇继续介绍这个join话题。

「看这篇就够了」Mysql大表中查询全表扫描是否会占用完内存？

「看这篇就够了」Mysql事务提交是怎么在保证性能情况下持久化？

「看这篇就够了」Mysql幻读的原理介绍

「看这篇就够了」Mysql的limit有哪些优点，快来了解下吧