sql连接查询语句中on、where筛选的区别是什么

这篇文章将为大家详细讲解有关sql连接查询语句中on、where筛选的区别是什么，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

来看一个示例，有两张数据表，结构和数据如图所示

表main

表ext

可以把这两张表看作是用来存放用户信息的， main放置主要信息，ext表放置附加信息，两张表的关系是1对1的，以id字符作为对应关系键。现在我们需要将地址不为杭州的所有用户信息筛选出来，结果中需要包含main表和ext表的所有字段数据。

select * from main left JOIN exton main.id = ext.id and address <> '杭州'

闭上眼睛， 请用大脑人肉运行一下这段SQL， 想象一下是什么结果。

当把address <> '杭州'这个筛选条件放在on之后，查询得到的结果似乎跟我们预料中的不同，从结果中能看出，这个筛选条件好像只过滤掉了ext表中对应的记录，而main表中的记录并没有被过滤掉，也就是上图中标记为红色的那条记录。outer join相对于inner join的一个主要特性就是以一侧的表为基础，但是在这里以左表为基这一点却可以无视筛选条件，这未免也太霸道了一些。

把查询语句稍微改动一下，将地址的筛选条件从on转移至where

select * from main left JOIN ext on main.id = ext.id where address <> '杭州'

结果就如我们预期的那样了

造成这种结果上的差异要从outer join查询的逻辑查询的各个阶段说起。

总的来说，outer join 的执行过程分为4步

      1、先对两个表执行交叉连接(笛卡尔积)

      2、应用on筛选器

      3、添加外部行

      4、应用where筛选器

就拿上面不使用where筛选器的sql来说，执行的整个详细过程如下

第一步，对两个表执行交叉连接，结果如下，这一步会产生36条记录（此图显示不全）

第二步，应用on筛选器。筛选器中有两个条件，main.id = ext.id and address<> '杭州'，符合要求的记录如下

这似乎正是我们期望中查询的结果，然而在接下来的步骤中这个结果会被打乱

第三步，添加外部行。outer join有一个特点就是以一侧的表为基，假如另一侧的表没有符合on筛选条件的记录，则以null替代。在这次的查询中，这一步的作用就是将那条原本应该被过滤掉的记录给添加了回来

是不是不种画蛇添足的感觉, 结果就成了这样

第四步，应用where筛选器

在这条问题sql中，因为没有where筛选器，所以上一步的结果就是最终的结果了。

而对于那条地址筛选在where条件中的sql，这一步便起到了作用，将所有地址不属于杭州的记录筛选了出来

通过上面的讲解，已经能反应出在outer join中的筛选条件在on中和where中的区别，开发人员如能详细了解之中差别，能规避很多在编写sql过程中出现的莫名其妙的错误。

关于“sql连接查询语句中on、where筛选的区别是什么”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。