摘要:

介紹在多表關(guān)聯(lián)SQL語(yǔ)句中，過濾條件放在on和where子句中的區(qū)別——inner join中沒區(qū)別，外連接就不一樣。

綜述

螞蟻金服的一道SQL面試題如下：SQL語(yǔ)句中，過濾條件放在on和where子句中的區(qū)別。當(dāng)時(shí)滿腦子是left join和inner join等，覺得沒區(qū)別啊！細(xì)思極恐，故梳理一下。

過濾條件放在on和where中的區(qū)別

數(shù)據(jù)庫(kù)在通過連接兩張或多張表來(lái)查詢記錄時(shí)，都會(huì)先通過join on子句生成一張中間的臨時(shí)表，然后再在臨時(shí)表中通過where子句過濾數(shù)據(jù)并將結(jié)果集返回給用戶。在使用多表關(guān)聯(lián)時(shí)，on和where子句的區(qū)別如下：

1、 on子句是在生成臨時(shí)表時(shí)使用的，它不管on中的條件是否為真，都會(huì)返回驅(qū)動(dòng)表中的記錄。右表成立就返回對(duì)應(yīng)數(shù)據(jù)，不成立就賦值為null。

通俗地說(shuō) ，對(duì)于左外連接或者右外連接的驅(qū)動(dòng)表來(lái)說(shuō)，如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過濾條件的記錄，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充。

2、where子句是對(duì)已經(jīng)生成的臨時(shí)表進(jìn)行過濾的條件，這時(shí)過濾臨時(shí)表中全部條件不為真的記錄。

需要注意的是內(nèi)連接中的WHERE子句和ON子句是等價(jià)的。ON子句是專門為外連接驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表找不到匹配記錄時(shí)應(yīng)不應(yīng)該把該記錄加入結(jié)果集這個(gè)場(chǎng)景提出的，所以如果把ON子句放到內(nèi)連接中，MySQL會(huì)把它和WHERE子句一樣對(duì)待。

在使用 INNER JOIN 時(shí)會(huì)產(chǎn)生一個(gè)結(jié)果集，WHERE 條件在這個(gè)結(jié)果集中再根據(jù)條件進(jìn)行過濾，如果把條件都放在 ON 中，在 INNER JOIN 的時(shí)候就進(jìn)行過濾了，比如

SELECT A.* FROM A
INNER JOIN B ON B.ID = A.ID AND B.State = 1 -- on子句可以寫多個(gè)條件
INNER JOIN C ON B.ID = C.ID

在聯(lián)查 B 表時(shí)，就過濾掉狀態(tài)不等于 1 的記錄，從而使得狀態(tài)不等于 1 的記錄就不需要去聯(lián)查 C 表了，而

SELECT A.* FROM A
INNER JOIN B ON B.ID = A.ID AND B.State = 1
INNER JOIN C ON B.ID = C.ID
WHERE B.State = 1

則不管 B 的狀態(tài)是否滿足，都去聯(lián)查 C生成臨時(shí)表，最后再通過where子句將滿足B 狀態(tài)State = 1的記錄查出來(lái)。綜上所述，得出的結(jié)論就是把 過濾條件放入inner join on 比直接 where 的查詢效率要高。

小結(jié)

到此這篇關(guān)于SQL語(yǔ)句過濾條件放在on與where子句中的區(qū)別和聯(lián)系的文章就介紹到這了,更多相關(guān)SQL語(yǔ)句過濾條件在on與where子句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

Reference

• http://www.dbjr.com.cn/article/262956.htm

最新評(píng)論