翻轉電子書系列：資料庫系統概論(含邏輯設計)  

翻轉工作室：粘添壽

第六章 合併查詢操作

6-1 合併的基本觀念

所謂『合併』（Join）就是 2 個以上表格結合成一個虛擬表格。由此虛擬表格查詢出所要的訊息。這與第三章、第四章介紹的有很大的差別，第四章僅對一個表格做查詢動作，第五章是一個表格接一個表格的連續查詢動作，雖然各個查詢語句針對不同的表格，但每一次都僅查詢一個表格。然而，合併查詢就不一樣了，一次可能針對 2 個以上的表格查詢，並且讓這些表格之間做運算。

其實，合併查詢僅是一個重要的觀念而已，在實務上我們幾乎不會利用原始資料表之間合併運算來得到訊息，因為它產生的資料量非常大，會降低資料庫的效能。一般我們會將合併查詢轉換成子查詢來運作，不然就是經過子查詢後得到比較小的動態資料表，再利用它來合併查詢，以減少資料產生。

圖 6-1 合併表格的概念

上圖為兩個表格之間做合併動作，假設資料表_A 有 20 筆資料，每一筆資料有 5 個欄位 {20, 5}；資料表_B 有 30 筆資料，每一筆資料有 4 個欄位 {30, 4}，兩者合併後產生的虛擬表格有 20 * 30 =600 筆資料，每一筆有 5 + 4 = 9 個欄位 {600, 9}。以目前資訊時代裡，一個資料表至少有上萬筆資料，兩個資料表座合併查詢，則有可能上億筆資料，如果 2 個以上表格合併查詢，那保證讓會電腦當機。但話說回來，我們還是需要利用合併查詢來得到所要的訊息，只不夠使用方法要有所改變而已。

表格之間交叉合併之後，如何由虛擬表格中擷取訊息，就延伸了幾種類型，可分為『交叉合併』（Cross join）、『內部合併』（Inner join）、『外部合併』（Outer join，未介紹）與『自我合併』（Self join）等四大類。我們先介紹這幾種合併類型的特性，再取一些範例來探討合併的運用，下一章再介紹如何將優化合併查詢。

表格之間合併查詢語句如下：

由上述語句中可以看出，我們可以 2 個以上資料表之間的查詢，並且每一個資料表給與一個 A、B或其它字元做代號，Where 條件判斷還是採用各個資料表欄位的內容，並由各個參與合併資料表的欄位擷取所要的訊息。

6-2 交叉合併

6-2-1 交叉合併的運作

交叉合併又稱為卡式積（Cartesian Product）合併是表格之間最基本的運算，功能是取表格之間所有可能的集合，也就是圖 6-2 所示。假設資料表_A 大小是 {L, m}，表示有 L 筆資料，每筆資料有 m 個欄位；資料表_B 的大小是 {K, n}，兩表格經過卡式積合併後產生的虛擬表為 {L*K, m+n}，有 L*K 筆資料，則每幾資料有 m+n 個欄位。

圖 6-2 卡式積合併結果

6-2-2 交叉合併的範例

(A)程式功能：Ex6_1

查詢教師資料表(teachers)與系所資料表(department)之間可能的集合。

(B)系統分析：

教師資料表 teachers={teacher_ID, name, Dep_ID, sex, tel, title, mailbox, address}，與系所資料表 department = {dep_ID, dep_name} 兩者合併後，其虛擬表格有 (teacher_ID, name, teachers.Dep_ID, sex, tel, mailbos, address, department.dep_ID, dep_name) 等 10 欄位。如果 teachers有 10 筆資料，系department 有 6 筆資料的話，合併後會產生 10 * 6 = 60 筆資料的虛擬表格。

(C)程式範例：

(D)執行結果：(僅顯示部分內容)

當然，我們也可以由上圖中擷取某些欄位，但系統的運作還是產生了龐大的虛擬表再來擷取，所佔用的記憶體空間還是很大。

6-3 兩表格合併查詢

6-3-1 表格合併語法

內部合併又稱為『條件式合併』，也就是交叉合併再加上倆資料表之間的『條件限制』，或稱為『對應』關係的合併。也就是說，兩資料表之間必定存在一個或多個屬性的『比較關係』，包括相等（=）、不相等（<>）、大於（>）、小於（<）、等等符號。

內部合併語法如下：

輸出虛擬表格的欄位數量與交叉合併相同，但資料筆數僅滿足條件部分，如下圖所示：

6-3-2 範例研討：查詢各系系主任

(A) 程式功能：Ex6_2

請查詢出各系主任資料，列印出：系別名稱、系主任姓名、電話與地址。

