SQL 中 on 和 where 条件放置的差异 _SQL

场景描述：由于生产环境的表比较复杂，字段很多。这里我们做下简化，只为说明今天要聊的问题。
有两张表 tab1，tab2：

tab1 数据如下：

文章插图

tab2 数据如下：

文章插图

然后给你看下，我用来统计 name='小白' 的两条SQL：

//①SELECT count(1) FROM tab1 LEFT JOIN tab2 ON tab1.NAME = tab2.NAMEAND tab2.NAME = '小白';// ②SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.NAME = tab2.NAME WHERE tab2.NAME = '小白';

第①个 sql 执行结果如下：

文章插图

结果并不是返回我预期中的 2，我想了俩小时也没搞明白为啥，分明表里面是有两条"小白"的数据，我已经限制了过滤条件怎么不是 2 呢？
第②个 sql 执行结果如下：

文章插图

显然按照我之前的理解是错的：我以为将过滤条件放到 where 和 on 后面是一样的效果，只是写法的差异而已。
结果分析我们直接将①、②这两条 SQL 查出来的数据搞出来看看就会明白了。
注意：数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

SQL①结果：

文章插图

通过结果，可以看出这个 SQL 的执行过程：它会以左表作为主表，然后不管 on 后面的条件是否为真也会返回主表的所有记录。
如果满足 on 后面的所有条件，那么中间表中左表和右表的字段值都会有，同时中间表的行数可能会大于左表总数，你可以思考下为什么，不明白的可以评论区留言。如果 on 条件不满足，左表的记录数也不会少的，这时候右表字段就补 NULL 。

SQL②结果：

文章插图

这条 SQL，首先会通过 on 后面的条件关联出一张中间表：

文章插图

然后在对中间表执行 where 条件，过滤出 NAME = '小白'的数据：

文章插图

踩坑总结其实以上结果的关键原因就是 left join、 right join、full join 的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则是 left 和 right 结果的并集。
而 inner jion 没这个特殊性，满足 on 后面的条件，表的数据才能查出，可以起到过滤作用。所以，条件放在 on 中和 where 中，返回的结果集是相同的。
在使用 left jion 时，on 和 where 条件的区别如下：

on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
where 条件是在临时表生成好后，再对临时表进行过滤的条件，条件不为真的就全部过滤掉。

在多表联接查询时，on 比 where 更早起作用。系统首先根据各个表之间的连接条件，把多个表合成一个临时表后，再由 where 进行过滤，然后再计算。
由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

【SQL 中 on 和 where 条件放置的差异】