外連線的一些總結

[@more@]

外連線的一些總結

外連線擴充套件簡單連線的結果，一個外連線返回的值不僅是所有滿足連線條件的行，同時還有為NULL值的行。

建立試驗表A、B

SQL> select * from a;

A1 A2

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

1 1

2

3 3

SQL> select * from b;

B1 B2

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

1 1

2 2

SQL> select * from a,b where a2=b2;

A1 A2 B1 B2

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

1 1 1 1

SQL> select * from c;

C1 C2

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

1 1

2 4

3

1、對於查詢一個外連線AB兩個表的話，並且返回A表上所有的行，這是一個左連線（left outer join），在FROM上使用LEFT JOIN,或者在where語句的B表的列上使用（+）。對於A上有一些行並不和表B匹配，Oracle會返回空值。

SQL> select * from a,b where a2=b2(+);

A1 A2 B1 B2

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

1 1 1 1

2

3 3

SQL> select * from a left outer join b on a2=b2;

A1 A2 B1 B2

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

1 1 1 1

2

3 3

2、對於查詢一個外連線AB兩個表的話，並且返回B表上所有的行，這是一個右連線（right outer join），在FROM上使用RIGHT JOIN,或者在where語句的A表的列上使用（+）。對於B上有一些行並不和表A匹配，Oracle會返回空值。

SQL> select * from a,b where a2(+)=b2;

A1 A2 B1 B2

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

1 1 1 1

2 2

SQL> select * from a right outer join b on a2=b2;

A1 A2 B1 B2

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

1 1 1 1

2 2

3、對於查詢一個外連線AB兩個表的話，並且返回A和B表上所有的行，這是一個全外連線（full outer join），在FROM上使用full JOIN。

SQL> select * from a full outer join b on a2=b2;

A1 A2 B1 B2

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

1 1 1 1

2

3 3

2 2

4、在查詢語句的FROM子句中有連線語法的時候是不能指定(+)操作的。

5、(+)只能用在WHERE子句中。

6、如果A、B是一種多連線狀態的連線，需要在所有的條件上使用(+)。

7、外連線不能對自身進行。

SQL> select * from a where a2=a2(+);

select * from a where a2=a2(+)

*

第 1 行出現錯誤:

ORA-01416: 兩表無法彼此外部連線

但是可以這樣使用：

SQL> select * from a t1,a t2 where t1.a2=t2.a2(+);

A1 A2 A1 A2

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

1 1 1 1

3 3 3 3

2

8、(+)操作只能用於列，不能適用於表示式。

9、一個條件包含(+)操作不能使用OR邏輯操作與其他條件聯合使用。

10、條件不能用IN來比較一個帶有操作的列。

11、不能在其子查詢上比較任何帶有(+)操作的列。

外連線是在SQL最佳化上難點之一，很多程式由於外連線的使用，會造成很多效能問題，這裡簡要總結以下幾個存在的外連線使用不對的地方。

1、外連線的使用不當的地方

SQL> select * from a,b,c where a2(+)=b2 and a2=c2;

A1 A2 B1 B2 C1 C2

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

1 1 1 1 1 1

SQL> select * from a,b,c where a2(+)=b2 and b2=c2;

A1 A2 B1 B2 C1 C2

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

1 1 1 1 1 1

在這兩個查詢來看都有一個共同的特點，由於ab表之間有外連線的關係，但是ab表和其他表之間還是有限制條件，這樣外連線就沒有意義了，但是由於外連線的存在，最佳化器就會被限制，很多查詢計劃就會因為這個外連線的存在而不被最佳化器考慮。所以在這種情況下，應該將這個外連線刪除。

2、遞推的關係

SQL> select * from a,b,c where a2(+)=b2 and b2(+)=c2;

A1 A2 B1 B2 C1 C2

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

1 1 1 1 1 1

2 4

3

Ab表之間有外連線，bc之間有外連線，既A要求B表的所有的行，而B要求C表的所有的行，最終的結果只是得到C表的所有的行。

SQL> select * from a,b,c where a2(+)=b2 and b2=c2(+);

A1 A2 B1 B2 C1 C2

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

1 1 1 1 1 1

2 2

Ab表之間有外連線，bc之間有外連線，既A要求B表的所有的行，而C要求B表的所有的行，最終的結果只是得到B表的所有的行

總結以上兩條，可以看到外連線是有遞推的關係的，不能相互衝突。

SQL> select * from a,b,c where a2(+)=b2 and a2(+)=c2;

select * from a,b,c where a2(+)=b2 and a2(+)=c2

*

第 1 行出現錯誤:

ORA-01417: 表可以外部連線到至多一個其它的表

這裡就是Ab表之間有外連線，ac之間也有外連線，就會出現問題

同時如果兩個有外連線的表，無論其中哪一個和其他表有等值的限制條件的產生的時候，就會出現外連線無用的情況，但是這樣就會造成效能問題。

外連線的改寫

對於一個查詢：

Select …,outer_joined_to_table.column

From some_table. outer_joined_to_table

Where …= outer_joined_to_table(+)

可以改寫為：

Select …,(select column from outer_joined_to_table where …) from some_table;

外連線的一些總結

外連線的一些總結

相關文章