關聯查詢


6-1 關聯查詢	6-2 子查詢
6-3 集合查詢	6-4 分組查詢

關聯式資料庫管理系統允許使用者在不同關聯(Table) 表格間定義各種關聯關係。這種內部關聯將獨立的表格(Table) 關聯成一個結果。若被連結之表格的欄位間沒有任何關係，則系統會利用“卡笛生乘積”(Cartesian Product) 將兩個表格合成一個表格。“卡笛生乘積”實際上就是一種無條件的關聯，這種操作往往會產生大量的資料列，其結果並沒有太大的意義。因此在相關的關聯操作時，往往會加上限制條件，再進行關聯運算。

兩個表格的“卡笛生乘積”形式如下：

SELECT <欄位名11> , <欄位名12> ,… ,<欄位名1m> ,
<欄位名21> , <欄位名22> ,…, <欄位名2n>,
From <表格名1> , <表格名2 >

其中：<欄位名11> , <欄位名12> , … 與 <欄位名1m> 是 <表格名1> 中的欄位，而 <欄位名21> , <欄位名22> , … 與 <欄位名2n> 是 <表格名2> 中的欄位。
　

	卡笛生乘積的例子。(假設有兩個表格 EMP1 與 DEPT1)。

	以上兩個表格，利用卡笛生乘積可得以下 6 列 (2x3) 的結果：

	關聯運算就是利用各表格的相關欄位之間的比較關係，關聯成一個新的結果。根據比較關係的不同，關聯運算可分成：等值關聯和非等值關聯；此外依據關聯對象不同又有兩種特殊的關聯：自身關聯和外部關聯。

SELECT…
FROM <表格名1> , <表格名2>
WHERE <表格名1 . 欄位名1> = <表格名2 . 欄位名2>;

等值關聯要求參與關聯運算的兩個表格在共同欄位上具有相同的值。SELECT 指令可以將兩個表格進行關聯操作。關聯時，將兩個表格中的所有記錄在關聯欄位上進行比較，檢查它們是否滿足條件。其中：給欄位名加上表格名，是為了避免被關聯表中各個被關聯欄位同名時產生資料欄的定義不明確(Ambiguity)。

我們以 ORACLE 所提供的學習帳號中之樣本表格 EMP 與 DEPT 為例說明：

P.K.			F.K.	P.K.
Empno	Ename	…	Deptno	Deptno	Dname	Loc
7369	SMITH	…	20	10	ACCOUNTING	NEW YORK
7499	ALLEN	…	10	20	RESEARCH	DALLAS
：	：	：	：	30	SALES	CHICAGO
7566	JONES	…	10	40	OPERATIONS	BOSTON
R1(員工基本資料表格 EMP)				R2(部門基本資料表格 DEPT)

例子中表格 EMP 的外來鍵 Deptno 與表格 Dept 表格中的主鍵 Deptno 關聯。

	我們將章節 6-1-1 的例 1 的兩個表格中加上等值關聯。

	查詢薪資(SAL) 大於 2000 的員工，並列出其員工代碼(EMPNO)，員工姓名(ENAME)，員工所在的部門名稱(DNAME)，部門所在地(LOC)。

	例子中 SELECT 子句中的 DEPTNO 欄位加了表名 EMP.，這是為了防止不同基本表格的同名欄位產生資料欄的定義不明確(column ambiguously defined)，因為 DEPT 表中也有 DEPTNO 欄位。

非等值關聯就是指關聯條件中不使用“ = ”運算子的關聯運算。非等值關聯能夠使用的比較運算子包括 != 、 < 、 > 、<= 、>= 、 BETWEEN… AND 和 LIKE 等。
　

	查詢薪資等級(GRADE) 為 2 的員工，並列出其員工代碼(EMPNO)，員工姓名(ENAME)，薪資(SAL)。

	例子中做為連結條件的比較運算子不是“ = ”，而是 BETWEEN…AND，所以稱為非等值關聯。

關聯操作不僅可以作用在幾個不同的表格上，而且同一個表格可以進行自身關聯，將同一個表的不同列連結起來。自身關聯可以把一個表格看成兩個完全相同的表格 (副本)，然後再對這兩個表格在相關欄位上進行關聯，其關聯方式與多表格關聯完全相同。

表格自身關聯時，需要不同的表格別名來對同一個表格的兩個副本加以區分。表格自身關聯的一般命令格式如下：

SELECT…
FORM <表格名> <表格別名1> , <表格名> <表格別名2>
WHERE <表格別名1.欄位名1> = <表格別名2.欄位名2>;

其中：<欄位名1> 和 <欄位名2> 標記自關聯表中的兩個不同欄位，這兩個欄位要求有相同的資料型態和寬度。其中“WHERE <表格別名1.欄位名1> = <表格別名2.欄位名2>”為自身關聯條件。我們以 ORACLE 所提供的學習帳號中之樣本表格 EMP 為例說明：

P.K.		F.K.
Empno	Ename	Mgr	Sal	Deptno
7369	SMITH	7499	800	20
7499	FORD	7566	1800	10
7521	WARD	7698	1450	30
：	：	：	：	：
7566	JONES	7839	2975	10
7839	KING	-	3975	10

員工基本資料表格 EMP

例子中表格 Emp 中的外來鍵 Mgr 與同一表格中的主鍵 Empno 關聯。我們可以看成有兩個完全相同的表格 (副本) W 與 M 做關聯，其關聯的方法與一般關聯相同。

		F.K.			P.K.
