Oracle 外連接和 （+）號的用法??

轉自：http://www.cnblogs.com/iloveu/archive/2009/08/04/1538781.html
對于外連接，Oracle中可以使用“(+)”來表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面將配合實例一一介紹。
1. LEFT OUTER JOIN：左外關聯 SELECT?e.last_name,?e.department_id,?d.department_name?
FROM?employees?e?
LEFT?OUTER?JOIN?departments?d?
ON?(e.department_id?=?d.department_id);?
等價于 SELECT?e.last_name,?e.department_id,?d.department_name?
FROM?employees?e,?departments?d?
WHERE?e.department_id=d.department_id(+);?
結果為：所有員工及對應部門的記錄，包括沒有對應部門編號department_id的員工記錄。

2. RIGHT OUTER JOIN：右外關聯 SELECT?e.last_name,?e.department_id,?d.department_name?
FROM?employees?e?
RIGHT?OUTER?JOIN?departments?d?
ON?(e.department_id?=?d.department_id);?
等價于 SELECT?e.last_name,?e.department_id,?d.department_name?
FROM?employees?e,?departments?d?
WHERE?e.department_id(+)=d.department_id;?
結果為：所有員工及對應部門的記錄，包括沒有任何員工的部門記錄。

3. FULL OUTER JOIN：全外關聯 SELECT?e.last_name,?e.department_id,?d.department_name?
FROM?employees?e?
FULL?OUTER?JOIN?departments?d?
ON?(e.department_id?=?d.department_id);? 結果為：所有員工及對應部門的記錄，包括沒有對應部門編號 department_id的員工記錄和沒有任何員工的部門記錄。

外連接:
除了顯示匹配相等連接條件的數據外,還可以顯示某一個表中無法匹配相等連接條件的記錄!
------------------------------------------------
1) 左條件(+) = 右條件
左條件所在的表必須嚴格進行相等連接條件的匹配,而右條件所在的表除了匹配相等連接條件外，還可以顯示無法匹配連接條件的數據!
也稱為右外連接.
--------------------------------
可以用下列語句取代:
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 條件;
2) 左條件 = 右條件(+)
右條件所在的表必須嚴格進行相等連接條件的匹配,而左條件所在的表除了匹配相等連接條件外，還可以顯示無法匹配連接條件的數據!
也稱為左外連接.
--------------------------------
可以用下列語句取代:
SELECT...FROM 表1 LEFT OUTER JOIN 表2 ON 條件;

REM 除了相等連接之外,顯示沒有員工的部門信息.
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;
SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
REM 除了相等連接之外,顯示沒有部門的員工信息.
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);
SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

Oracle 建議你用在From語句后用Outer Join語法 而不是Oracle的Join操作符(+).而且(+)是要受下面的規則限制的，但Outer Join語法則不受的～～

1）你不可以在查詢塊中使用(+) 當它同時包含 join的from語句中
2）(+)只是在where語句中,并且只能對應一個表或視圖的一行字段
3）如果A和B做聯接時有多個條件，那么(+)必須完善所有的匹配條件，
如果沒有 ,oracle不會警告你～只是結果自然不同的
4）不可以在作出一個表外查詢 另張表內查詢的(+)聯接操作～～
5）不可以用（+）外聯接到自己 當然Self Join是可以的
6）含（+）的Where后的注意
OR不可用
IN不可用
子查詢不可用

以下給些個例子：

SQL code

SQL> desc part

?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- -----------------
?PART_ID?????????????????????????????????? NOT NULL VARCHAR2(4)
?SUPPLIER_ID??????????????????????????????????????? VARCHAR2(4)

SQL> select * from part;

PART SUPP
---- ----
P1?? S1
P2?? S2
P3
P4

SQL> desc supplier

?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- -----------------
?SUPPLIER_ID?????????????????????????????? NOT NULL VARCHAR2(4)
?SUPPLIER_NAME???????????????????????????? NOT NULL VARCHAR2(20)

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1?? Supplier#1
S2?? Supplier#2
S3?? Supplier#3

SQL> select p.part_id, s.supplier_name
? 2? from part p, supplier s
? 3? where p.supplier_id = s.supplier_id (+);

PART SUPPLIER_NAME
---- --------------------
P1?? Supplier#1
P2?? Supplier#2
P3
P4

--(+)是單向的

SQL> select p.part_id, s.supplier_name
? 2? from part p, supplier s
? 3? where p.supplier_id (+) = s.supplier_id (+);
where p.supplier_id (+) = s.supplier_id (+)
??????????????????????? *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table

--實現Full Join的方法

SQL> select p.part_id, s.supplier_name
? 2? from part p, supplier s
? 3? where p.supplier_id = s.supplier_id (+)
? 4? union
? 5? select p.part_id, s.supplier_name
? 6? from part p, supplier s
? 7? where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1?? Supplier#1
P2?? Supplier#2
P3
P4
???? Supplier#3

--現在的語法
SQL> select p.part_id, s.supplier_name
? 2? from part p full outer join supplier s
? 3? on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1?? Supplier#1
P2?? Supplier#2
P4
P3
???? Supplier#3

另外的EG：

SQL code
SQL> select * from testa;

??????? ID NAME? ADDR???????? AGE
---------- ------ ------ ----------
???????? 3 電子?? 南京?????????? 23
???????? 5 物理?? 北京?????????? 23
???????? 6 物理?? 河北?????????? 25
???????? 1 電腦?? 上海?????????? 23
???????? 2 電子?? 北京?????????? 22
???????? 4 物理?? 蕪湖?????????? 22
???????? 7 藝術?? 上海?????????? 21
???????? 8 歌劇?? 蘇州?????????? 16
???????? 9 沈亮?? 上海?????????? 22

已選擇9行。

SQL> select * from testb;

??????? ID ADDR
---------- ----------
??????? 7 上海
???????? 1 北京
???????? 3 上海
???????? 5 合肥
--匹配完全
select testa.id,testa.addr from testa,testb where testa.id(+)=testb.id and
testa.addr(+)=testb.addr;

??????? ID ADDR
---------- ------
???????? 7 上海

總結

以上是生活随笔為你收集整理的Oracle 外连接和 （+）号的用法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。