【轉】MYSQL 表連接 ON AND 和ON WHERE 的區別
1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
2、where條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉，on后的條件用來生成左右表關聯的臨時表，where后的條件對臨時表中的記錄進行過濾。
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
根據上面那段話的解釋，where字句是在生成臨時表以后再進行過濾的，也就是可以理解為就是一個左連接：select * from A left join B on A.id = B.id;
其運行結果如下：
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對臨時表進行過濾，除掉A.type不為1的，顯然結果正確。
②
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
根據上面那段話的解釋：on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。顯然左連接再加上新的條件:B.class = 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
原因同①。（①，②）和（③，④）就是關鍵詞on and和on where的區別，但結果卻完全不同。綜上三個例子，where是生成臨時表以后再進行過濾，對左右表都進行篩選。而and后面的語句如果是對left join中的左表進行過濾將不起任何作用，對右表進行過濾的話，那么左表還是返回所有行，只是右表會被過濾掉一部分行。
再來看看內連接inner join on and和 on where的區別：
由于剛開始表的數據不是太適合，所以先稍微更新一下，這樣更好觀察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
先看看and的：
⑤
SQL> select * from A inner join B on A.id = B.id and A.type = 1; ID TYPE ID CLASS ---------- ---------- ---------- ---------- 2 1 2 2
顯然輸出結果與左連接的不一樣，先與沒有and的內連接比較一下：
SQL> select * from A inner join B on A.id = B.id; ID TYPE ID CLASS ---------- ---------- ---------- ---------- 1 2 1 1 2 1 2 2
顯然如果按左連接的邏輯，這個結果就是錯誤的。但這是Oracle輸出的，而不是我瞎打的，顯然在內連接時與左連接不同了。這里on and條件和on where條件一樣對生成以后的臨時表同樣會被過濾。顯然A表id為1的type不為1，所以它被過濾了。
再上幾組來驗證一下上面這個猜想是否是正確的：
⑥
再來看看內連接的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
對比發現：（⑤，⑥）和（⑦，⑧）結果都一樣，也就是說內連接inner join on and 或者on where不管是對左表還是右表進行過濾，實際都是在生成臨時表以后再進行過濾的，而且對左表和右表都起作用，這與左連接left join有本質的區別！！！
最后上倆沒有使用連接語句的例子：
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
比較簡單，不作解釋。
總結一下：
在使用left join時，on和where條件的區別如下：
1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。（實際上左連接中如果and語句是對左表進行過濾的，那么不管真假都不起任何作用。如果是對右表過濾的，那么左表所有記錄都返回，右表篩選以后再與左表連接返回）
2、where條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉，on后的條件用來生成左右表關聯的臨時表，where后的條件對臨時表中的記錄進行過濾。
在使用inner join時，不管是對左表還是右表進行篩選，on and和on where都會對生成的臨時表進行過濾。
不過我自己驗證時發現一個現象,想補充一下,and語句是對左表進行過濾時,如果篩選左表A.id=3時,因為右表B.id沒有等于3的所以會過濾掉右表的所有數據
浙公網安備 33010602011771號