Sql_连接查询中on筛选与where筛选的区别

sql中的连接查询分为3种, cross join,inner join,和outer join ,  在 cross join和inner join中,筛选条件放在on后面还是where后面是没区别的,极端一点,在编写这两种连接查询的时候,只用on不使用where也没有什么问题。因此,on筛选和where筛选的差别只是针对outer join,也就是平时最常使用的left join和right join。

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

表main

Sql_连接查询中on筛选与where筛选的区别

表ext

Sql_连接查询中on筛选与where筛选的区别

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

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

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

Sql_连接查询中on筛选与where筛选的区别

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

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

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

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

Sql_连接查询中on筛选与where筛选的区别

造成这种结果上的差异要从outer join查询的逻辑查询的各个阶段说起。总的来说,outer join 的执行过程分为4步

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

2、应用on筛选器

3、添加外部行

4、应用where筛选器

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

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

Sql_连接查询中on筛选与where筛选的区别

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

Sql_连接查询中on筛选与where筛选的区别

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

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

Sql_连接查询中on筛选与where筛选的区别

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

Sql_连接查询中on筛选与where筛选的区别

第四步,应用where筛选器

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

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

Sql_连接查询中on筛选与where筛选的区别

上一篇:Java中的一些常见错误


下一篇:【洛谷4719】 动态dp(树链剖分,dp,矩阵乘法)