摘要:

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

綜述

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

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

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

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

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

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

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

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

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

在聯(lián)查 B 表時，就過濾掉狀態(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生成臨時表，最后再通過where子句將滿足B 狀態(tài)State = 1的記錄查出來。綜上所述，得出的結(jié)論就是把 過濾條件放入inner join on 比直接 where 的查詢效率要高。

小結(jié)

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

Reference

• http://www.dhdzp.com/article/262956.htm