MySql Outer Join 簡單化

標籤：

   查詢from語句中的Outer Join可以在多種情況下被簡化；

   在解析階段，右外串連操作可以被轉變為等下ode值包含left join的操作，在一般情況下，轉變：

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

   所有inner join運算式形如（t1 inner join t2 on p(t1,t2)）可以被(t1,t2,p(t1,t2))代替（通過where condition 做兩表的conjunct或者嵌套串連條件 ）；

   當最佳化器評估帶有outer join操作的join 查詢執行計畫時，它只考慮這幾種情況，外表在內表之前被訪問；

   e.g.

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)  WHERE P(T1,T2) AND R(T2)

    where condition中的R(t2)減少了很多t2表中的匹配的行數，如果我們執行這條查詢，最佳化器會毫不猶豫的先訪問t1表，然後這將導致非常無效率的執行計畫；

    幸運的，如果where condition 為 null-rejected,Mysql 會把像這樣的查詢轉變為非outer join操作。對於一個outer join操作，對於任何null被填充的行，如果null-rejected的condition判斷為false或者unknown（即e.g. 對於left join，非left table,用Null值來填充行資料，當該條件可以判定該操作不成立）；

    e.g.

T1 LEFT JOIN T2 ON T1.A=T2.A

 為null-rejected的條件有：

T2.B IS NOT NULL,T2.B > 3,T2.C <= T1.C,T2.B < 2 OR T2.C > 1

為非null-rejected的條件：

T2.B IS NULL,T1.B < 3 OR T2.B IS NOT NULL,T1.B < 3 OR T2.B > 3

 對Outer join操作的null-rejected條件常規條件比較簡單：

   1： 如果它是A is not null形式，當A 是任何內部表的一個屬性；

   2：如果一個串連包含一個null-rejected條件作為串連條件；

   3：如果他是一個分裂的Null-rejected條件；

在一個outer join操作查詢中， 一個Null-rejected條件，可能對於別的表為not null-rejected的：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 LEFT JOIN T3 ON T3.B=T1.B  WHERE T3.C > 0

 where條件對於第二個outer join操作是一個null-rejected條件，但是對於第一個就不是了。

 如果where條件對於一個outer join操作是一個 null-rejected條件，那麼該outer join操作可以轉化為一個inner join操作；

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 INNER JOIN T3 ON T3.B=T1.B  WHERE T3.C > 0

對於原始的查詢，最佳化器會蘋果查詢計劃通過唯一的訪問順序t1,t2,t3,對於上述的替代查詢，他可能會考慮t3,t1,t2

一個outer join操作的轉變可能觸發另外一個的轉變：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 LEFT JOIN T3 ON T3.B=T2.B  WHERE T3.C > 0

first:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                 INNER JOIN T3 ON T3.B=T2.B  WHERE T3.C > 0

等效於：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3  WHERE T3.C > 0 AND T3.B=T2.B

現在剩餘的outer join操作可以被替換成一個inner join,因為條件t3.b= t2.b是一個null-rejected，所以我們得到一個沒有outer join的查詢

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3  WHERE T3.C > 0 AND T3.B=T2.B

有時我們可以替換一個嵌套的outer join操作，但不是不能轉變嵌套outer join;

SELECT * FROM T1 LEFT JOIN              (T2 LEFT JOIN T3 ON T3.B=T2.B)              ON T2.A=T1.A  WHERE T3.C > 0

替換為：

SELECT * FROM T1 LEFT JOIN              (T2 INNER JOIN T3 ON T3.B=T2.B)              ON T2.A=T1.A  WHERE T3.C > 0,

可以被重寫成依舊包含嵌套outer join操作：

SELECT * FROM T1 LEFT JOIN              (T2,T3)              ON (T2.A=T1.A AND T3.B=T2.B)  WHERE T3.C > 0.

當嘗試轉變一個嵌套outer join操作是，必須把嵌套的outer join的join condition和where 條件一起考慮：

SELECT * FROM T1 LEFT JOIN              (T2 LEFT JOIN T3 ON T3.B=T2.B)              ON T2.A=T1.A AND T3.C=T1.C  WHERE T3.D > 0 OR T1.D > 0

where條件不是嵌套outer join的一個null-rejected條件，但嵌套outer join的串連條件T2.A=T1.A AND T3.C=T1.C 是一個null-rejected條件，所以等效與：

SELECT * FROM T1 LEFT JOIN              (T2, T3)              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B  WHERE T3.D > 0 OR T1.D > 0

 

MySql Outer Join 簡單化