MYSQL之on和where的区别解读
作者:听雨婷婷 发布时间:2024-01-21 20:17:46
on和where的区别
多表查询语法结构:
table_reference {[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
在多表查询时,ON和where都表示筛选条件,on先执行,where后执行。
区别
外连接时,on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。而where条件是在临时表生成好后,再对临时表进行过滤的条件。
如:
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.`deptno` AND e.`deptno`=40;
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.`deptno` WHERE e.`deptno`=40;
来我们分析一下为什么会造成以上两种不同的结果。
on是生成临时表时使用的条件,上面我们采用的是左外连接,左外连接是以左表为基础的,左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录。也就是说emp是左表,dept是右表,条件是emp的deptno与dept中的deptno相等且为40时才连接,但emp表中不存在deptno为40的记录,也就是右表没有符合条件的记录,而记录不足的地方均用NULL来补充。
而where是在临时表生成好后,再对临时表进行过滤。也就是说emp表与dept的连接条件只是emp的deptno与dept中的deptno相等,然后在对生成的临时表进行筛选,由于emp表中不存在deptno为40的记录,所以未找到符合条件的记录。
由于内连接是从结果表中删除与其他被连接表中没有匹配行的所有行,所有在内连接时on和where的结果是相同的。而左外、右外与全连接由于它的特殊性,on和where造成的差别大小取决于表达式和表中的数据。
on & where条件区别和执行顺序
一、案例
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。假设有两张表:
表1:tab1
id | size |
1 | 10 |
2 | 20 |
3 | 30 |
表2:tab2
size | name |
10 | AAA |
20 | BBB |
20 | CCC |
两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')
第一条SQL的过程:
1、中间表on条件:tab1.size = tab2.size | tab1.idtab1.sizetab2.sizetab2.name11010AAA22020BBB22020CCC330(null)(null) |
2、再对中间表过滤where 条件:tab2.name=’AAA’ | tab1.idtab1.sizetab2.sizetab2.name11010AAA |
第二条SQL的过程:
1、中间表on条件:tab1.size = tab2.size and tab2.name=’AAA’(条件不为真也会返回左表中的记录) | tab1.idtab1.sizetab2.sizetab2.name11010AAA220(null)(null)330(null)(null) |
其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。
而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。
二、on、where、having 区别以及顺序
on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后。有时候如果这先后顺序不影响中间结果的话,那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的。
根据上面的分析,可以知道where也应该比having快点的,因为它过滤数据后才进行sum,所以having是最慢的。但也不是说having没用,因为有时在步骤3还没出来都不知道那个记录才符合要求时,就要用having了。
在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢。
如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。
在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里。
Ps:JOIN联表中ON、WHERE后面跟条件的区别对于JOIN的连表操作,这里就不细述了,当我们在对表进行JOIN关联操作时,对于ON和WHERE后面的条件,不清楚大家有没有注意过,有什么区别,可能有的朋友会认为跟在它们后面的条件是一样的,你可以跟在ON后面,如果愿意,也可以跟在WHERE后面。它们在ON和WHERE后面究竟有一个什么样的区别呢?
对于JOIN参与的表的关联操作,如果需要不满足连接条件的行也在我们的查询范围内的话,我们就必需把连接条件放在ON后面,而不能放在WHERE后面,如果我们把连接条件放在了WHERE后面,那么所有的LEFT、RIGHT,等这些操作将不起任何作用,对于这种情况,它的效果就完全等同于INNER连接。对于那些不影响选择行的条件,放在ON或者WHERE后面就可以。
记住:所有的连接条件都必需要放在ON后面,不然前面的所有 LEFT 和 RIGHT 关联将作为摆设,而不起任何作用。
三、优化分析
我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法,ON语句的执行是在JOIN语句之前的,也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合ON语句后面的条件,再决定是否JOIN。
口诀:先执行 ON,后执行 WHERE;ON 是建立关联关系,WHERE 是对关联关系的筛选。
来源:https://blog.csdn.net/tayngh/article/details/99684035
猜你喜欢
- 本文主要研究的是tornado 多进程模式的相关内容,具体如下。官方文档的helloworld实例中的启动方法:if __name__ ==
- python的三种输出格式环境:pycharm + python3.81. % (不推荐使用)格式: 格式字符串% (输出项1,输出项2,&
- Python是静态作用域语言,但是它自身是一个动态语言。在Python中变量的作用域是由变量在代码中的位置决定的,与C语言有些相似,但不是完
- 方案5 使用xml参数 对sql server xml类型参数不熟悉的童鞋需要先了解下XQuery概念,这里简单提下XQuery 是用来从
- 1. 关于 try.. finally..假如上帝用 python 为每一个来到世界的生物编写程序,那么除去中间过程的种种复杂实现,最不可避
- sort()方法排序列表中的对象,比较使用func(如果给定)。语法以下是sort()方法的语法:list.sort([func
- 有些时间没更新blog了,这两天为了更新<code collection>,于是重写了语法高亮的模块,这次是一个引擎,你可以根据
- OpenCV 是一个C++库,目前流行的计算机视觉编程库,用于实时处理计算机视觉方面的问题,它涵盖了很多计算机视觉领域的模块。在P
- 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。在不使用ICP的情况
- 本文实例讲述了Python松散正则表达式用法。分享给大家供大家参考,具体如下:Python 允许用户利用所谓的 松散正则表达式来完成这个任务
- 如下所示:import osdef anyTrue(predicate, sequence):return True in map(pred
- 准备写一个操作Excel脚本却在导入包的时候出现了一个小问题导入包from Tkinter import Tkfrom time impor
- 简单介绍:Selenium是一个Web的自动化测试工具,最初是为网站自动化测试而开发的,Selenium 可以直接运行在浏览器上,它支持所有
- Python与Perl,C和Java语言等有许多相似之处。不过,也有语言之间有一些明确的区别。本章的目的是让你迅速学习Pytho
- 本文通过Python3+PyQt5实现自定义部件–分数滑块。它既能支持键盘也支持鼠标,使用物理(视口)坐标通过绘制方式显示。#!/usr/b
- 本文实例讲述了PHP递归调用数组值并用其执行指定函数的方法。分享给大家供大家参考。具体分析如下:以下为wordpress原代码,为了偷懒,简
- python使用socket创建tcp服务器和客户端。服务器端为一个时间戳服务器,在接收到客户端发来的数据后,自动回复。客户端,等待用户输入
- Python初学,定义urlConfig 接收参数,正常传递参数时,出现,多给了一个参数的错误问题,定义class的函数之后,在调用的时候出
- 什么是deferdefer用来声明一个延迟函数,把这个函数放入到一个栈上, 当外部的包含方法return之前,返回参数到调用方法之前调用,也
- 如何引入同级包和模块工程项目结构如下包AnimalShow和Class_test是同级包,AnimalShow是父类,Gound,Sea,S