第四章

1. 第四章 結合與子查詢

2. 章節概要 • 4.1 查詢多個資料表的資料 • 4.2 Equi-Join與Non-Equi-Join及Self-Join • 4.3 聯集　 • 4.4 交集與差集　 • 4.5 次查詢 　 • 4.6 結合與次查詢的優缺點

3. 4.1 查詢多個資料表的資料 • 關聯式資料庫中有一個可以將多個資料表組合起來以選取我們需要的資料的方法，稱之為結合(Join；連結；聯結；合併)。 • 我們可以利用結合多個資料表來創建出一個新的資料表，而結合時會利用關聯欄位來連結資料表，也就是利用具有相同定義域的欄位來組合資料表。

4. 查詢多個資料表的資料(續) • Join可以分成以下幾種類型： • CROSS JOIN - 交叉結合查詢 • Θ(Theta) JOIN - Θ結合查詢 • INNER JOIN - 內部結合查詢 • NATURAL JOIN - 自然結合查詢 • OUTER JOIN - 外部結合查詢

5. NATURAL JOIN • 所謂NATURAL JOIN(自然結合)就是利用具相同名稱之特定的欄位來結合資料表，它可以避免結合後的欄位數量過多。最簡單的NATURAL JOIN之SQL語法如下： • 【NATURAL JOIN】(SQL Server未支援) SELECT * FROM 資料表一 NATURAL JOIN 資料表二

6. NATURAL JOIN(續) • 例如我們可以使用NATURAL JOIN來連結訂單主檔(Orders)與訂單明細檔(OrderDetails)如下： • SELECT *FROM Orders NATURAL JOIN OrderDetails; • 以下為執行時的結果：

8. NATURAL JOIN(續) • 我們若使用NATURAL JOIN於Departments和Employees，即下達如下指令： • SELECT *FROM Departments NATURAL JOIN Employees; 註：此時將擷取不到任何資料，因為使用 DepartmentID和Name欄位結合資料表是無效的結合。單獨使用 DepartmentID 才是正確的結合欄位。因此能否正確地使用NATURAL JOIN和資料庫的欄位名稱設計有關。

9. NATURAL JOIN(續) • 例如我們想限定訂單編號(OrderID)小於等於10250的資料，則可下達如下的指令： • SELECT *FROM Orders NATURAL JOIN OrderDetailsWHERE OrderID <= 10250 註：NATURAL JOIN結合的關聯欄位不可加資料表識別名稱，即不可使用 Orders.OrderID。(Oracle)

10. NATURAL JOIN(續) • 對於NATURAL JOIN而言，其整個SQL語法如下： • 【NATURAL JOIN】SELECT 表示式一 AS 別名一, 表示式二 AS 別名二, ...FROM 資料表一 NATURAL JOIN資料表二WHERE 條件表示式GROUP BY 群組名稱一, 群組名稱二, ...HAVING 群組或聚合的篩選條件ORDER BY 排序欄位名稱一 [ASC|DESC], 排序欄位名稱二 [ASC|DESC], ...

11. INNER JOIN • INNER JOIN(內部結合)的SQL語法如下： • 【INNER JOIN】SELECT 表示式一 AS 別名一, 表示式二 AS 別名二, ...FROM 資料表一 INNER JOIN 資料表二ON 關聯條件WHERE 條件表示式GROUP BY 群組名稱一, 群組名稱二, ...HAVING 群組或聚合的篩選條件ORDER BY 排序欄位名稱一 [ASC|DESC],排序欄位名稱二 [ASC|DESC], ...

12. INNER JOIN(續) • NATURAL JOIN可以用INNER JOIN取代 • 如原NATURAL JOIN的指令： • SELECT OrderID, CustomerID, EmployeeID, OrderDateFROM Orders NATURAL JOIN OrderDetailsWHERE OrderID <= 10250 • 可以改寫成： • SELECT Orders.OrderID, CustomerID, EmployeeID, OrderDateFROM Orders INNER JOIN OrderDetailsON Orders.OrderID = OrderDetails.OrderIDWHERE Orders.OrderID <= 10250

