連結： http://blog.itpub.net/28602568/viewspace-1280508/

前言：

一般開發人員都比較喜歡寫left join，那是否有些情況不用根據業務情況就能將left join 改成join 呢？

情景① A left join B on a.a=b.a where b.任意欄位 =(只要不是is null) 可將left 去掉;

理由：根據left join特性不匹配顯示空，而where是針對關聯後的結果進行篩選

例如（where b.a=1）那也就說明'is null'不匹配的過濾掉..既然不匹配的過濾掉那完全可以改成join了；

情景② A left join B on 1=1 where .. 可以將left 去掉;

理由：left join 和 join 都能實現 on 1=1 笛卡爾乘積

情景③ A left join B on (a.a=X or/and b.a=X ) where b.任意欄位 =(只要不是is null), 可將left 去掉;

理由：(a.a=X or/and b.a=X ) 篩選後不匹配的顯示為空.. 所以where 傳值(只要不是is null)就可以將left去掉；

註釋：
很多時候即使寫left join A where a.欄位 is not null 情況。CBO生成執行計劃時會自己判斷成join的執行計劃;
此部落格僅是對left join的where 和on 2部分做一些瞭解；

以上三種情況可以統稱：A left join B on ... where b.任意欄位 =(只要不是is null) 都可將left 去掉;

情景①

【on a.a = b.a 】部分

SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a =1; A B A B ---------- ---------- ---------- ---------- 1 2 1 2 SQL>	等同於	SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a =1; A B A B ---------- ---------- ---------- ---------- 1 2 1 2 SQL>
SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a is null; A B A B ---------- ---------- ---------- ---------- 2 1 SQL>	不等於	SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a is null; A B A B ---------- ---------- ---------- ---------- SQL>

情景②

【on 1 = 1 】部分

SQL> SELECT *
  2    FROM (SELECT '1' a, 2 b
  3            FROM dual
  4          union all
  5          SELECT '2' a, 1 b FROM dual) a
  6    left join (SELECT '1' a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT '' a, 3 b FROM dual) b
10      on (1 = 1)
11   where b.a =1;

A          B A          B
- ---------- - ----------
1          2 1          2
2          1 1          2
SQL>

等同於

SQL>  SELECT *
  2    FROM (SELECT '1' a, 2 b
  3            FROM dual
  4          union all
  5          SELECT '2' a, 1 b FROM dual) a
  6    /*left*/ join (SELECT '1' a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT '' a, 3 b FROM dual) b
10      on (1 = 1)
11   where b.a =1;

A          B A          B
- ---------- - ----------
1          2 1          2
2          1 1          2

SQL>

情景③

【on (a.a = 1 or a.b =2)】部分

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    left join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (b.a = 2 or b.b = 5)
11   where b.b is null;

         A          B          A          B
---------- ---------- ---------- ----------
         1          2
         2          1

SQL>

不等於

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    /*left*/ join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (b.a = 2 or b.b = 5)
11   where b.b is null;

         A          B          A          B
---------- ---------- ---------- ----------

SQL>

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    left join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (a.a = 2 or b.b = 5)
11   where b.b is not null;

         A          B          A          B
---------- ---------- ---------- ----------
         2          1          1          2
         2          1          1          3

SQL>

等同於

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    /*left*/ join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (a.a = 2 or b.b = 5)
11   where b.b is not null;

         A          B          A          B
---------- ---------- ---------- ----------
         2          1          1          2
         2          1          1          3

SQL>

【on (a.a = 1 and a.b =2)】部分

SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is null; A B A B ---------- ---------- ---------- ---------- 2 1 SQL>	不等於		SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is null; A B A B ---------- ---------- ---------- ---------- SQL>
SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is not null; A B A B ---------- ---------- ---------- ---------- 1 2 1 3 SQL>	等同於		SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is not null; A B A B ---------- ---------- ---------- ---------- 1 2 1 3 SQL>

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle -- left join 什麼情況可以直接改成join

相關文章