Mysql的left join無效及使用
今天寫sql發(fā)現(xiàn)使用left join 沒有把左邊表的數(shù)據(jù)全部查詢出來,讓我郁悶了一會(huì),后來仔細(xì)研究了一會(huì)才知道自己犯了個(gè)常識(shí)性的錯(cuò)誤(我是菜鳥)
這是原sql
這樣的查詢并不能將tb_line這張表的數(shù)據(jù)都查詢出來,好尷尬...
后面我才知道原來當(dāng)我們進(jìn)行多表查詢,在執(zhí)行到where之前,會(huì)先形成一個(gè)臨時(shí)表
而on就是臨時(shí)表中的條件篩選,使用left join則不管條件是否為真,都會(huì)查詢出左邊表的數(shù)據(jù),條件為假的,則顯示為null
where則是在臨時(shí)表生成之后的過濾條件
在第一張圖中,我將tb_vehicle這張表的過濾條件放在where之中,那left join所產(chǎn)生條件為假的數(shù)據(jù),則會(huì)在where 的 v.del_flag='0'中被過濾掉(因?yàn)闂l件為假的數(shù)據(jù),del_flag都為空)
所以我看似使用了left join ,實(shí)際上這樣寫與使用inner join的結(jié)果是一樣的
正確sql如下:
在臨時(shí)表中就做好條件篩選,這樣就能夠得到左邊表的數(shù)據(jù)
總結(jié):
使用left join 并需要做條件查詢的時(shí)候,需要仔細(xì)斟酌改條件篩選放在on后面還是where后面
Mysql left join 避坑指南
現(xiàn)象
left join在我們使用mysql查詢的過程中可謂非常常見,比如博客里一篇文章有多少條評(píng)論、商城里一個(gè)貨物有多少評(píng)論、一條評(píng)論有多少個(gè)贊等等。但是由于對(duì)join、on、where等關(guān)鍵字的不熟悉,有時(shí)候會(huì)導(dǎo)致查詢結(jié)果與預(yù)期不符,所以今天我就來總結(jié)一下,一起避坑。
這里我先給出一個(gè)場(chǎng)景,并拋出兩個(gè)問題,如果你都能答對(duì)那這篇文章就不用看了。
假設(shè)有一個(gè)班級(jí)管理應(yīng)用,有一個(gè)表classes,存了所有的班級(jí);有一個(gè)表students,存了所有的學(xué)生,具體數(shù)據(jù)如下:
那么現(xiàn)在有兩個(gè)需求:
找出每個(gè)班級(jí)的名稱及其對(duì)應(yīng)的女同學(xué)數(shù)量
找出一班的同學(xué)總數(shù)
對(duì)于需求1,大多數(shù)人不假思索就能想出如下兩種sql寫法,請(qǐng)問哪種是對(duì)的?
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and s.gender = 'F'
group by c.name
或者
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where s.gender = 'F'
group by c.name
對(duì)于需求2,大多數(shù)人也可以不假思索的想出如下兩種sql寫法,請(qǐng)問哪種是對(duì)的?
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where c.name = '一班'
group by c.name
或者
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and c.name = '一班'
group by c.name
請(qǐng)不要繼續(xù)往下翻 ??!先給出你自己的答案,正確答案就在下面。
~
~
~
答案是兩個(gè)需求都是第一條語句是正確的,要搞清楚這個(gè)問題,就得明白mysql對(duì)于left join的執(zhí)行原理,下節(jié)進(jìn)行展開。
根源
mysql 對(duì)于left join的采用類似嵌套循環(huán)的方式來進(jìn)行從處理,以下面的語句為例:
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on過濾條件,缺失則認(rèn)為是TRUE,P2是where過濾條件,缺失也認(rèn)為是TRUE,該語句的執(zhí)行邏輯可以描述為:
FOR each row lt in LT {// 遍歷左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍歷右表每一行,找到滿足join條件的行
IF P2(lt, rt) {//滿足 where 過濾條件
t:=lt||rt;//合并行,輸出該行
}
b=TRUE;// lt在RT中有對(duì)應(yīng)的行
}
IF (!b) { // 遍歷完RT,發(fā)現(xiàn)lt在RT中沒有有對(duì)應(yīng)的行,則嘗試用null補(bǔ)一行
IF P2(lt,NULL) {// 補(bǔ)上null后滿足 where 過濾條件
t:=lt||NULL; // 輸出lt和null補(bǔ)上的行
}
}
}
當(dāng)然,實(shí)際情況中MySQL會(huì)使用buffer的方式進(jìn)行優(yōu)化,減少行比較次數(shù),不過這不影響關(guān)鍵的執(zhí)行流程,不在本文討論范圍之內(nèi)。
從這個(gè)偽代碼中,我們可以看出兩點(diǎn):
如果想對(duì)右表進(jìn)行限制,則一定要在on條件中進(jìn)行,若在where中進(jìn)行則可能導(dǎo)致數(shù)據(jù)缺失,導(dǎo)致左表在右表中無匹配行的行在最終結(jié)果中不出現(xiàn),違背了我們對(duì)left join的理解。因?yàn)閷?duì)左表無右表匹配行的行而言,遍歷右表后b=FALSE,所以會(huì)嘗試用NULL補(bǔ)齊右表,但是此時(shí)我們的P2對(duì)右表行進(jìn)行了限制,NULL若不滿足P2(NULL一般都不會(huì)滿足限制條件,除非IS NULL這種),則不會(huì)加入最終的結(jié)果中,導(dǎo)致結(jié)果缺失。
如果沒有where條件,無論on條件對(duì)左表進(jìn)行怎樣的限制,左表的每一行都至少會(huì)有一行的合成結(jié)果,對(duì)左表行而言,若右表若沒有對(duì)應(yīng)的行,則右表遍歷結(jié)束后b=FALSE,會(huì)用一行NULL來生成數(shù)據(jù),而這個(gè)數(shù)據(jù)是多余的。所以對(duì)左表進(jìn)行過濾必須用where。
下面展開兩個(gè)需求的錯(cuò)誤語句的執(zhí)行結(jié)果和錯(cuò)誤原因:
需求1
需求2
需求1由于在where條件中對(duì)右表限制,導(dǎo)致數(shù)據(jù)缺失(四班應(yīng)該有個(gè)為0的結(jié)果)
需求2由于在on條件中對(duì)左表限制,導(dǎo)致數(shù)據(jù)多余(其他班的結(jié)果也出來了,還是錯(cuò)的)
總結(jié)
通過上面的問題現(xiàn)象和分析,可以得出了結(jié)論:在left join語句中,左表過濾必須放where條件中,右表過濾必須放on條件中,這樣結(jié)果才能不多不少,剛剛好。
SQL 看似簡(jiǎn)單,其實(shí)也有很多細(xì)節(jié)原理在里面,一個(gè)小小的混淆就會(huì)造成結(jié)果與預(yù)期不符,所以平時(shí)要注意這些細(xì)節(jié)原理,避免關(guān)鍵時(shí)候出錯(cuò)。
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
您可能感興趣的文章:- 淺談mysql join底層原理
- SQL語句中JOIN的用法場(chǎng)景分析
- MYSQL數(shù)據(jù)庫(kù)基礎(chǔ)之Join操作原理
- mysql left join快速轉(zhuǎn)inner join的過程
- 為什么代碼規(guī)范要求SQL語句不要過多的join
- mysql高效查詢left join和group by(加索引)
- MySQL的join buffer原理
- SQL之各種join小結(jié)詳細(xì)講解