left/right join中on和where的區別
- 2020 年 3 月 18 日
- 筆記
開發同學提了個問題，如下兩種left join中on和where條件的寫法是否等價？
select * from j_a left join j_b on j_a.id=j_b.id where j_a.name='b'; select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b';
我們先看測試，創建兩張測試表，導入一些數據，
SQL> create table j_a(id number, name varchar2(1)); Table created. SQL> create table j_b(id number, name varchar2(1)); Table created. SQL> select * from j_a; ID N ---------- - 1 a 2 b 3 c SQL> select * from j_b; ID N ---------- - 2 d 3 e 5 o
為了比較，先看下join全連接，共有2條記錄，
SQL> select * from j_a join j_b on j_a.id=j_b.id; ID N ID N ---------- - ---------- - 2 b 2 d 3 c 3 e
使用left join，會顯示j_a表的3條記錄，其中j_a.id=1的記錄，對應j_b為空，
SQL> select * from j_a left join j_b on j_a.id=j_b.id; ID N ID N ---------- - ---------- - 2 b 2 d 3 c 3 e 1 a
使用on，得到3條記錄，
SQL> select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b'; ID N ID N ---------- - ---------- - 2 b 2 d 3 c 1 a
使用where，得到1條記錄，
SQL> select * from j_a left join j_b on j_a.id=j_b.id where j_a.name='b'; ID N ID N ---------- - ---------- - 2 b 2 d
從測試結論看，left join使用on和where得到的結果集是不相同的。
究其原因，是兩種關鍵字執行的時間點有所區別。
(1) on條件是在left join生成臨時表時執行的，因此無論on中的條件是否為真，都會返回左邊表中的所有記錄，所以上述測試中，得到3條記錄。
(2) where條件是在left join臨時表生成後，再對臨時表進行過濾，此時是沒有left join的含義了，條件不為真的就會被過濾，所以上述測試中，得到1條記錄。
因此，之所以on和where的測試結果不同，這和left join、right join的特性是有關的，因為on的條件無論是否為真，都會返回left或right表中的記錄。
當然，非得用這種寫法，使用is not null，還是能讓on和where得到相同的結果集，
select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b' and j_b.id is not null;
如果是join/full join，他是left join和right join的並集，所以使用on和where是相同的結果。
使用join和on，得到1條記錄，
SQL> select * from j_a join j_b on j_a.id=j_b.id and j_a.name='b'; ID N ID N ---------- - ---------- - 2 b 2 d
這是使用join和where，得到1條記錄，
SQL> select * from j_a join j_b on j_a.id=j_b.id where j_a.name='b'; ID N ID N ---------- - ---------- - 2 b 2 d
對待問題，從原理的理解，加上實際的測試，才可能讓你抓到問題的本質，才可能讓他成為你真正掌握的知識。不僅是Oracle，還是其他的技術，又或是任何其他的領域，都是如此。