13. NATURAL JOIN(續) • 原NATURAL JOIN指令： • SELECT *FROM Departments NATURAL JOIN Employees; • 可取代為 • SELECT Departments.*, Employees.* FROM Departments INNER JOIN Employees ON Departments.DepartmentID = Employees.DepartmentID AND Departments.Name = Employees.Name 註：此時將擷取不到任何資料，因為使用 DepartmentID和Name欄位結合資料表是無效的結合。單獨使用 DepartmentID 才是正確的結合欄位。

14. NATURAL JOIN(續) • 正確的Department和Employees結合指令應為 • SELECT Departments.*, Employees.* FROM Departments INNER JOIN Employees ON Departments.DepartmentID = Employees.DepartmentID 註：單獨使用 DepartmentID 才是正確的結合欄位，而不是DepartmentID和Name的組合。

15. INNER JOIN(續) • 例如我們要選取Departments中所有的欄位及Employees中的EmployeeID、Name、EngName，則可以下達如下的指令： • SELECT Departments.*, EmployeeID,Employees.Name, EngNameFROM Departments INNER JOIN EmployeesON Departments.DepartmentID = Employees.DepartmentID; • 其中 Departments.* 意指我們要選取Departments資料表的所有欄位。而執行的結果如下：

17. INNER JOIN(續) • 假如我們想加額外的條件，則可使用WHERE子句。 • 例如我們只要部門編號10的資料，且依員工的英文名字排序，則可下達如下的指令： • SELECT Departments.*, EmployeeID, Employees.Name, EngNameFROM Departments INNER JOIN EmployeesON Departments.DepartmentID = Employees.DepartmentIDWHERE Departments.DepartmentID = 10ORDER BY Employees.EngName;

18. INNER JOIN(續) • 資料表也可以取別名。 • 如上指令我們可以取Departments的別名為D，而Employees的別名為E。則上述指令可以改寫為： • SELECT D.*, EmployeeID, E.Name, EngNameFROM Departments D INNER JOIN Employees EON D.DepartmentID = E.DepartmentIDWHERE D.DepartmentID = 10ORDER BY E.EngName;

19. INNER JOIN(續) • 為了方便設計SQL指令，我們可以使用DBMS提供的QBE設計功能來設計需要的查詢指令。 • 例如：欲查詢訂單編號<=10250的出貨資料，含 訂單編號OrderID、訂單日期OrderDate、產品編號ProductID、產品名稱ProductName、單價UnitPrice和數量Quantity。 QBE： Query By Example 依範例查詢

20. 使用QBE來設INNER JOIN (Access)

21. 使用QBE來設INNER JOIN (Access) • 以下是其執行的結果畫面：

22. 使用QBE來設INNER JOIN (Access) • 而Access QBE所產生的SQL指令則是如下： • SELECT Orders.OrderID, Orders.OrderDate, OrderDetails.ProductID, Products.ProductName, OrderDetails.UnitPrice, OrderDetails.QuantityFROM Products INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) ON Products.ProductID = OrderDetails.ProductIDWHERE (((Orders.OrderID)<=10250));

23. 使用QBE來設INNER JOIN (SQL Server)

24. INNER JOIN(續) • Enterprise Manager所產生的SQL指令明顯較MS Access容易看得懂，其為： • SELECT Orders.OrderID, Orders.OrderDate, OrderDetails.ProductID, Products.ProductName, OrderDetails.UnitPrice, OrderDetails.QuantityFROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductIDWHERE (Orders.OrderID <= 10250)

25. OUTER JOIN • 在結合資料表時，若僅取出符合結合條件的資料列，這種結合稱為內部結合(Inner Join)。 • 如果在結合時，不管資料列是否符合結合條件，都要取出其中一個資料表的所有資料列，這種結合稱為外部結合(Outer Join；例外結合)。此時，不符合條件的資料列就會被設定為NULL值。 • 當使用外部結合時，若取出所有OUTER JOIN左側的資料表的資料列時，稱為LEFT OUTER JOIN(左側外部結合)。 • 若是要取出所有OUTER JOIN右側的資料表的資料列時，稱為RIGHT OUTER JOIN(右側外部結合)。

26. OUTER JOIN(續) • RIGHT OUTER JOIN可以用LEFT OUTER JOIN取代。 • 當使用外部結合時，若取出所有OUTER JOIN兩側的資料表的資料列時，稱為FULL OUTER JOIN(完全外部結合)。 • OUTER JOIN可細分成如下三種： • LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL OUTER JOIN

