Oracle
奇特的 Oracle 外連接語法案例
我在應該從 Oracle 外連接語法移植到 SQL 標準外連接語法的查詢中看到以下內容:
SELECT ... FROM A, B, C, D, E WHERE A.A_ID = B.A_ID AND B.B_ID = C.A_ID(+) AND B.B_KEY = C.B_KEY(+) AND C.C_ID = D.C_ID(+) AND B.A_ID = E.A_ID(+) AND B.B_KEY = E.B_KEY(+) AND 'CONSTANT' = C.X_ID(+)
現在翻譯外連接語法通常是一個相當機械的過程,但最後一行讓我感到困惑。這是什麼意思?它有什麼作用?
我試圖執行機械過程。我希望我沒記錯。
這導致:
SELECT ... FROM A join B on A.A_ID = B.A_ID left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID left join D on C.C_ID = D.C_ID left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY
簡而言之,我認為 Leigh Riffel 的回答是正確的。
筆記
在過去,要記住的規則是:oracle where Aa = Bb (+) become Aa *= Bb in SQL-Server old syntax the plus 轉到相反的一側並變成一個星號,這意味著 A left join B on Aa = Bb
該行要求 c.X_ID 等於常量值或 C 表中沒有記錄。當然,由於它是左連接的,它不會限制 A 表中的記錄,只限制 C 表中加入的記錄。這是一個展示:
設置:
CREATE TABLE T1 as (select rownum+1 t1_id from dual connect by rownum <= 4); CREATE TABLE T2 as ( select rownum t1_id, DECODE(rownum,2,'CONSTANT',3,'NoMatch') CompareField from dual connect by rownum <= 3 ); SELECT * FROM T1; SELECT * FROM T2;
結果:
SELECT T1.t1_id, T2.t1_id, T2.CompareField FROM T1, T2 WHERE T1.t1_id = T2.t1_id(+) AND 'CONSTANT' = T2.CompareField(+) ORDER BY 1;
要麼:
SELECT T1.t1_id, T2.t1_id, T2.CompareField FROM T1 LEFT JOIN T2 ON T1.t1_id = T2.t1_id AND 'CONSTANT' = T2.CompareField ORDER BY 1;