|
原先一直對SQL左右連接中的on and和on where的區(qū)別不是太了解，直到在網(wǎng)上看到了下面這段話才豁然開朗。
在使用left join時，on and和on where條件的區(qū)別如下：
1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
2、where條件是在臨時表生成好后，再對臨時表進(jìn)行過濾的條件。這時已經(jīng)沒有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉，on后的條件用來生成左右表關(guān)聯(lián)的臨時表，where后的條件對臨時表中的記錄進(jìn)行過濾。
實踐是檢驗真理的唯一標(biāo)準(zhǔn)，接下來用實驗來證明一下：
先上表結(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
其運行結(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對臨時表進(jìn)行過濾，除掉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
再來看看內(nèi)連接inner join on and和 on where的區(qū)別：
由于剛開始表的數(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
顯然如果按左連接的邏輯，這個結(jié)果就是錯誤的。但這是Oracle輸出的，而不是我瞎打的，顯然在內(nèi)連接時與左連接不同了。這里on and條件和on where條件一樣對生成以后的臨時表同樣會被過濾。顯然A表id為1的type不為1，所以它被過濾了。
再上幾組來驗證一下上面這個猜想是否是正確的：
⑥
再來看看內(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
最后上倆沒有使用連接語句的例子：
- </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é)一下：
在使用left join時，on和where條件的區(qū)別如下：
1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。（實際上左連接中如果and語句是對左表進(jìn)行過濾的，那么不管真假都不起任何作用。如果是對右表過濾的，那么左表所有記錄都返回，右表篩選以后再與左表連接返回）
2、where條件是在臨時表生成好后，再對臨時表進(jìn)行過濾的條件。這時已經(jīng)沒有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉，on后的條件用來生成左右表關(guān)聯(lián)的臨時表，where后的條件對臨時表中的記錄進(jìn)行過濾。
在使用inner join時，不管是對左表還是右表進(jìn)行篩選，on and和on where都會對生成的臨時表進(jìn)行過濾。
相關(guān)文章：SQL joins
尊重版權(quán)，轉(zhuǎn)載請不要刪除下方二維碼并注明本文鏈接：http://blog.csdn.NET/xingzhemoluo/article/details/39677891
歡迎關(guān)注行者摩羅微信公眾號(xingzhemoluo)，共同交流編程經(jīng)驗，掃描下方二維碼即可;
|