27. OUTER JOIN(續) • 例如使用如下的INNER JOIN指令以列出各部門員工資料時： • SELECT D.*, EmployeeID, E.Name, EngNameFROM Departments D INNER JOIN Employees EON D.DepartmentID = E.DepartmentID; • 其可選取出的資料列數為15筆。 • 由於部門編號40沒有員工，故使用INNER JOIN時並不會列出部門編號40的資料錄。

28. OUTER JOIN(續) • 假如我們想列出各部門員工資料，即使該部門為新設而尚無部門員工，則可用LEFT OUTER JOIN，並將Departments置於LEFT OUTER JOIN的左側，即如下指令： • SELECT D.*, EmployeeID, E.Name, EngNameFROM Departments D LEFT OUTER JOIN Employees EON D.DepartmentID = E.DepartmentID; • 以下是OUTER JOIN 執行的結果：

30. OUTER JOIN(使用QBE) • 假如我們想用MS Access的QBE來建立如上OUTER JOIN查詢指令，則於QBE視窗上點選資料表關聯的連線，並按以滑鼠兩下(Double Click)以開啟設定連接屬性的對話盒如下：

31. 選取包括所有來自Departments的記錄和只包括那些連接欄位相等的Employees欄位。選取包括所有來自Departments的記錄和只包括那些連接欄位相等的Employees欄位。

32. OUTER JOIN(使用QBE) • 其中的連接屬性1為預設的連接狀態，即INNER JOIN。而連接屬性2為即為LEFT OUTER JOIN，連接屬性3為即為RIGHT OUTER JOIN。以下則為設定好連接屬性後的樣式：

34. CROSS JOIN • 交叉結合(Cross Join)可以用來取得二個資料表的資料錄與資料錄的乘積，即關聯式代數裡的卡笛生乘積(Caresian Product)。 • 例如部門檔的資料有4筆，而員工檔的資料有15筆，則Cross Join的結果為 4*15=60筆。 • 簡單的CROSS JOIN的語法： • 【CROSS JOIN】 SELECT * FROM 資料表一 CROSS JOIN資料表二 或使用逗點分隔資料表名稱，而寫成： • 【CROSS JOIN】 SELECT * FROM 資料表一 , 資料表二

35. CROSS JOIN(續) • 例如我們想要列出所有的部門與員工的資料列組合，則可下達如下的指令： • SELECT *FROM Departments CROSS JOIN Employees; • 或者 • SELECT *FROM Departments, Employees; • 而這個指令的查詢結果會傳回4*15=60列。

36. CROSS JOIN(續) • 對SELECT查詢而言，其完整的CROSS JOIN語法如下： • 【SELECT指令格式】SELECT 表示式一 AS 別名一, 表示式二 AS 別名二, ...FROM 資料表來源名稱一, 資料表來源名稱二,...WHERE 條件表示式GROUP BY 群組名稱一, 群組名稱二, ...HAVING 群組或聚合的篩選條件ORDER BY 排序欄位名稱一 [ASC|DESC], 排序欄位名稱二 [ASC|DESC], ... 注意：使用cross join時一般皆會有WHERE子句用於設定關聯條件或限制資料輸出量，若無WHERE子句則可能將造成龐大的資料輸出而造成系統無謂的負荷。

37. CROSS JOIN(取代NATURAL JOIN) • NATURAL JOIN可以用CROSS JOIN取代。 • 例如NATURAL JOIN： • SELECT Orders.OrderID, CustomerID, EmployeeID, OrderDateFROM Orders NATURAL JOIN OrderDetailsWHERE Orders.OrderID <= 10250 • 我們只要把原來的關聯條件加入到CROSS JOIN的WHERE子句，寫成： • SELECT Orders.OrderID, CustomerID, EmployeeID, OrderDateFROM Orders, OrderDetailsWHERE Orders.OrderID = OrderDetails.OrderIDAND (Orders.OrderID <= 10250) 即可。

38. CROSS JOIN(取代INNER JOIN) • INNER JOIN可以用CROSS JOIN取代。 • 例如INNER JOIN： • SELECT Orders.OrderID, Orders.OrderDate, OrderDetails.ProductID, Products.ProductName, OrderDetails.UnitPrice, OrderDetails.QuantityFROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductIDWHERE (Orders.OrderID <= 10250)