Empno	Ename	Mgr	Sal	Deptno	Empno	Ename	Mgr	Sal	Deptno
7369	SMITH	7499	800	20	7369	SMITH	7499	800	20
7499	FORD	7566	1800	10	7499	FORD	7566	1800	10
7521	WARD	7698	1450	30	7521	WARD	7698	1450	30
：	：	：	：	：	：	：	：	：	：
7566	JONES	7839	2975	10	7566	JONES	7839	2975	10
7839	KING	-	3975	10	7839	KING	-	3975	10
EMP W (員工基本資料表格 EMP副本一)					EMP M (員工基本資料表格 EMP副本二)

	查詢每個員工的員工姓名與其主管姓名。

	例子中利用 W.MGR = M.EMPNO 做為關聯。所以在關聯查詢中的 W 的部份可以視為員工資料，而M的部份可以視為其主管資料。例如：W.EMPNO 代表員工的代號，W.ENAME 代表員工的名字，M.EMPNO 代表該名員工的主管之員工代號，M.ENAME 代表該名員工的主管名字等等。
	查詢所有員工中，其薪資(SAL) 大於主管的薪資(SAL)，並列出其員工姓名、員工薪資、主管姓名、主管薪資。

	例子中利用 W.MGR = M.EMPNO 做為關聯，所以在關聯查詢中的 W 的部份可以視為員工資料，而 M 的部份可以視為其主管資料，並且加上員工薪資大於主管的薪資 (W.SAL > M.SAL) 之條件做為篩選條件。

在使用前面幾種關聯時，如果關聯條件滿足，系統則取有關的資料；如果條件不滿足，系統則不取相應的資料。而外部關聯則不同，它不僅傳回兩個或兩個以上的表格中能夠直接匹配的資料列外，還傳回關聯表格中無法直接匹配的資料列。其指令格式如下：

SELECT…
FROM <表格名1> , <表格名2>
WHERE { <表格名1.欄位名1> = <表格名2.欄位名2> ( + ) |
( + ) <表格名1.欄位名1 > = <表格名2.欄位名2> } ;

其中：( + ) 是外部關聯運算子，根據它在“ = ”符號的左邊或右邊，分為左外部關聯和右外部關聯，其運算結果是不同的。進行外部關聯時，若沒有 ( + ) 運算子之表格中的一列無法和有 ( + ) 運算子之表格中的任何資料列相關聯，則前者中的資料列會與後者中的一個虛值(Null) 資料列相匹配，經過連結後被傳回。而 Outer Join (外部關聯) 分為 Left Outer Join (左外部關聯)、Right Outer Join (右外部關聯) 與 Full Outer Join (全外部關聯) 三種。假設關聯表R1 與 R2 如下：

A1	A2	A3	B1		B2	B3
A11	A21	J1	J2		B21	B31
A12	A22	J2	J3		B22	B32
A13	A23	J3	J4		B23	B33
A14	A24	J4	J5		B24	B34
R1				R2

1. 左外部關聯(Left Outer Join)
這個關聯運算會把「左方關聯表」的所有資料值輸出，如果左關聯表 R1 中找不到可匹配的 R2 值組時，則會以虛值(Null) 代替 R2 的值組再與 R1 的值關聯運算。R1⋈_{A3
left outer join B1}R2 的結果如下：
　

A1	A2	A3	B1	B2	B3
A11	A21	J1	Null	Null	Null
A12	A22	J2	J2	B21	B31
A13	A23	J3	J3	B22	B32
A14	A24	J4	J4	B23	B33

2. 右外部關聯(Right Outer Join)
這個關聯運算會把「右方關聯表」的所有資料值輸出，如果右關聯表 R2 中找不到可匹配的 R1 值組時，則會以虛值(Null) 代替 R1 的值組再與 R2 的值做關聯(Join) 運算。R1⋈_{A3
right outer join B1}R2 的結果如下：

　

A1	A2	A3	B1	B2	B3
A12	A22	J2	J2	B21	B31
A13	A23	J3	J3	B22	B32
A14	A24	J4	J4	B23	B33
Null	Null	Null	J5	B24	B34

3. 全外部關聯(Full Outer Join)
這個關聯運算會把「左方關聯表」與「右方關聯表」的所有資料值輸出，如果左關聯表 R1 或右關聯表 R2 找不到可匹配的值組做關聯運算時，則會以虛值(Null) 代替。R1⋈_{A3
full outer join B1}R2 的結果如下：
　

A1	A2	A3	B1	B2	B3
A11	A21	J1	Null	Null	Null
A12	A22	J2	J2	B21	B31
A13	A23	J3	J3	B22	B32
A14	A24	J4	J4	B23	B33
Null	Null	Null	J5	B24	B34

	列出員工名字(ENAME)、部門代碼(DEPTNO) 與部門所在(LOC)。

	本例子中為右外部關聯(EMP.DEPTNO (+)= DEPT.DEPTNO)，如果 DEPT.DEPTNO 中有的數值在 EMP.DEPTNO 中沒有關聯記錄值，則在 EMP 表格中利用虛值記錄與之相對應。例如：DEPT.DEPTNO = 40，在做外部關聯時，會為 EMP 表格的欄位產生虛值(NULL)。如果外部關聯運算子 (+) 放在另一側，則結果將會不同。若有 ( + ) 運算子的關聯表格中無法找到符合的資料列，經過系統以虛值 (NULL) 填補相應部分後，均作為外部關聯的結果被傳回。

此外，在一個 SELECT 敘述的 WHERE 子句中，出現比較運算子 NOT IN 是一個最慢的邏輯運算，因為它在 SELECT 子查詢中要讀遍整個表格。為了加快查詢，可以利用外部關聯替代 NOT IN 的子查詢，我們將於後面章節中介紹。