(B)系統分析：

系別名稱是登錄在 department 表內，至於誰是系主任，則登錄在 teachers 表中 title =系統主任，他們之間是利用 dep_ID 欄位關聯。

(C)程式範例：

• 步驟 1：找出系主任的教師資料。

我們可以發現，虛擬表格也是 10 個欄位 (department + teachers)，但資料筆數還非常多。原因是每位系主任的資料還交叉混合到其他系資料內，因此需資料另一個過濾條件。

• 步驟 2：增加 teachers.Dep_ID = department.Dep_ID 條件，如下：

已可以過濾取得各系主任資料，但並非所有資料都要輸出，僅擷取部分資料即可，如下：

• 步驟 3：擷取所需欄位，如下：

• 步驟 4：改成多重子查詢練習

6-3-3 範例研討：查詢班級名冊

(A) 程式功能：Ex6_3

請查詢『進四資管二丙』班級學生名冊，請列印出：班別名稱、姓名、性別、電話與地址。

(B) 系統分析：

全校學生資料是登錄在 students 表內，至於是哪一班級的學生，則利用 class_ID 標示，但 class_ID 對應到班級名稱則登錄在 classes 表內。因此， students.class_ID 與 classes.class_ID 之間建立了關聯。

(C) 程式範例：

• 步驟 1：找出進『進四資管二丙』班級內學生資料。

我們可以發現，虛擬表格也是 11 個欄位 (students + classes)，但資料筆數還非常多。原因是每位系主任的資料還交叉混合到其他系資料內，因此需資料另一個過濾條件。

• 步驟 2：擷取所需欄位，如下：

• 步驟 3：改成多重子查詢練習

6-3-4 範例研討：查詢系所課程規劃資料

(A) 程式功能：Ex6_4

請查詢『資訊管理系』全學年規劃有那些課程，請依照：系別名稱、課程代碼、課程名稱、學分數、必選修之順序印出。

(B) 系統分析：

全校各系所規劃的課程全部登錄於 all_course 表內，但該表以 dep_ID 標示哪一系所規劃之課程，又 dep_ID 對應到哪一夕，則登錄於 department 表內。因此，我們將 all_course 與 department 兩表個合併後，取 department.dep_ID = all_course.dep_ID，必且過濾 depart_name = “資訊管理系”，如下圖所示。

 (C) 程式範例：

• 步驟 1：department 與 all_course 兩表格內部合併結果如下：

• 步驟 2：擷取所需欄位，如下：

• 步驟 4：改成多重子查詢練習

6-3-5 範例研討：表格與自己合併

(A) 程式功能：Ex6_5

請查詢全校內姓名相同的學生。

(B) 系統分析：

全校學生資料都登錄於 students 資料表內，將該表格自我合併，找出姓名相同，但學號不同的學生，印出其資料即是。

(C) 程式範例：

Students 表格自我合併語法如下：

6-3-6 自我挑戰：查詢各系班級名稱

(A) 程式功能：PM6_1

查詢『資訊管理系』有哪些班級，並印出系別名稱、班級代碼與班級名稱，查詢結果如下：

 (B) 系統分析：

全校班級都登錄於 classes 表內，並且 dep_ID 標示是屬於哪一系別，但 dep_ID 對應到系別名稱則登錄於 department 表內。則必須將 classes 與 department 兩表交叉合併如下：

6-4 多表格合併查詢

許多查詢條件必須兩個以上表格合併而成，牽涉到問題就比較複雜，我們用幾個實例來介紹。

6-4-1 範例研討：查詢教師開課課程

(A) 程式功能：Ex6_6

請查詢『粘添壽』老師這學期開了那些課程，請依照班級名稱、課程名稱、必選修、學分數支順序印出。

(B) 系統分析：

這題目看起來稀鬆平常，但做出來可不簡單。全校所開的課程都登錄於『open_course』表內，但它僅記錄教師代碼(teacher_ID)，因此我們必須由『teachers』表得知粘添壽老師的代碼多少。另外，它也僅登錄課程代碼(course_ID)，我們還需結合『all_course』才知道所開課程的名稱與相關資料。 還有班級資料登錄於 classes 表格內，由此可見，此問題牽涉到 4 個表格，但合併表格時，我們只要擷取有用到的欄位就好，這樣產生的虛擬表格就不會太大。

(C) 程式範例：

• 步驟 1：先由 teachers 與 open_course 兩表格中查詢出，粘添壽老師開了那些課程，以及其班級，如下：

• 步驟 2：再合併 all_course 表，取出課程資料，如下：

• 步驟 3：再合併 classes 取出班級資料，如下：