39. CROSS JOIN(取代INNER JOIN) • 將INNER JOIN的關聯條件併入CROSS JOIN的WHERE子句，改成： • SELECT Orders.OrderID, Orders.OrderDate, OrderDetails.ProductID, Products.ProductName, OrderDetails.UnitPrice, OrderDetails.QuantityFROM Orders, OrderDetails, ProductsWHERE (Orders.OrderID = OrderDetails.OrderID) AND (OrderDetails.ProductID = Products.ProductID) AND (Orders.OrderID <= 10250) • 其中WHERE子句內含兩條關聯條件。

40. 4.2 Equi-Join與Non-Equi-Join及Self-Join • JOIN運算通常是以連結欄位的值是否相等來比較，也就是在WHERE子句中使用 “=”邏輯符號，這種結合方式稱為Equi-Join(等式結合)。 • 但是Join運算也可以用其他的限制條件，可以是等號以外的比較運算子，如： >、>=、<、<=、<>...等等。 • 在關聯式資料庫的專用術語裡，使用任何比較運算子的Join運算統稱為Θ (Theta) Join運算。 • 使用非等號的Join運算，則稱為Non-Equi-Join (非等式結合)。

41. Equi-Join與Non-Equi-Join及Self-Join(續) • 例如如下的部門員工列表查詢指令即為Equi-Join的一個例子：(以下取資料表別名) • SELECT D.DepartmentID, D.Name, E.EmployeeID, E.Name, E.EngNameFROM Departments D, Employees EWHERE D.DepartmentID = E.DepartmentID • 或SELECT D.DepartmentID, D.Name, E.EmployeeID, E.Name, E.EngNameFROM Departments AS D, Employees AS EWHERE D.DepartmentID = E.DepartmentID

42. Equi-Join與Non-Equi-Join及Self-Join(續) • 若欄位名稱為唯一，則可以不用 資料表.欄位名稱，而直接使用其欄位名稱即可。 • SELECT D.DepartmentID, D.Name, EmployeeID, E.Name, EngNameFROM Departments D, Employees EWHERE D.DepartmentID = E.DepartmentID • 或SELECT D.DepartmentID, D.Name, EmployeeID, E.Name, EngNameFROM Departments AS D, Employees AS EWHERE D.DepartmentID = E.DepartmentID

43. Equi-Join與Non-Equi-Join及Self-Join(續) • 對於Equi-Join而言，有一種特殊的變型稱為Self-Join(自身結合)，它只以一個資料表的欄位值來進行連結(連結相同的資料表)。 • 例如我們想查詢和員工王明亮同部門的其他員工的員工編號和姓名，則可下達如下的指令： • SELECT D.DepartmentID, D.EmployeeID, D.NameFROM Employees D, Employees EWHERE D.DepartmentID = E.DepartmentIDAND E.Name = '王明亮'

44. Equi-Join與Non-Equi-Join及Self-Join(續) • 這個查詢命令以Employees資料表和自身進行Join運算，所以Employees資料表同時扮演了兩個角色，因此必須使用別名的方式來表示其不同的角色。而這個查詢的結果則如下：

46. Equi-Join與Non-Equi-Join及Self-Join(續) • 自身連結會把王明亮的資料和其自身比較，造成自己等自己的狀況，因而必須加入一條限制式，以將王明亮本身的資料排除，即下如下的指令： SELECT D.DepartmentID, D.EmployeeID, D.Name FROM Employees D,Employees E WHERE D.DepartmentID = E.DepartmentID AND E.Name = '王明亮' AND D.EmployeeID <> E.EmployeeID • 以下為其執行的結果：

48. Equi-Join與Non-Equi-Join及Self-Join(續) • 假如我們想要用QBE來建立這個查詢，則請參考如下的畫面：

49. Equi-Join與Non-Equi-Join及Self-Join(續) • 例如我們想查詢薪資比王明亮高的員工之員工編號、員工姓名和薪資料，則可以下達如下的指令： SELECT D.DepartmentID, D.EmployeeID, D.Name, D.Salary, E.Salary FROM Employees D, Employees E WHERE D.Salary > E.Salary AND E.Name = '王明亮' • 其查詢的結果如下：