当前位置: 首页 > 编程笔记 >

sql连接查询语句中on、where筛选的区别总结

慕鸿波
2023-03-14
本文向大家介绍sql连接查询语句中on、where筛选的区别总结,包括了sql连接查询语句中on、where筛选的区别总结的使用技巧和注意事项,需要的朋友参考一下

前言

相信对于每位程序员来说,sql查询这个东西, 要说它简单, 可以很简单, 通常情况下只需使用增删查改配合编程语言的逻辑表达能力,就能实现所有功能。 但是增删查改并不能代表sql语句的所有, 完整的sql功能会另人望而生畏。 就拿比普通增删查改稍微复杂一个层次的连接查询来说, 盲目使用, 也会出现意料之外的危险结果,导致程序出现莫名其妙的BUG。

在连接查询语法中,另人迷惑首当其冲的就要属on筛选和where筛选的区别了, 在我们编写查询的时候, 筛选条件的放置不管是在on后面还是where后面, 查出来的结果总是一样的, 既然如此,那为什么还要多此一举的让sql查询支持两种筛选器呢? 事实上, 这两种筛选器是存在差别的,只是如果不深挖不容易发现而已。

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

表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过程中出现的莫名其妙的错误。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对小牛知识库的支持。

 类似资料:
  • 问题内容: 我有一个oc_category_description表,其中的列是: category_id 姓名 和其他表oc_category,其中的列是: category_id 图像 parent_id 这是oc_category_description表的示例图片 oc_category表 在这里,我想显示名称,category_id,图像,parent_id,其中oc_category

  • 问题内容: 以下语句给出相同的结果(一个使用,另一个使用): 我只能看到在左外部联接的情况下找到“无与伦比”的情况:( 以找出从未有人发送过的礼物) 在这种情况下,它首先使用,然后使用。是否先做匹配,然后做“辅助”过滤?还是有使用vs 的更一般规则?谢谢。 问题答案: 是整个查询的一部分,是每个单独联接的一部分。 只能引用以前使用的表的字段。 如果与左表中的记录没有实际匹配,则从右表中返回一条记录

  • 问题内容: 我仍然生活在这种模糊性中:从概念上讲,SQL中的 语句 和 查询 之间有什么区别?谁能给每个人一个定义?这将很有用,例如,当在程序中以每个人都清楚的方式选择变量名称时。谢谢! 另外: 我如何调用由多个语句组成的SQL代码块,其中语句之间用分号()分隔?谁已经答复可以编辑他的答案。非常感谢! 问题答案: 一个 说法 是,数据库引擎识别为一个有效的命令的任何文字。截至: SQL语句是一个字

  • 本文向大家介绍MySQL查询条件中放置on和where的区别分析,包括了MySQL查询条件中放置on和where的区别分析的使用技巧和注意事项,需要的朋友参考一下 导语 今天在写 SQL 的时候,遇到一个问题。需求是这样的,查询数据,按照评分倒序、近一周访问量倒序,这样进行排序。问题是常规的写法,将 day >= xxx 条件放到 where 中, 如果某些数据近一周没有访问量,那么这条数据就查不

  • 问题内容: 对于 内部联接 ,在子句或子句中应用过滤器的性能有何不同?哪个会更有效,或者优化器将使它们相等? 加入 VS 在哪里 Oracle11gR2 问题答案: 应该没有区别。在这两种情况下,优化器都应生成相同的计划,并且应该能够基于特定查询的最有效方法,在这两种情况下的连接之前,之后或之中应用谓词。 当然,事实优化 可以 做一些事情,一般来说,是不能保证优化器 会 做一些实际的特定查询。随着