SQL中的左外連接和+號(hào)的用法

Oracle 左連接、右連接、全外連接、（+）號(hào)作用

Oracle  外連接

（1）左外連接 (左邊的表不加限制)
       （2）右外連接(右邊的表不加限制)
       （3）全外連接(左右兩表都不加限制)

外連接(Outer Join)

outer join則會(huì)返回每個(gè)滿(mǎn)足第一個(gè)（頂端）輸入與第二個(gè)（底端）輸入的聯(lián)接的行。它還返回任何在第二個(gè)輸入中沒(méi)有匹配行的第一個(gè)輸入中的行。外連接分為三種： 左外連接，右外連接，全外連接。 對(duì)應(yīng)SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我們省略outer 這個(gè)關(guān)鍵字。 寫(xiě)成：LEFT/RIGHT/FULL JOIN。

在左外連接和右外連接時(shí)都會(huì)以一張表為基表，該表的內(nèi)容會(huì)全部顯示，然后加上兩張表匹配的內(nèi)容。 如果基表的數(shù)據(jù)在另一張表沒(méi)有記錄。 那么在相關(guān)聯(lián)的結(jié)果集行中列顯示為空值（NULL）。

對(duì)于外連接， 也可以使用“(+) ”來(lái)表示。 關(guān)于使用（+）的一些注意事項(xiàng)：
       1.（+）操作符只能出現(xiàn)在where子句中，并且不能與outer join語(yǔ)法同時(shí)使用。
       2. 當(dāng)使用（+）操作符執(zhí)行外連接時(shí)，如果在where子句中包含有多個(gè)條件，則必須在所有條件中都包含（+）操作符
       3.（+）操作符只適用于列，而不能用在表達(dá)式上。
       4.（+）操作符不能與or和in操作符一起使用。
       5.（+）操作符只能用于實(shí)現(xiàn)左外連接和右外連接，而不能用于實(shí)現(xiàn)完全外連接。
在做實(shí)驗(yàn)之前，我們先將dave表和bl里加一些不同的數(shù)據(jù)。 以方便測(cè)試。

SQL> select * from bl;

ID NAME

---------- ----------

1 dave

2 bl

3 big bird

4 exc

9 懷寧

SQL> select * from dave;

ID NAME

---------- ----------

8 安慶

1 dave

2 bl

1 bl

2 dave

3 dba

4 sf-express

5 dmm

2.1 左外連接（Left outer join/ left join）

left join是以左表的記錄為基礎(chǔ)的,示例中Dave可以看成左表,BL可以看成右表,它的結(jié)果集是Dave表中的數(shù)據(jù)，在加上Dave表和BL表匹配的數(shù)據(jù)。換句話(huà)說(shuō),左表(Dave)的記錄將會(huì)全部表示出來(lái),而右表(BL)只會(huì)顯示符合搜索條件的記錄。BL表記錄不足的地方均為NULL.

示例：

SQL> select * from dave a left join bl b on a.id = b.id;

ID NAME               ID NAME

--------- ---------- ---------- ----------

1 bl                  1 dave

1 dave                1 dave

2 dave                2 bl

2 bl                  2 bl

3 dba                 3 big bird

4 sf-express          4 exc

5 dmm                             -- 此處B表為null，因?yàn)闆](méi)有匹配到

8 安慶                             -- 此處B表為null，因?yàn)闆](méi)有匹配到

SQL> select * from dave a left outer join bl b on a.id = b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

1 bl                  1 dave

1 dave                1 dave

2 dave                2 bl

2 bl                  2 bl

3 dba                 3 big bird

4 sf-express          4 exc

5 dmm

8 安慶

用（+）來(lái)實(shí)現(xiàn)， 這個(gè)+號(hào)可以這樣來(lái)理解： + 表示補(bǔ)充，即哪個(gè)表有加號(hào)，這個(gè)表就是匹配表。所以加號(hào)寫(xiě)在右表，左表就是全部顯示，故是左連接。

SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意： 用（+） 就要用關(guān)鍵字where

ID NAME               ID NAME

---------- ---------- ---------- ----------

1 bl                  1 dave

1 dave                1 dave

2 dave                2 bl

2 bl                  2 bl

3 dba                 3 big bird

4 sf-express          4 exc

5 dmm

8 安慶

2.2 右外連接（right outer join/ right join）

和left join的結(jié)果剛好相反,是以右表(BL)為基礎(chǔ)的, 顯示BL表的所以記錄，在加上Dave和BL 匹配的結(jié)果。 Dave表不足的地方用NULL填充.

示例：

SQL> select * from dave a right join bl b on a.id = b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

1 dave                1 dave

2 bl                  2 bl

1 bl                  1 dave

2 dave                2 bl

3 dba                 3 big bird

4 sf-express          4 exc

9 懷寧    --此處左表不足用Null 填充

已選擇7行。

SQL> select * from dave a right outer join bl b on a.id = b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

1 dave                1 dave

2 bl                  2 bl

1 bl                  1 dave

2 dave                2 bl

3 dba                 3 big bird

4 sf-express          4 exc

9 懷寧  --此處左表不足用Null 填充

已選擇7行。

用（+）來(lái)實(shí)現(xiàn)， 這個(gè)+號(hào)可以這樣來(lái)理解： + 表示補(bǔ)充，即哪個(gè)表有加號(hào)，這個(gè)表就是匹配表。所以加號(hào)寫(xiě)在左表，右表就是全部顯示，故是右連接。

SQL> Select * from dave a,bl b where a.id(+)=b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

1 dave                1 dave

2 bl                  2 bl

1 bl                  1 dave

2 dave                2 bl

3 dba                 3 big bird

4 sf-express          4 exc

9 懷寧

2.3 全外連接（full outer join/ full join）

左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null 填充。 全外連接不支持（+）這種寫(xiě)法。

示例：

SQL> select * from dave a full join bl b on a.id = b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

8 安慶

1 dave                1 dave

2 bl                  2 bl

1 bl                  1 dave

2 dave                2 bl

3 dba                 3 big bird

4 sf-express          4 exc

5 dmm

9 懷寧

已選擇9行。

SQL> select * from dave a full outer join bl b on a.id = b.id;

ID NAME               ID NAME

---------- ---------- ---------- ----------

8 安慶

1 dave                1 dave

2 bl                  2 bl

1 bl                  1 dave

2 dave                2 bl

3 dba                 3 big bird

4 sf-express          4 exc

5 dmm     

我一直以來(lái)也是認(rèn)為這兩種寫(xiě)法是一樣的，沒(méi)想到樓主特意去看了它們的執(zhí)行計(jì)劃，而且發(fā)現(xiàn)了它們的不同，這使得我比較驚訝。
按照書(shū)上的講法，這兩種寫(xiě)法是沒(méi)有什么區(qū)別的，后一種寫(xiě)法只不過(guò)是前一種寫(xiě)法的新版本。
為什么兩者的執(zhí)行計(jì)劃會(huì)不一樣呢？
我仔細(xì)看了一下兩者的執(zhí)行計(jì)劃，發(fā)現(xiàn)了為什么后一種要兩個(gè)表都全表 掃描，而前一個(gè)表有一個(gè)索引掃描。
原來(lái)前者選擇的優(yōu)化器是RULE，而后者選擇的優(yōu)化器是CBO的ALL ROWS。
不過(guò)，似乎要后者的效率高。

1. SELECT /*+RULE*/ * FROM T1,T2 WHERE T1.F1=T2.F2(+)

2. SELECT /*+RULE*/ * FROM T1 LEFT JOIN T2 ON T1.F1=T2.F2

這樣再看下執(zhí)行計(jì)劃吧