MySQL中的 inner join 和 left join的区别解析(小结果集驱动大结果集)
目录
  • 场景描述
  • inner join 和 left join 的区别

场景描述

以一个场景为例：

单据A：下游子表 （数据量级小）
单据B：下游主表（数据量级小）
单据C：中游子表（数据量级小）
单据D：中游主表（数据量级小）
单据E：上游子表（数据量级小）
单据F：上游主表（比其他表数据量级大）

需求：将单据F的某个字段，刷到单据A的某个字段上面。从A到F，都可以通过id连接索引的形式，来关联。但是A到F的连接顺序必须是从A到F顺序连接。比如：

这几个表的连接可以用下面的例子演示：

a join b on a.id = b.id
b join c on b.id = c.mainId
c join d on c.id = d.tableId
d join e on d.id = e.tid
e join f on e.tid = f.code

inner join 和 left join 的区别

我们写update语句的时候，肯定是想要用join连表的。但是到底是使用inner join；还是left join比较好呢？

  • left join：

select a.*,b.* from a left join b on a.id = b.id , 这两个表连接，根据后面的on条件，如果b表里面的b.id不符合 a.id = b.id ，那么a表里面的所有数据列都会给展示出来。然后b表没有这种数据，所以sql里面的 b.* 会被全部填充成null

  • inner join：

select a.*,b.* from a inner join b on a.id = b.id , 这两个表连接，根据后面on条件，如果b表里面的b.id不符合 a.id = b.id ，那么a表里面的部分数据列（不符合a.id = b.id条件的）就不会展示。

根据上面的定义，left join常用于select 语句；这是为了防止a表会少一些匹配记录，为了能展示全a表，所以使用left join。

如下图所示：

索引角度理解小结果集驱动大结果集

不管是 left join 和 inner join，都要注意小结果集驱动大结果集。a表 join b表的时候，

还是看之前例子的SQL:

select a.*,b.* from a left join b on a.id = b.id

假设a表数量级是100万条，b表数量级是100条。我这样连接，就是大表驱动小表；直接看查找次数：

在用后面on条件连接两个表的时候，首先要走B+树索引进行匹配；拿a表这100w的数量级，逐条对比 -> B+树 -> 匹配到 b表的记录。假设B+树查找b表的100条要用2次查找，那么最终查找次数就是： 100万 * 2 次

如果是小表驱动大表：

select a.*,b.* from b left join a on a.id = b.id

那么就会拿b表这100条，逐条对比 -> B+树 -> 匹配到 a表的记录。假设B+树查找a表的100w条要用3次查找，那么最终查找次数就是： 100 * 3 次

从索引匹配的角度讲，小结果集驱动大结果集的效率优化了不是一点半点。我们要有意识地让小表在左边，大表在右边

但是如果你用的是inner join，MySQL内部会做优化，自动让小表在前大表在后。也就是说你怎么写，效率都是一样的。但是left join却不能自动优化，这点需要注意！

update语句常用 inner join而不是left join

举例如下SQL:

（任务目标：用f 表字段更新a表字段）

update a
inner join b on a.id = b.id
inner join c on b.id = c.mainId
inner join d on c.id = d.tableId
inner join e on d.id = e.tid
inner join f on e.tid = f.code
set a.Demand_orgid = f.req_org_id
where xxx = xxx;

update 原则上 都得用inner join。

看上面的SQL，假设你全部都用的left join做关联，由于你最终update 的是 a的字段；假设a表在left join的过程中，因为某个点匹配不到f表，那么用f 表字段更新a表字段 这一个过程中，一旦有任意一个环节匹配不到，那么f 表的字段全都会用null填充。最终，a表匹配不到f表的数据，都会被更新成null ！

但是如果你用inner join，用f 表字段更新a表字段 这一个过程中，一旦有任意一个环节匹配不到，那么a表匹配不到 f 表的所有数据就不会显示，也就是说不会更新。