6-4-2 範例研討：同上題 - 巢狀查詢

(A) 程式功能：Ex6_7

請查詢『粘添壽』老師這學期開了那些課程，請依照班級名稱、課程名稱、必選修、學分數支順序印出。

(B) 系統分析：(同上題分析)

(C) 程式範例：

l   步驟 1：先由 teachers 與 open_course 兩表格中查詢出，粘添壽老師開了那些課程，以及其班級，如下：

l   步驟 2：我們得到了粘添壽老師開課的班級代碼與課程代碼後，利用課程代碼由 all_course 表查詢出該課程的相關資料，如下：

得到結果如下：

上述結果並沒有得到該門課開在哪一個班級的班級代碼(class_ID)，吾人需要再增加得到班級代碼的查詢語句。

l   步驟 3 (很難達成)：接下來，我們必須在最後輸出語句中增加班級代碼，語法如下：

上述語法無法執行，原因是兩個子查詢都可能是一個以上的結果，因此無法匹配。由此可見，該題很難由巢狀查詢方法得到結果，利用合併查詢較容易。

6-4-3 自我挑戰：查詢學生選課資料

(A) 程式功能：PM6_3

查詢學號 = 『90211306』同學，這學期修了那些課程，請依照開課班級名稱、課程名稱、學分數、必選修之順序印出，查詢結果如下：

(B) 系統分析：

全校學生選課情況都登錄於 select_course 表格內。吾人可利用學生學號由此表格中查詢出他修讀哪門課的 open_course_ID，再利用 open_course_ID 由 open_course 表中查詢出課程代碼(course_ID) 、班級代碼(class_ID) 與 開課教師的代碼( teacher_ID)，再分別由 all_course 與 classes表格中查詢他們的名稱，如下圖所示。

6-4-4 自我挑戰：查詢開課班級與教師

(A) 程式功能：PM6_4

請查詢這學期是否有開『資料庫管理系統』課程，請列印出課程代碼、開課班級及開課教師資料。查詢結果如下：

(B) 系統分析：

首先須由 all_course 表中查詢出『資料庫管理系統』的課程代碼(course_ID)，再利用它由 open_course 表中查詢出該課程開在哪一班(class_ID)與哪位老師(teacher_ID) 開的，再由 teachers 與 classes 表中查詢出它的名稱，如下圖所示。

6-5 表格之間的集合

表格經 SQL 處理之後的結果也是一個表格，但他的型態也許會和原表格不同，如果兩個 SQL 語句處理後之虛擬表格型態相同，可透過集合運算將兩個虛擬表格結合成一個表格。表格集合運算有：

• 聯集（Union）

• 交集（Intersect）

• 減集（Minus）

• …

6-5-1 範例研討：查詢同地區師生名單

(A) 程式功能：Ex6_8

請查詢住在『台南市』的老師和學生名冊，請依照姓名、電話、地址之順序印出。

(B) 系統分析：

我們分別由 teachers 與 students 兩表格中找出台南市者，兩表格輸出再聯集成一個表格即可。

(C) 程式範例：

程式如下：

6-6 專題研討

(A) 程式功能：Ex6_7

請查詢選修『粘添壽』老師所開的『資料庫管理系統』學生的名冊，請依照班級名稱、姓名、性別，以及電話等順序印出。

(B) 系統分析：

此題目牽涉到六個資料表，如下圖所示。首先必須由 all_course 與 teachers 兩表格中查詢到『資料庫管理系統』的課程代碼(course_ID)，『粘添壽』老師的教師代碼(teacher_ID)，再利用這兩個代碼由 open_course 表中查詢出開課代碼(open_course_ID)。接著，利用 open_course_ID 由 select_course 表中查詢出有哪些學生(student_ID)選修該課程，再利用選課學生的 stucent_ID，由 students 表中查詢出學生的相關資料。

(C) 程式範例：

• 步驟 1：首先由 teachers 與 all_course 兩表格中查詢出，『粘添壽』老師所開『資料庫管理系統』的開課代碼 (open_course_ID)，，如下：

• 步驟 2：找到開課代碼 open_course_ID後，再由 select_course 表中查詢出有哪些學生修課的 student_ID，如下：

• 步驟 3：再利用 student_ID 由 students 表中查詢出學生資料，如下：

• 步驟 4：再利用 class_ID 由 classes 表中查詢出班級名稱，如下：

• 步驟 4：改成多重子查詢練習

(C) 進階研究：(請自行研習)

上述查詢結果並沒有顯示該課程是開在哪一班級的班級名稱，請修改上述查詢語句，使其能顯示開在哪一班的班級名稱。