oracle左外串連，右外串連，全外串連–執行個體講解

 看了網上一些例子，自己改了改，並做了實驗，希望能把這個東西說明白。

先建立表，並插入資料：

-- 部門表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));delete from employees delete from departments  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(001,'wbq',1,0);INSERT INTO employees VALUES(002,'czh',2,0);INSERT INTO employees VALUES(003,'chh',1,0);INSERT INTO employees VALUES(004,'wal',2,0);INSERT INTO employees VALUES(005,'ddd',3,0);INSERT INTO employees VALUES(006,'ddd',5,0);INSERT INTO employees VALUES(007,'ddd',6,0);COMMIT;

 

看看左外串連：

--列出部門ID為3的部門和員工資訊，不管該部門是否有員工/*如果員工表作為做外連結的左邊，那麼所有資料必須是員工表裡存在的資料，即先到員工表查資料，拿到這些資料再到右邊的部門表一條一條尋找，有就顯示，沒有就null*/-- 測試1SELECT d.depID,d.depName,e.empid,e.empName FROM departments d LEFT OUTER JOIN employees e   ON d.depID = e.depID ORDER BY d.depID;-- 測試2--和上面等效SELECT d.depID,d.depName,e.empid,e.empName FROM departments d, employees e WHERE d.depID = e.depID(+) ORDER BY d.depID;-------------------------------------------------

資料結果集如下：

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	1	wbq
1	Finacle	3	chh
2	Marketing	4	wal
2	Marketing	2	czh
3	HR	5	ddd
4	IT

 

看看右外串連：

-------------------------------------------------/*如果部門表作為做外連結的左邊，那麼所有資料必須是部門表裡存在的資料，即先到部門表查資料，拿到這些資料再到右邊的員工表一條一條尋找，有就顯示，沒有就null*/-- 測試3  SELECT d.depID,d.depName,e.empid,e.empName FROM employees e  LEFT OUTER JOIN departments d    ON  e.depID=d.depID  ORDER BY d.depID;  --和上面等效 -- 測試4 SELECT d.depID,d.depName,e.empid,e.empName FROM employees e,departments d    WHERE  e.depID=d.depID(+)  ORDER BY d.depID; /* 總結：左外串連就是把左邊的資料作為查詢條件， 一條一條去右邊查詢，如果有就顯示，沒有就為null。*/

 

結果集：

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	3	chh
1	Finacle	1	wbq
2	Marketing	4	wal
2	Marketing	2	czh
3	HR	5	ddd
		6	ddd
		7	ddd

 

-------------------------------------------------------------/*如果你學會了做外連結，你就會右外串連，就是把兩個表的順序換一換而已，所以沒必要練習右外連結，練習了反而會覺得亂，想用的話，把順序放對用左外串連就行了。以下是右外連結測試*/-- 測試1 的左外串連SELECT d.depID,d.depName,e.empid,e.empName FROM departments d LEFT OUTER JOIN employees e   ON d.depID = e.depID ORDER BY d.depID;

 

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	1	wbq
1	Finacle	3	chh
2	Marketing	4	wal
2	Marketing	2	czh
3	HR	5	ddd
4	IT

 

 

-- 測試1 的右外串連SELECT d.depID,d.depName,e.empid,e.empName FROM departments d RIGHT OUTER JOIN employees e   ON d.depID = e.depID ORDER BY d.depID;

 

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	3	chh
1	Finacle	1	wbq
2	Marketing	4	wal
2	Marketing	2	czh
3	HR	5	ddd
		6	ddd
		7	ddd

 

 

-- 測試1 的右外串連 等價於下面的sql(注意下面是左外串連，可見左外串連和右外串連可以轉化)SELECT d.depID,d.depName,e.empid,e.empName FROM employees e LEFT OUTER JOIN departments d   ON d.depID = e.depID ORDER BY d.depID;

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	3	chh
1	Finacle	1	wbq
2	Marketing	4	wal
2	Marketing	2	czh
3	HR	5	ddd
		6	ddd
		7	ddd

 

 --如果使用（+），那（+）在哪邊，那邊就是被串連的表， --它的資料是可選的(有就顯示，沒有就null，條件是沒有（+）的表每一條作為 --查詢條件）

 

 

 --FULL OUTER JOIN：全外關聯  SELECT d.depID,d.depName,e.empid,e.empName FROM departments d FULL OUTER JOIN employees e   ON d.depID = e.depID ORDER BY d.depID;

 

DEPID	DEPNAME	EMPID	EMPNAME
1	Finacle	3	chh
1	Finacle	1	wbq
2	Marketing	2	czh
2	Marketing	4	wal
3	HR	5	ddd
4	IT
		6	ddd
		7	ddd

 

看看結果集就很清楚了