你想想，你都匹配不到数据列。你还更新啥，难道更新null吗。基于上面的原因，inner join 其实就满足需求了

况且！left join 要考虑这几张表的大小关系，谁大谁小，小结果集驱动大结果集。但是inner join 就完全不用考虑此问题，因为inner join MySQL内部会做优化，自动让小表在前大表在后。

到此这篇关于MySQL中的 inner join 和 left join的区别的文章就介绍到这了,更多相关mysql inner join 和 left join区别内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mysql left join快速转inner join的过程

    mysql left join快速转inner join的过程

    在日常优化过程中,发现一个怪事情,同一个SQL出现两个完全不一样执行计划,left join 连驱动表都可以变成不一样. 对于left join,如果where条件里有被关联表过滤,left join有可能被转成inner join ,本案例中shopInfo有ShopCategory = 'LOC'过滤条件: 保证shopInfo的记录非NULL,因此left join在优化过程中可以转为inner join. 那么O和S的JOIN顺序就是可以交换的. 验证结论: 创建表: --班级表 CRE

  • SQL之left join、right join、inner join的区别浅析

    今天和某朋友聊天,谈到他们公司的一个小问题.如下: 表A设备表,存储MAC地址,省份,城市,区. 表B软件表,存储MAC地址,软件名字. 功能是可以按省份,城市,或者区来查询软件列表. 你猜它现在如何做的? 它通过省份,城市,或者区取得MAC地址,然后查询B表用in查询. 这个很明显是不合理的,处理这种多对多的关系,为什么不用多表联查呢? 链表的方法常用的有3个: (inner) join 内部等值连接.left join 左连接 和 right join右连接. 有什么区别呢?怎么用呢? 下面

  • 超详细mysql left join,right join,inner join用法分析

    下面是例子分析表A记录如下: aID        aNum 1           a20050111 2           a20050112 3           a20050113 4           a20050114 5           a20050115 表B记录如下: bID        bName 1            2006032401 2           2006032402 3           2006032403 4           20

  • 解析sql语句中left_join、inner_join中的on与where的区别

    table a(id, type):id     type ----------------------------------1      1         2      1          3      2          table b(id, class):id    class ---------------------------------1      12      2sql语句1:select a.*, b.* from a left join b on a.id = b

  • mysql中inner join和left join使用详解

    目录 区别 inner join 场景 inner join 场景 区别 返回不同1.inner join只返回两个表中联结字段相等的行2.left join的数量小于等于左表和右表中的记录数量. 数量不同1.inner join返回包括左表中的所有记录和右表中联结字段相等的记录.2.left join的数量以左表中的记录数量相同 记录属性不同1.inner join不足的记录属性会被直接舍弃2.left join不足的记录属性用NULL填充 inner join 场景 设计两张表: chann

  • mysql中的delete,drop和truncate有什么区别

    mysql中的delete,drop和truncate有什么区别

    目录 前言: 1.delete 1.1 delete 实现原理 1.2 关于自增列 2.truncate 2.1 truncate 实现原理 2.2 重置自增列 3.drop 4.三者的区别 总结 前言: 在 MySQL 中,删除的方法总共有 3 种:delete.truncate.drop,而三者的用法和使用场景又完全不同,接下来我们具体来看. 1.delete detele 可用于删除表的部分或所有数据,它的使用语法如下: delete from table_name [where...]

  • mysql清空表数据的两种方式和区别解析

    在MySQL中删除数据有两种方式: truncate(截短)属于粗暴型的清空 delete属于精细化的删除 删除操作 如果你需要清空表里的所有数据,下面两种均可: delete from tablename; truncate table tablename; 而如果你只是删除一部分数据,就只能使用delete: delete from tablename where case1 and case2; 区别 在精细化的删除部分数据时,只能使用delete. 而清空所有表数据时,两者均可,此时这两

  • MySQL中的乐观锁,悲观锁和MVCC全面解析

    前言 在数据库的实际使用过程中,我们常常会遇到不希望数据被同时写或者读的情景,例如秒杀场景下,两个请求同时读到系统还有库存1个,然后又先后把库存更新为0,这时候就会出现超卖的情况,这时候货物的实际库存和我们的记录就会对应不上了. 为了解决这种资源竞争导致的数据不一致等问题,我们需要有一种机制来进行保证数据的正确访问和修改,而在数据库中,这种机制就是数据库的并发控制.其中乐观并发控制,悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段. 悲观并发控制 本质 维基百科:在关系数据库管理系

  • nginx中的两个模块的proxy_pass的区别解析

    1.ngx_stream_proxy_module模块的proxy_pass指令 只能在server段使用使用, 只需要提供域名或ip地址和端口. 可以理解为端口转发,可以是tcp端口,也可以是udp端口. server { listen 127.0.0.1:12345; proxy_pass 127.0.0.1:8080; } server { listen 12345; proxy_connect_timeout 1s; proxy_timeout 1m; proxy_pass exampl

  • Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结

    用了这么长时间,发现自己竟然不知道utf_bin和utf_general_ci这两者到底有什么区别.. ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的; bin 是二进制, a 和 A 会别区别对待. 例如你运行: SELECT * FROM table WHERE txt = 'a' 那么在utf8_bin中你就找不到 txt = 'A' 的那一行, 而 utf8_general_ci 则可以. utf8_general_

  • mysql中insert ignore、insert和replace的区别及说明

    目录 insert ignore.insert和replace的区别 测试代码 insert replace insert ignore insert ignore.insert和replace的区别 指令 已存在 不存在 举例 insert 报错 插入 insert into names(name, age) values(“小明”, 23); insert ignore 忽略 插入 insert ignore into names(name, age) values(“小明”, 24); r

  • WinForm中KeyDown,KeyPress和KeyUp的顺序与区别解析

    WinForm中的键盘按键有KeyDown,KeyPress和KeyUp事件.那么它们的顺序以及区别在哪里呢?本文就此作出如下分析: 一.顺序: 根据MSDN所讲,三者的顺序是:KeyDown>KeyPress>KeyUp. 二.区别: KeyDown和KeyPress很接近,都是在按键按下时候先后发生.KeyUp是放掉某个键之后发生的事件. 三.KeyDown和KeyPress: KeyDown的事件中属性是KeyEventArgs,其中包含三个重要属性: 1)KeyCode(返回一个Key

  • Python中lambda的用法及其与def的区别解析

    python中的lambda通常是用来在python中创建匿名函数的,而用def创建的方法是有名称的,除了从表面上的方法名不一样外,python中的lambda还有如下几点和def不一样: 1. python lambda会创建一个函数对象,但不会把这个函数对象赋给一个标识符,而def则会把函数对象赋值给一个变量. 2. python lambda它只是一个表达式,而def则是一个语句. 下面是python lambda的格式,看起来非常精简. lambda x: print x 如果你在pyt

  • Vue中的v-model，v-bind，v-on的区别解析

    目录 v-model v-bind(缩写为 : ) v-on(缩写为@) 总结 v-model v-model是进行动态双向绑定的(只能用在input, textarea, select上),以input为例,进行绑定后,vue对象中data的相应值会与input的输入同步变动. // HTML <input type="text" v-model="message"> new Vue({ data { message: ' ', //最开始messag

  • Mysql中基本语句优化的十个原则小结

    前言 在数据库的应用中,程序员们通过不断的实践总结了很多经验,这些经验是一些普遍的适用规则,每一个程序员都应该了解并记住它们,在构造sql时,养成良好的习惯,下面话不多说,来看看详细的介绍: mysql基本语句优化原则 一.尽量避免在列上运算,这样会导致索引失效 select * from t where YEAR(d) >= 2011; 优化为 select * from t where d >='2011-0101' 二.使用 JOIN 时,应该用小结果集驱动大结果集,同时把复杂的 JOI

随机推荐

其他