outer join新舊語法分析

pwz1688發表於2014-01-07
語法分析
從Oracle9i開始，對於外連線(Outer join)Oracle支援SQL92標準：這個標準有很多新的連線語法。外連線的新語法有left outer join和rigth outer join以及full outer join，舊語法外連線使用+號，
另外，舊語法無法實現full outer join，只能用union all來代替。Oracle也強烈建議9i以及之後的版本使用新的外連線語法。
outer join外連線有基表和從表的概念，基表也就是參考表，此表的內容會在outer join中全部選中，然後基表根據join的條件到從表中選出從表記錄，如果滿足條件則按從表實際內容選出，否則沒有找到則從表對應行的所表列值為null。
就新語法而言，比如
左外連結（left outer  join)左邊的表是基表，右邊的表是從表，如a left join b則a為基表，b為從表；
右外連結（rigth outer join）右邊的表是基表，左邊的表是從表，如a rigth join b則a為從表，b為基表；
全連結（full outer join）比較特殊，左右邊表互為基表和從表，如a full join b則a,b互為基表和從表。
舊語法來說，在where條件中無(+)號的表示式中的列所屬表為基表，有(+)號的表示式中的列所屬表為從表，比如where a.id=b.id(+)則a為基表，b為從表。
下面測試下新舊語法外連線的差別。
表a,b,c初始化指令碼如下：

點選(此處)摺疊或開啟

  1. SQL> drop table a;

  3. 表已刪除。

  5. SQL> create table a as
  6.   2 select level id,'x'||level name
  7.   3 from dual connect by level<5
  8.   4 union all
  9.   5 select level,'y'||level
  10.   6 from dual connect by level<5;

  12. 表已建立。

  14. SQL> drop table b;

  16. 表已刪除。

  18. SQL> create table b as
  19.   2 select level id,'x'||level name
  20.   3 from dual connect by level<3;

  22. 表已建立。

  24. SQL> drop table c;
  25. drop table c
  26.            *
  27. 第 1 行出現錯誤:
  28. ORA-00942: 表或檢視不存在

  30. SQL> create table c as
  31.   2 select level id,'y'||level name
  32.   3 from dual connect by level<3;

  34. 表已建立。

  36. SQL> select * from a;

  38.         ID NAME
  39. ---------- -----------------------------------------

  41.          1 x1
  42.          2 x2
  43.          3 x3
  44.          4 x4
  45.          1 y1
  46.          2 y2
  47.          3 y3
  48.          4 y4

  50. 已選擇8行。

  52. SQL> select * from b;

  54.         ID NAME
  55. ---------- -----------------------------------------

  57.          1 x1
  58.          2 x2

  60. SQL> select * from c;

  62.         ID NAME
  63. ---------- -----------------------------------------

  65.          1 y1
  66.          2 y2
  67.                                                                               
外連線舊語法(+)如下：

點選(此處)摺疊或開啟

  1. SQL> show linesize;
  2. linesize 80
  3. SQL> set linesize 200;
  4. SQL> column name format a10
  5. SQL> select * from a,b
  6.   2 where a.id=b.id(+) and a.name like 'x%';

  8.         ID NAME ID NAME
  9. ---------- ---------- ---------- ----------

  11.          1 x1 1 x1
  12.          2 x2 2 x2
  13.          4 x4
  14.          3 x3

新語法left(rigth/full) outer join測試程式碼如下：

點選(此處)摺疊或開啟

  1. SQL> set null null
  2. SQL> select * from a left join b
  3.   2 on a.id=b.id and a.name like 'x%';

  5.         ID NAME ID NAME
  6. ---------- ---------- ---------- ----------

  8.          1 x1 1 x1
  9.          2 x2 2 x2
  10.          3 x3 null null
  11.          4 x4 null null
  12.          1 y1 null null
  13.          2 y2 null null
  14.          3 y3 null null
  15.          4 y4 null null

  17. 已選擇8行。

  19. SQL> select * from a left join b
  20.   2 on a.id=b.id
  21.   3 where a.name like 'x%';

  23.         ID NAME ID NAME
  24. ---------- ---------- ---------- ----------

  26.          1 x1 1 x1
  27.          2 x2 2 x2
  28.          4 x4 null null
  29.          3 x3 null null

  31. SQL> select * from a left join b
  32.   2 on a.name like 'x%'
  33.   3 where a.id=b.id;

  35.         ID NAME ID NAME
  36. ---------- ---------- ---------- ----------

  38.          1 x1 1 x1
  39.          2 x2 2 x2


從上面新語法測試程式碼第一個sql語句中可知，外連線中的on不需要過濾基表資料，過濾基表資料是在where裡做的，on只是連線條件，根據連線條件找匹配的從表資料，找不到匹配的從錶行，則置空。簡單來說on關鍵字，只是連線條件，它不能過濾基表。過濾不了的原因是on過濾掉的a表結果，最後又被left outer join拿了回來。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1068010/，如需轉載，請註明出處，否則將追究法律責任。

相關文章