Outer Join 的測試

前言:

  Outer Join 就是想在A table查得的資料中，在B table即使沒有，也詳列出來的一種用法....
如果想使用full outer join = UNION of (left outer join + right outer join)

如果不清楚 也可以參考wiki 的說明 ( http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join )

1.Equijoin:

就是指一般的select from T1, T2 where T1.C1=T2.C2...

2.Inner Joins (效果同 Equijoin), 只取用兩個表格都有的資料做Join

3.Outer Join (+) , 允許資料not match

當使用left outer join時，(+)是加在等號右邊 ，(+) 為次要表格，可能沒有資料之那一方

from T1 left join T2 on T1.C1=T2.C1 lfet join T3 on (T1.C2=T3.C2 and T1.C3=T3.C3)...

等價於

from T1,T2,T3 where T1.C1=T2.C1(+) and T1.C2=T3.C2(+) and T1.C3=T3.C3(+)...

--

CREATE TABLE departments

(

 depID  NUMBER(38,0),

 depName VARCHAR2(20),

 delFlag NUMBER(1,0)

);

CREATE TABLE employees

(

 empID  NUMBER(38,0),

 empName VARCHAR2(20),

 depID  NUMBER(38,0),

 delFlag NUMBER(1,0)

);

INSERT INTO departments VALUES(1,'Finacle',0);

INSERT INTO departments VALUES(2,'Marketing',0);

INSERT INTO departments VALUES(3,'HR',1);

INSERT INTO departments VALUES(4,'IT',0);

INSERT INTO employees VALUES(1,'jay',1,0);

INSERT INTO employees VALUES(2,'peter',2,0);

INSERT INTO employees VALUES(3,'alan',1,0);

INSERT INTO employees VALUES(4,'roger',2,0);

INSERT INTO employees VALUES(5,'jason',3,0);

COMMIT;

--

--列出部門ID為3的部門和員工資訊，不管該部門是否有員工

SELECT d.depID,d.depName,e.empName

 FROM departments d

 LEFT OUTER JOIN employees e

   ON d.depID = e.depID

 WHERE d.depID =3

 ORDER BY d.depID;

--和上面等價

SELECT d.depID,d.depName,e.empName

 FROM departments d, employees e

 WHERE d.depID = e.depID(+)

  AND d.depID =3

 ORDER BY d.depID;

--查詢部門四是否有員工 , employee可能無資料

SELECT d.depID,d.depName,e.empName

 FROM departments d, employees e

 WHERE d.depID = e.depID(+)

  AND d.depID =4

 ORDER BY d.depID;