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

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

  • tab1 数据如下:

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

文章插图
 
  • tab2 数据如下:

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

文章插图
 
然后给你看下,我用来统计 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 执行结果如下:
SQL 中 on 和 where 条件放置的差异

文章插图
 
结果并不是返回我预期中的 2,我想了俩小时也没搞明白为啥,分明表里面是有两条"小白"的数据,我已经限制了过滤条件怎么不是 2 呢?
第②个 sql 执行结果如下:
SQL 中 on 和 where 条件放置的差异

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

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

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

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

文章插图
 
这条 SQL,首先会通过 on 后面的条件关联出一张中间表:
SQL 中 on 和 where 条件放置的差异

文章插图
 
然后在对中间表执行 where 条件,过滤出 NAME = '小白'的数据:
SQL 中 on 和 where 条件放置的差异

文章插图
 
踩坑总结其实以上结果的关键原因就是 left join、 right join、full join 的特殊性,不管 on 上的条件是否为真都会返回 left 或 right 表中的记录,full 则是 left 和 right 结果的并集 。
而 inner jion 没这个特殊性,满足 on 后面的条件,表的数据才能查出,可以起到过滤作用 。所以,条件放在 on 中和 where 中,返回的结果集是相同的 。
在使用 left jion 时,on 和 where 条件的区别如下:
  1. on 条件是在生成临时表时使用的条件,它不管 on 中的条件是否为真,都会返回左边表中的记录 。
  2. where 条件是在临时表生成好后,再对临时表进行过滤的条件,条件不为真的就全部过滤掉 。
在多表联接查询时,on 比 where 更早起作用 。系统首先根据各个表之间的连接条件,把多个表合成一个临时表后,再由 where 进行过滤,然后再计算 。
由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里 。

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


    推荐阅读