翻轉工作室:粘添壽
第六章 合併查詢操作
所謂『合併』(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)等四大類。我們先介紹這幾種合併類型的特性,再取一些範例來探討合併的運用,下一章再介紹如何將優化合併查詢。
表格之間合併查詢語句如下:
SELECT A.欄位, …, B.欄位, … From資料表_1 as A, 資料表_2 as B, … Where <A.欄位 | B.欄位條件判斷>; |
由上述語句中可以看出,我們可以 2 個以上資料表之間的查詢,並且每一個資料表給與一個 A、B或其它字元做代號,Where 條件判斷還是採用各個資料表欄位的內容,並由各個參與合併資料表的欄位擷取所要的訊息。
6-2 交叉合併
交叉合併又稱為卡式積(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)程式範例:
Select * From teachers, department; |
(D)執行結果:(僅顯示部分內容)
當然,我們也可以由上圖中擷取某些欄位,但系統的運作還是產生了龐大的虛擬表再來擷取,所佔用的記憶體空間還是很大。
6-3 兩表格合併查詢
6-3-1 表格合併語法
內部合併又稱為『條件式合併』,也就是交叉合併再加上倆資料表之間的『條件限制』,或稱為『對應』關係的合併。也就是說,兩資料表之間必定存在一個或多個屬性的『比較關係』,包括相等(=)、不相等(<>)、大於(>)、小於(<)、等等符號。
內部合併語法如下:
SELECT A.欄位, …, B.欄位, … From資料表_1 as A, 資料表_2 as B, … Where <條件判斷>; |
輸出虛擬表格的欄位數量與交叉合併相同,但資料筆數僅滿足條件部分,如下圖所示:
(A) 程式功能:Ex6_2
請查詢出各系主任資料,列印出:系別名稱、系主任姓名、電話與地址。
(B)系統分析:
系別名稱是登錄在 department 表內,至於誰是系主任,則登錄在 teachers 表中 title =系統主任,他們之間是利用 dep_ID 欄位關聯。
(C)程式範例:
步驟 1:找出系主任的教師資料。
Select * From teachers as A, department as B Where A.title = "系主任"; |
我們可以發現,虛擬表格也是 10 個欄位 (department + teachers),但資料筆數還非常多。原因是每位系主任的資料還交叉混合到其他系資料內,因此需資料另一個過濾條件。
步驟 2:增加 teachers.Dep_ID = department.Dep_ID 條件,如下:
Select * From teachers as A, department as B Where A.title = "系主任" And A.Dep_ID = B.Dep_ID; |
已可以過濾取得各系主任資料,但並非所有資料都要輸出,僅擷取部分資料即可,如下:
步驟 3:擷取所需欄位,如下:
Select dep_name, name, tel, address From teachers as A, department as B Where A.title = "系主任" And A.Dep_ID = B.Dep_ID; |
Select (select dep_name from department where dep_ID = A.dep_ID) as "系別名稱", name, tel, address From teachers as A Where title = "系主任"; |
(A) 程式功能:Ex6_3
請查詢『進四資管二丙』班級學生名冊,請列印出:班別名稱、姓名、性別、電話與地址。
(B) 系統分析:
全校學生資料是登錄在 students 表內,至於是哪一班級的學生,則利用 class_ID 標示,但 class_ID 對應到班級名稱則登錄在 classes 表內。因此, students.class_ID 與 classes.class_ID 之間建立了關聯。
(C) 程式範例:
步驟 1:找出進『進四資管二丙』班級內學生資料。
Select * From students as A, classes as B Where A.class_ID = B.class_ID And B.class_name = "進四資管二丙"; |
我們可以發現,虛擬表格也是 11 個欄位 (students + classes),但資料筆數還非常多。原因是每位系主任的資料還交叉混合到其他系資料內,因此需資料另一個過濾條件。
步驟 2:擷取所需欄位,如下:
Select class_name, name, sex, tel, address From students as A, classes as B Where A.class_ID = B.class_ID And B.class_name = "進四資管二丙"; |
步驟 3:改成多重子查詢練習
Select (select class_name from classes where class_ID = A. class_ID) as "班級", name, sex, tel, address From students as A Where class_ID = (Select class_ID From classes Where class_name = "進四資管二丙"); |
(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 兩表格內部合併結果如下:
Select * From department as A, all_course as B Where A.dep_ID = B.dep_ID And A.dep_name = "資訊管理系"; |
步驟 2:擷取所需欄位,如下:
Select dep_name, course_ID, course_name, credit, required From department as A, all_course as B Where A.dep_ID = B.dep_ID And A.dep_name = "資訊管理系"; |
步驟 4:改成多重子查詢練習
請自行練習 |
(A) 程式功能:Ex6_5
請查詢全校內姓名相同的學生。
(B) 系統分析:
全校學生資料都登錄於 students 資料表內,將該表格自我合併,找出姓名相同,但學號不同的學生,印出其資料即是。
(C) 程式範例:
Students 表格自我合併語法如下:
Select A.* From students as A, students as B Where A.name = B.name And A.student_ID <> B.student_ID; |
(A) 程式功能:PM6_1
查詢『資訊管理系』有哪些班級,並印出系別名稱、班級代碼與班級名稱,查詢結果如下:
(B) 系統分析:
全校班級都登錄於 classes 表內,並且 dep_ID 標示是屬於哪一系別,但 dep_ID 對應到系別名稱則登錄於 department 表內。則必須將 classes 與 department 兩表交叉合併如下:
6-4 多表格合併查詢
許多查詢條件必須兩個以上表格合併而成,牽涉到問題就比較複雜,我們用幾個實例來介紹。
(A) 程式功能:Ex6_6
請查詢『粘添壽』老師這學期開了那些課程,請依照班級名稱、課程名稱、必選修、學分數支順序印出。
(B) 系統分析:
這題目看起來稀鬆平常,但做出來可不簡單。全校所開的課程都登錄於『open_course』表內,但它僅記錄教師代碼(teacher_ID),因此我們必須由『teachers』表得知粘添壽老師的代碼多少。另外,它也僅登錄課程代碼(course_ID),我們還需結合『all_course』才知道所開課程的名稱與相關資料。 還有班級資料登錄於 classes 表格內,由此可見,此問題牽涉到 4 個表格,但合併表格時,我們只要擷取有用到的欄位就好,這樣產生的虛擬表格就不會太大。
(C) 程式範例:
步驟 1:先由 teachers 與 open_course 兩表格中查詢出,粘添壽老師開了那些課程,以及其班級,如下:
Select A.course_ID, A.class_ID From open_course as A, teachers as B where A.teacher_ID = B.teacher_ID And B.name = "粘添壽"; |
步驟 2:再合併 all_course 表,取出課程資料,如下:
Select A.course_ID, C.course_name, C.required, C.credit, A.class_ID From open_course as A, teachers as B, all_course as C where A.teacher_ID = B.teacher_ID And B.name = "粘添壽" And A.course_ID = C.course_ID; |
步驟 3:再合併 classes 取出班級資料,如下:
Select D.class_name, A.course_ID, C.course_name, C.required, C.credit From open_course as A, teachers as B, all_course as C, classes as D where A.teacher_ID = B.teacher_ID And B.name = "粘添壽" And A.course_ID = C.course_ID And A.class_ID = D.class_ID; |
6-4-2 範例研討:同上題 - 巢狀查詢
(A) 程式功能:Ex6_7
請查詢『粘添壽』老師這學期開了那些課程,請依照班級名稱、課程名稱、必選修、學分數支順序印出。
(B) 系統分析:(同上題分析)
(C) 程式範例:
l 步驟 1:先由 teachers 與 open_course 兩表格中查詢出,粘添壽老師開了那些課程,以及其班級,如下:
select course_ID, class_ID from open_course where teacher_ID = (select teacher_ID from teachers where name = "粘添壽"); |
l 步驟 2:我們得到了粘添壽老師開課的班級代碼與課程代碼後,利用課程代碼由 all_course 表查詢出該課程的相關資料,如下:
select course_ID, course_name, required, credit from all_course where course_ID in (select course_ID from open_course where teacher_ID = (select teacher_ID from teachers where name = "粘添壽")); |
得到結果如下:
上述結果並沒有得到該門課開在哪一個班級的班級代碼(class_ID),吾人需要再增加得到班級代碼的查詢語句。
l 步驟 3 (很難達成):接下來,我們必須在最後輸出語句中增加班級代碼,語法如下:
select course_ID, course_name, required, credit, (select class_ID from open_course where teacher_ID = (select teacher_ID from teachers where name = "粘添壽")) from all_course where course_ID in (select course_ID from open_course where teacher_ID = (select teacher_ID from teachers where name = "粘添壽")); |
上述語法無法執行,原因是兩個子查詢都可能是一個以上的結果,因此無法匹配。由此可見,該題很難由巢狀查詢方法得到結果,利用合併查詢較容易。
(A) 程式功能:PM6_3
查詢學號 = 『90211306』同學,這學期修了那些課程,請依照開課班級名稱、課程名稱、學分數、必選修之順序印出,查詢結果如下:
(B) 系統分析:
全校學生選課情況都登錄於 select_course 表格內。吾人可利用學生學號由此表格中查詢出他修讀哪門課的 open_course_ID,再利用 open_course_ID 由 open_course 表中查詢出課程代碼(course_ID) 、班級代碼(class_ID) 與 開課教師的代碼( teacher_ID),再分別由 all_course 與 classes表格中查詢他們的名稱,如下圖所示。
(A) 程式功能:PM6_4
請查詢這學期是否有開『資料庫管理系統』課程,請列印出課程代碼、開課班級及開課教師資料。查詢結果如下:
(B) 系統分析:
首先須由 all_course 表中查詢出『資料庫管理系統』的課程代碼(course_ID),再利用它由 open_course 表中查詢出該課程開在哪一班(class_ID)與哪位老師(teacher_ID) 開的,再由 teachers 與 classes 表中查詢出它的名稱,如下圖所示。
6-5 表格之間的集合
表格經 SQL 處理之後的結果也是一個表格,但他的型態也許會和原表格不同,如果兩個 SQL 語句處理後之虛擬表格型態相同,可透過集合運算將兩個虛擬表格結合成一個表格。表格集合運算有:
聯集(Union)
交集(Intersect)
減集(Minus)
…
(A) 程式功能:Ex6_8
請查詢住在『台南市』的老師和學生名冊,請依照姓名、電話、地址之順序印出。
(B) 系統分析:
我們分別由 teachers 與 students 兩表格中找出台南市者,兩表格輸出再聯集成一個表格即可。
(C) 程式範例:
程式如下:
Select name, tel, address From students Where address Like "台南市%" Union Select name, tel, address From teachers Where address Like "台南市%"; |
(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),,如下:
Select C.open_course_ID From teachers as A, all_course as B, open_course as C Where A.name = "粘添壽" And B.course_name = "資料庫管理系統" And A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID; |
步驟 2:找到開課代碼 open_course_ID後,再由 select_course 表中查詢出有哪些學生修課的 student_ID,如下:
Select D.student_ID From teachers as A, all_course as B, open_course as C, select_course as D Where A.name = "粘添壽" And B.course_name = "資料庫管理系統" And A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID And C.open_course_ID = D.open_course_ID; |
步驟 3:再利用 student_ID 由 students 表中查詢出學生資料,如下:
Select E.student_ID, E.name, E.sex, E.tel From teachers as A, all_course as B, open_course as C, select_course as D, students as E Where A.name = "粘添壽" And B.course_name = "資料庫管理系統" And A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID And C.open_course_ID = D.open_course_ID And E.student_ID = D.student_ID; |
步驟 4:再利用 class_ID 由 classes 表中查詢出班級名稱,如下:
Select F.class_name, E.student_ID, E.name, E.sex, E.tel From teachers as A, all_course as B, open_course as C, select_course as D, students as E, classes as F Where A.name = "粘添壽" And B.course_name = "資料庫管理系統" And A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID And C.open_course_ID = D.open_course_ID And E.student_ID = D.student_ID And F.class_ID = E.class_ID; |
步驟 4:改成多重子查詢練習
請參閱 5-4 節說明 |
(C) 進階研究:(請自行研習)
上述查詢結果並沒有顯示該課程是開在哪一班級的班級名稱,請修改上述查詢語句,使其能顯示開在哪一班的班級名稱。