Oracle中left join中右表的限制條件

無過濾條件的LEFT JOIN

SQL中最簡單形式的LEFT JOIN，是直接根據關聯欄位，以左表為基準，對右表進行匹配。在SELECT語句中選取的欄位，如果有右表的記錄（一般都是需要右表的某些記錄的），取出配對成功的右表記錄中對應的這個欄位的值；否則，直接置NULL。這本身就是LEFT JOIN的特點：保證左表記錄完整，右表只是輔助匹配。

直接看例子，為了演示，準備了兩張測試表test1，test2：

select * from test1  

select * from test2  

select t2.*,t1.market_place_id from test2 t2   
left join test1 t1  
on t2.parent_asin=t1.asin  

對照上面例子解釋一下這個結果：以左表test2為基準，用右表test1的asin欄位和test2的parent_asin欄位進行匹配，取出test2的全部資料和test1的market_place_id欄位。對於test2中的第一條記錄，因為右表中有兩條記錄符合的asin='parent1'，只是market_place_id不同而已（分別為1、2）。於是這兩條記錄都會作為符合條件的記錄加入結果集。這時，雖然是以左表為基準，但是這條記錄卻在結果集中產生了兩條對應的記錄。這點要稍加註意：以左表為基準並不意味著結果集的記錄數量=左表的記錄數量！

再回過頭來看結果集的5條記錄，由ID欄位可以很好的區分出每條記錄是由左表的哪條記錄對應產生的。這裡，最後兩條記錄可以很好的體現出LEFT JOIN的特點。

右表有濾條件的LEFT JOIN

這裡，我們忽略左表有過濾條件場景的討論，因為在LEFT JOIN中左表作為基準表，對他的過濾直接反應在SQL的WHERE字句中，效果上也相當於單表SELECT的WHERE字句過濾，縮小左表範圍後，再和右表做JOIN，沒什麼懸念。

但是對於右表的過濾，通常有兩種主要的方式：在ON字句中加入過濾條件或者在LEFT JOIN之後的WHERE字句中加入過濾條件。對於這兩種方式的對比，下面主要針對邏輯語義和實現效能上加以對比。

過濾條件在ON字句中

select t2.*,t1.market_place_id from test2 t2   
left join test1 t1  
on t2.parent_asin=t1.asin and t1.market_place_id='2'  

上面這條SQL加上了對右表test1中market_place_id的過濾條件：只關心market_place_id為‘2’的右表記錄。查詢結果如下。

邏輯語義上，這個結果相當於右表test1首先進行了條件過濾，只剩下兩條記錄[(2,‘parent1’,‘2’),(3,‘parent2’,‘2’)]，然後左表test2和這個過濾之後的結果集進行無過濾條件的LEFT JOIN，於是得到了上圖的結果。

效能上，來看一下這條語句的執行計劃截圖

可以看出，T1確實先以2為標準對market_place_id做了一次過濾，然後，在外層，再做原來的LEFT JOIN。由此可以證實上面邏輯語義結果的展示，同時也可以發現，就本例而言，如果能夠在market_place_id上建立index，可以直接避免內層過濾對右表進行的全表掃描，從而提高整個SQL的執行效率。下圖為在market_place_id上建立index之後，同樣SQL語句的執行計劃：

這裡可以看出，原來的TABLE FULL SCAN 已經被換成了INDEX的RANGE SCAN，從而也直接導致了Oracle的優化器在最外層的Hash Join替換為了Nested Loops。（當然這個join的方式並不能說明什麼問題，因為畢竟測試用的資料集太小，完全有可能在大資料集的真實情況下，優化器根據統計資訊還是最終使用Hash Join演算法）

過濾條件在WHERE子句中

select t2.*,t1.market_place_id from test2 t2   
left join test1 t1  
on t2.parent_asin=t1.asin   
where t1.market_place_id='2'  

上面語句的執行結果如下：

邏輯語義上，所有的market_place_id1!='2'的記錄（包括NULL）全部被過濾掉了。
效能上，再來看一下這條語句的執行計劃：

由上面的執行計劃可以看出，Oracle也是首先對右表test1進行了market_place_id的過濾，但是過濾之後JOIN操作已經不是LEFT JOIN了，而是變成了普通的INNER JOIN。這就解釋了為什麼最後的結果集只有兩條記錄。
同樣思路，就本例而言，在右表test的market_place_id欄位上建立INDEX，同樣可以達到優化SQL的目的，以下是建立INDEX之後的SQL執行計劃：

結論：

在使用LEFT JOIN時，右表的限制條件，在ON和WHERE字句中出現，邏輯上的語義完全不同。
過濾條件在ON子句中出現時，不會改變原來LEFT JOIN的執行語義：以左表為基表。
過濾條件在WHERE字句中出現時，已經改變了原來LEFT JOIN的語義，相當於在最後LEFT JOIN的結果集裡面再做了一次WHERE條件的過濾，所以已經喪失的LEFT JOIN的原始語義。
效能上，其實兩者並沒有本質的區別，掃描路徑完全一致，只是對於後者，Oracle的內部實現，巧妙的將上面描述的語義轉換為了通過INNER JOIN實現。這樣就保證了在真正執行時還是首先進行內層過濾，縮小右表的資料集，然後進行外層INNER JOIN。
所以使用LEFT JOIN是，有需求對右表進行過濾時，要格外小心了。

備註：
以上測試使用Oracle 11g，更老版本的優化器的執行計劃可能會不同。但最終語義上不會有差別。