場(chǎng)景描述

以一個(gè)場(chǎng)景為例：

單據(jù)A：下游子表 （數(shù)據(jù)量級(jí)?。?br />單據(jù)B：下游主表（數(shù)據(jù)量級(jí)?。?br />單據(jù)C：中游子表（數(shù)據(jù)量級(jí)?。?br />單據(jù)D：中游主表（數(shù)據(jù)量級(jí)?。?br />單據(jù)E：上游子表（數(shù)據(jù)量級(jí)?。?br />單據(jù)F：上游主表（比其他表數(shù)據(jù)量級(jí)大）

需求：將單據(jù)F的某個(gè)字段，刷到單據(jù)A的某個(gè)字段上面。從A到F，都可以通過(guò)id連接索引的形式，來(lái)關(guān)聯(lián)。但是A到F的連接順序必須是從A到F順序連接。比如：

這幾個(gè)表的連接可以用下面的例子演示：

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 的區(qū)別

我們寫update語(yǔ)句的時(shí)候，肯定是想要用join連表的。但是到底是使用inner join；還是left join比較好呢？

• left join：

select a.*,b.* from a left join b on a.id = b.id , 這兩個(gè)表連接，根據(jù)后面的on條件，如果b表里面的b.id不符合 a.id = b.id ，那么a表里面的所有數(shù)據(jù)列都會(huì)給展示出來(lái)。然后b表沒(méi)有這種數(shù)據(jù)，所以sql里面的 b.* 會(huì)被全部填充成null

• inner join：

select a.*,b.* from a inner join b on a.id = b.id , 這兩個(gè)表連接，根據(jù)后面on條件，如果b表里面的b.id不符合 a.id = b.id ，那么a表里面的部分?jǐn)?shù)據(jù)列（不符合a.id = b.id條件的）就不會(huì)展示。

根據(jù)上面的定義，left join常用于select 語(yǔ)句；這是為了防止a表會(huì)少一些匹配記錄，為了能展示全a表，所以使用left join。

如下圖所示：

索引角度理解小結(jié)果集驅(qū)動(dòng)大結(jié)果集

不管是 left join 和 inner join，都要注意小結(jié)果集驅(qū)動(dòng)大結(jié)果集。a表 join b表的時(shí)候，

還是看之前例子的SQL:

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

假設(shè)a表數(shù)量級(jí)是100萬(wàn)條，b表數(shù)量級(jí)是100條。我這樣連接，就是大表驅(qū)動(dòng)小表；直接看查找次數(shù)：

在用后面on條件連接兩個(gè)表的時(shí)候，首先要走B+樹索引進(jìn)行匹配；拿a表這100w的數(shù)量級(jí)，逐條對(duì)比 -> B+樹 -> 匹配到 b表的記錄。假設(shè)B+樹查找b表的100條要用2次查找，那么最終查找次數(shù)就是： 100萬(wàn) * 2 次

如果是小表驅(qū)動(dòng)大表：

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

那么就會(huì)拿b表這100條，逐條對(duì)比 -> B+樹 -> 匹配到 a表的記錄。假設(shè)B+樹查找a表的100w條要用3次查找，那么最終查找次數(shù)就是： 100 * 3 次

從索引匹配的角度講，小結(jié)果集驅(qū)動(dòng)大結(jié)果集的效率優(yōu)化了不是一點(diǎn)半點(diǎn)。我們要有意識(shí)地讓小表在左邊，大表在右邊

但是如果你用的是inner join，MySQL內(nèi)部會(huì)做優(yōu)化，自動(dòng)讓小表在前大表在后。也就是說(shuō)你怎么寫，效率都是一樣的。但是left join卻不能自動(dòng)優(yōu)化，這點(diǎn)需要注意！

update語(yǔ)句常用 inner join而不是left join

舉例如下SQL:

（任務(wù)目標(biāo)：用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，假設(shè)你全部都用的left join做關(guān)聯(lián)，由于你最終update 的是 a的字段；假設(shè)a表在left join的過(guò)程中，因?yàn)槟硞€(gè)點(diǎn)匹配不到f表，那么用f 表字段更新a表字段 這一個(gè)過(guò)程中，一旦有任意一個(gè)環(huán)節(jié)匹配不到，那么f 表的字段全都會(huì)用null填充。最終，a表匹配不到f表的數(shù)據(jù)，都會(huì)被更新成null ！

但是如果你用inner join，用f 表字段更新a表字段 這一個(gè)過(guò)程中，一旦有任意一個(gè)環(huán)節(jié)匹配不到，那么a表匹配不到 f 表的所有數(shù)據(jù)就不會(huì)顯示，也就是說(shuō)不會(huì)更新。

你想想，你都匹配不到數(shù)據(jù)列。你還更新啥，難道更新null嗎。基于上面的原因，inner join 其實(shí)就滿足需求了

況且！left join 要考慮這幾張表的大小關(guān)系，誰(shuí)大誰(shuí)小，小結(jié)果集驅(qū)動(dòng)大結(jié)果集。但是inner join 就完全不用考慮此問(wèn)題，因?yàn)?strong>inner join MySQL內(nèi)部會(huì)做優(yōu)化，自動(dòng)讓小表在前大表在后。

到此這篇關(guān)于MySQL中的 inner join 和 left join的區(qū)別的文章就介紹到這了,更多相關(guān)mysql inner join 和 left join區(qū)別內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論