|
原先一直對(duì)SQL左右連接中的on and和on where的區(qū)別不是太了解，直到在網(wǎng)上看到了下面這段話才豁然開(kāi)朗。
在使用left join時(shí)，on and和on where條件的區(qū)別如下：
1、on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左邊表中的記錄。
2、where條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。這時(shí)已經(jīng)沒(méi)有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過(guò)濾掉，on后的條件用來(lái)生成左右表關(guān)聯(lián)的臨時(shí)表，where后的條件對(duì)臨時(shí)表中的記錄進(jìn)行過(guò)濾。
實(shí)踐是檢驗(yàn)真理的唯一標(biāo)準(zhǔn)，接下來(lái)用實(shí)驗(yàn)來(lái)證明一下：
先上表結(jié)構(gòu)以及數(shù)據(jù)：
- SQL> create table A (id int, type int);
- SQL> select * from A;
- ID TYPE
- ---------- ----------
- 1 1
- 2 1
- 3 2
-
- SQL> create table B(id int ,class int);
- SQL> select * from B;
-
- ID CLASS
- ---------- ----------
- 1 1
- 2 2
①
- SQL> select * from A left join B on A.id = B.id where A.type = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 2 1 2 2
其運(yùn)行結(jié)果如下：
- SQL> select * from A left join B on A.id = b.id;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 2 1 2 2
- 3 2
然后加上where A.type = 1對(duì)臨時(shí)表進(jìn)行過(guò)濾，除掉A.type不為1的，顯然結(jié)果正確。
②
- SQL> select * from A left join B on A.id = B.id and A.type = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 2 1 2 2
- 3 2
③
- SQL> select * from A left join B on A.id = B.id and B.class = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 3 2
- 2 1
④
- SQL> select * from A left join B on A.id = B.id where B.class = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
再來(lái)看看內(nèi)連接inner join on and和 on where的區(qū)別：
由于剛開(kāi)始表的數(shù)據(jù)不是太適合，所以先稍微更新一下，這樣更好觀察inner join和left join在and和where的不同之處。
- SQL> update A set type = 2 where id = 1;
-
- 已更新 1 行。
-
- SQL> select * from A;
-
- ID TYPE
- ---------- ----------
- 1 2
- 2 1
- 3 2
-
- SQL> select * from B;
-
- ID CLASS
- ---------- ----------
- 1 1
- 2 2
⑤
- SQL> select * from A inner join B on A.id = B.id and A.type = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 2 1 2 2
- SQL> select * from A inner join B on A.id = B.id;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 2 1 1
- 2 1 2 2
顯然如果按左連接的邏輯，這個(gè)結(jié)果就是錯(cuò)誤的。但這是Oracle輸出的，而不是我瞎打的，顯然在內(nèi)連接時(shí)與左連接不同了。這里on and條件和on where條件一樣對(duì)生成以后的臨時(shí)表同樣會(huì)被過(guò)濾。顯然A表id為1的type不為1，所以它被過(guò)濾了。
再上幾組來(lái)驗(yàn)證一下上面這個(gè)猜想是否是正確的：
⑥
再來(lái)看看內(nèi)連接的where：
- SQL> select * from A inner join B on A.id = B.id where A.type = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 2 1 2 2
⑦
- SQL> select * from A inner join B on A.id = B.id and B.class = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 2 1 1
⑧
- SQL> select * from A inner join B on A.id = B.id where B.class = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 2 1 1
最后上倆沒(méi)有使用連接語(yǔ)句的例子：
- </pre><p></p><pre name="code" class="sql">SQL> select * from A,B where A.id = B.id and A.type = 1;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 2 1 2 2
-
-
-
- SQL> select * from A,B where A.type = 1 and A.id = B.id;
-
- ID TYPE ID CLASS
- ---------- ---------- ---------- ----------
- 1 1 1 1
- 2 1 2 2
比較簡(jiǎn)單，不作解釋。
總結(jié)一下：
在使用left join時(shí)，on和where條件的區(qū)別如下：
1、on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左邊表中的記錄。（實(shí)際上左連接中如果and語(yǔ)句是對(duì)左表進(jìn)行過(guò)濾的，那么不管真假都不起任何作用。如果是對(duì)右表過(guò)濾的，那么左表所有記錄都返回，右表篩選以后再與左表連接返回）
2、where條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。這時(shí)已經(jīng)沒(méi)有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過(guò)濾掉，on后的條件用來(lái)生成左右表關(guān)聯(lián)的臨時(shí)表，where后的條件對(duì)臨時(shí)表中的記錄進(jìn)行過(guò)濾。
在使用inner join時(shí)，不管是對(duì)左表還是右表進(jìn)行篩選，on and和on where都會(huì)對(duì)生成的臨時(shí)表進(jìn)行過(guò)濾。
相關(guān)文章：SQL joins
尊重版權(quán)，轉(zhuǎn)載請(qǐng)不要?jiǎng)h除下方二維碼并注明本文鏈接：http://blog.csdn.NET/xingzhemoluo/article/details/39677891
歡迎關(guān)注行者摩羅微信公眾號(hào)(xingzhemoluo)，共同交流編程經(jīng)驗(yàn)，掃描下方二維碼即可;
|
