連接2 · 數據庫學習摘記

SQL 的連接(JOIN)語句將數據庫中的兩個或多個表組合起來.[1] 由"連接"生成的集合, 可以被保存為表, 或者當成表來使用. JOIN 語句的含義是把兩張表的屬性通過它們的值組合在一起. 基于 ANSI 標準的 SQL 列出了五種 JOIN 方式: 內連接(INNER), 全外連接(FULL OUTER), 左外連接(LEFT OUTER), 右外連接(RIGHT OUTER)和交叉連接(CROSS). 在特定的情況下, 一張表(基本表, 視圖, 或連接表)可以和自身進行連接, 成為自連接(self-join). 程序員用 JOIN 謂詞表示要得到"連接"后的集合. 如果evaluated predicate為真, 組合后的記錄就會按照預期的方式生成, 如一個記錄集, 或者一張臨時表. ## 示例用表下文中解釋"連接"都將用到這里的兩張表. 表中的記錄(行)用于演示不同類型的"連接"和"連接謂詞"的作用. 在下面兩張表中,?`Department.DepartmentID`?是主鍵,?`Employee.DepartmentID`?是外鍵. 雇員表(Employee) | LastName | DepartmentID | | --- | --- | | Rafferty | 31 | | Jones | 33 | | Steinberg | 33 | | Robinson | 34 | | Smith | 34 | | Jasper | NULL | 部門表(Department) | DepartmentID | DepartmentName | | --- | --- | | 31 | 銷售部 | | 33 | 工程部 | | 34 | 秘書 | | 35 | 市場部 | 注: "市場部" 目前沒有員工列出. 同樣, 雇員 "Jasper" 不在部門表中的任何一個部門. ## 內連接 **內連接**(**inner join**)是應用程序中用的普遍的"連接"操作，它一般都是默認連接類型。內連接基于連接謂詞將兩張表(如 A 和 B)的列組合在一起，產生新的結果表。查詢會將 A 表的每一行和 B 表的每一行進行比較，并找出滿足連接謂詞的組合。當連接謂詞被滿足，A 和 B 中匹配的行會按列組合(并排組合)成結果集中的一行。連接產生的結果集，可以定義為首先對兩張表做笛卡爾積(交叉連接) -- 將 A 中的每一行和 B 中的每一行組合，然后返回滿足連接謂詞的記錄。實際上 SQL 產品會盡可能用其他方式去實現連接，笛卡爾積運算是非常沒效率的. SQL 定義了兩種不同語法方式去表示"連接"。首先是"顯式連接符號"，它顯式地使用關鍵字?`JOIN`，其次是"隱式連接符號"，它使用所謂的"隱式連接符號"。隱式連接符號把需要連接的表放到?`SELECT`?語句的?`FROM`?部分，并用逗號隔開。這樣就構成了一個"交叉連接"，`WHERE`?語句可能放置一些過濾謂詞(過濾條件)。那些過濾謂詞在功能上等價于顯式連接符號. SQL 89標準只支持內部連接與交叉連接，因此只有隱式連接這種表達方式；SQL 92標準增加了對外部連接的支持，這才有了`JOIN`表達式。內連接"可以進一步被分為: 相等連接，自然連接，和交叉連接(見下). 程序要應該特別注意連接依據的列可能包含 NULL 值，NULL 值不與任何值匹配(甚至和它本身) -- 除非連接條件中顯式地使用?`IS NULL`?或?`IS NOT NULL`?等謂詞. 例如，下面的查詢通過 Employee 表和 Department 表共有的屬性 DepartmentID 連接兩表。在兩表 DepartmentID 匹配之處(如連接謂詞被滿足)，查詢將組合兩表的?*LastName*，*DepartmentID*?和*DepartmentName*?等列，把它們放到結果表的一行(一條記錄)里。當 DepartmentID 不匹配，就不會往結果表中生成任何數據. 顯式的內連接實例: ~~~ SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ 等價于: ~~~ SELECT * FROM employee，department WHERE employee.DepartmentID = department.DepartmentID ~~~ 顯式的內連接的輸出結果: | Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID | | --- | --- | --- | --- | | Robinson | 34 | 秘書 | 34 | | Jones | 33 | 工程部 | 33 | | Smith | 34 | 秘書 | 34 | | Steinberg | 33 | 工程部 | 33 | | Rafferty | 31 | 銷售部 | 31| **注**?雇員 "Jasper" 和部門 "市場部" 都未出現。它們在預期得到的表中沒有任何匹配的記錄: "Jasper" 沒有關聯的部門，而號碼為35的部門中沒有任何雇員。這樣，在"連接"后的表中，就沒有關于 Jasper 或市場部的信息了。相對于預期的結果，這個行為可能是一個微妙的[Bug](https://zh.wikipedia.org/wiki/Bug "Bug")。外連接可能可以避免這種情況. ### 相等鏈接[[編輯](https://zh.wikipedia.org/w/index.php?title=%E8%BF%9E%E6%8E%A5_(SQL)&action=edit&section=3 "編輯小節：相等鏈接")] 相等連接 (**equi-join**，或?**equijoin**)，是比較連接(*θ連接*)的一種特例，它的連接謂詞只用了相等比較。使用其他比較操作符(如?`<`)的不是相等連接。前面的查詢已經展示了一個相等連接的實例: ~~~ SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ SQL 提供了一種可選的簡短符號去表達相等連接，它使用?`USING`?關鍵字 (Feature ID F402): ~~~ SELECT * FROM employee INNER JOIN department USING (DepartmentID) ~~~ `USING`?結構并不僅僅是[語法糖](https://zh.wikipedia.org/wiki/%E8%AF%AD%E6%B3%95%E7%B3%96 "語法糖")，上面查詢的結果和使用顯式謂詞得到的查詢得到的結果是不同的。特別地，在?`USING`?部分列出的列(column)將在連接結果的臨時表中只出現一次，且無表名限定列名.在上面的例子中，連接結果的臨時表產生單獨的名為?`DepartmentID`?的列，而不是?`employee.DepartmentID`?或?`department.DepartmentID`. `USING`?語句現已被 MySQL，Oracle，PostgreSQL，SQLite，和 DB2/400 等產品支持. ### 自然連接 [自然連接]比相等連接的進一步特例化。兩表做自然連接時，兩表中的所有名稱相同的列都將被比較，這是隱式的。自然連接得到的結果表中，兩表中名稱相同的列只出現一次. 上面用于內連接的查詢實例可以用自然連接的方式表示如下: ~~~ SELECT * FROM employee NATURAL JOIN department ~~~ 用了?`USING`?語句后，在連接表中，DepartmentID 列只出現一次，且沒有表名作前綴: | DepartmentID | Employee.LastName | Department.DepartmentName | | --- | --- | --- | --- | | 34 | Smith | 秘書 | | 33 | Jones | 工程部 | | 34 | Robinson | 秘書 | | 33 | Steinberg | 工程部 | | 31 | Rafferty | 銷售部 | 在?[Oracle](https://zh.wikipedia.org/wiki/Oracle "Oracle")?里用?`JOIN USING`?或?`NATURAL JOIN`?時，如果兩表共有的列的名稱前加上某表名作為前綴，則會報編譯錯誤: "ORA-25154: column part of USING clause cannot have qualifier" 或 "ORA-25155: column used in NATURAL join cannot have qualifier". ### 交叉連接 **交叉連接**(**cross join**)，又稱**[笛卡爾連接](https://zh.wikipedia.org/w/index.php?title=%E7%AC%9B%E5%8D%A1%E7%88%BE%E8%BF%9E%E6%8E%A5&action=edit&redlink=1 "笛卡爾連接（頁面不存在）")**(**cartesian join**)或**[叉乘](https://zh.wikipedia.org/wiki/%E5%8F%89%E4%B9%98 "叉乘")**(**Product**)，它是所有類型的內連接的基礎。把表視為行記錄的集合，交叉連接即返回這兩個集合的[笛卡爾積](https://zh.wikipedia.org/wiki/%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF "笛卡爾積")。這其實等價于內連接的鏈接條件為"永真"，或連接條件不存在. 如果 A 和 B 是兩個集合，它們的交叉連接就記為: A × B. 用于交叉連接的 SQL 代碼在?`[FROM](https://zh.wikipedia.org/w/index.php?title=From_(SQL)&action=edit&redlink=1 "From (SQL)（頁面不存在）")`?列出表名，但并不包含任何過濾的連接謂詞. 顯式的交叉連接實例: ~~~ SELECT * FROM employee CROSS JOIN department ~~~ 隱式的交叉連接實例: ~~~ SELECT * FROM employee ,department; ~~~ | Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID | | --- | --- | --- | --- | | Rafferty | 31 | Sales | 31 | | Jones | 33 | Sales | 31 | | Steinberg | 33 | Sales | 31 | | Smith | 34 | Sales | 31 | | Robinson | 34 | Sales | 31 | | Jasper | NULL | Sales | 31 | | Rafferty | 31 | Engineering | 33 | | Jones | 33 | Engineering | 33 | | Steinberg | 33 | Engineering | 33 | | Smith | 34 | Engineering | 33 | | Robinson | 34 | Engineering | 33 | | Jasper | NULL | Engineering | 33 | | Rafferty | 31 | Clerical | 34 | | Jones | 33 | Clerical | 34 | | Steinberg | 33 | Clerical | 34 | | Smith | 34 | Clerical | 34 | | Robinson | 34 | Clerical | 34 | | Jasper | NULL | Clerical | 34 | | Rafferty | 31 | Marketing | 35 | | Jones | 33 | Marketing | 35 | | Steinberg | 33 | Marketing | 35 | | Smith | 34 | Marketing | 35 | | Robinson | 34 | Marketing | 35 | | Jasper | NULL | Marketing | 35 | 交叉連接不會應用任何謂詞去過濾結果表中的記錄。程序員可以用?`WHERE`?語句進一步過濾結果集. ## 外連接 [外連接]并不要求連接的兩表的每一條記錄在對方表中都一條匹配的記錄。要保留所有記錄（甚至這條記錄沒有匹配的記錄也要保留）的表稱為**保留表**。外連接可依據連接表保留左表, 右表或全部表的行而進一步分為左外連接, 右外連接和全連接. (在這種情況下*left* 和?*right* 表示?`JOIN`?關鍵字的兩邊.) 在標準的 SQL 語言中, 外連接沒有隱式的連接符號. 外部連接既包含ON子句又包含WHERE子句時，應當只把表之間的連接條件寫在ON子句中，對表中數據的篩選必須寫在WHERE子句中。而內部連接的各條件表達式既可以放在ON子句又可以放在WHERE子句中。這是因為對于外部連接，保留表中被ON子句篩除掉的行要被添加回來，在此操作之后才會用WHERE子句去篩選連接結果中的各行。 ### 左外連接 *左外連接*(*left outer join*), 亦簡稱為**左連接**(**left join**), 若 A 和 B 兩表進行左外連接, 那么結果表中將包含"左表"(即表 A)的所有記錄, 即使那些記錄在"右表" B 沒有符合連接條件的匹配. 這意味著即使?`ON`?語句在 B 中的匹配項是0條, 連接操作還是會返回一條記錄, 只不過這條記錄中來自于 B 的每一列的值都為 NULL. 這意味著**左外連接**會返回左表的所有記錄和右表中匹配記錄的組合(如果右表中無匹配記錄, 來自于右表的所有列的值設為 NULL). 如果左表的一行在右表中存在多個匹配行, 那么左表的行會復制和右表匹配行一樣的數量, 并進行組合生成連接結果. 如, 這允許我們去找到雇員的部門時, 顯示所有雇員, 即使這個雇員還沒有關聯的部門. (在上面的內連接部分由一個相反的例子, 沒有關聯的部門號的雇員在結果中是不顯示的). 左外連接實例: (相對于內連接增添的行用斜體標出) ~~~ SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ | Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID | | --- | --- | --- | --- | | Jones | 33 | Engineering | 33 | | Rafferty | 31 | Sales | 31 | | Robinson | 34 | Clerical | 34 | | Smith | 34 | Clerical | 34 | | *Jasper* | NULL | NULL | NULL | | Steinberg | 33 | Engineering | 33 | ### 右外連接 **右外連接**, 亦簡稱**右連接**, 它與左外連接完全類似, 只不過是作連接的表的順序相反而已. 如果 A 表右連接 B 表, 那么"右表" B 中的每一行在連接表中至少會出現一次. 如果 B 表的記錄在"左表" A 中未找到匹配行, 連接表中來源于 A 的列的值設為 NULL. 右連接操作返回右表的所有行和這些行在左表中匹配的行(沒有匹配的, 來源于左表的列值設為 NULL). 例如, 這允許我們在找每一個雇員以及他的部門信息時, 當這個部門里沒有任何雇員時, 也把部門顯示出來. 右連接的實例: (相對于內連接增添的行用斜體標出) ~~~ SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ | Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID | | --- | --- | --- | --- | | Smith | 34 | Clerical | 34 | | Jones | 33 | Engineering | 33 | | Robinson | 34 | Clerical | 34 | | Steinberg | 33 | Engineering | 33 | | Rafferty | 31 | Sales | 31 | | NULL | NULL | *Marketing* | *35* | 實際上顯式的右連接很少使用, 因為它總是可以被替換成左連接--換換表的位置就可以了, 另外, 右連接相對于左連接并沒有什么額外的功能. 上表同樣可以使用左連接得到: ~~~ SELECT * FROM department LEFT OUTER JOIN employee ON employee.DepartmentID = department.DepartmentID ~~~ ### 全連接 **全連接**是左右外連接的并集. 連接表包含被連接的表的所有記錄, 如果缺少匹配的記錄, 即以 NULL 填充. 如, 這允許我們查看每一個在部門里的員工和每一個擁有雇員的部門, 同時, 還能看到不在任何部門的員工以及沒有任何員工的部門. 全連接實例: ~~~ SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ | Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID | | --- | --- | --- | --- | | Smith | 34 | Clerical | 34 | | Jones | 33 | Engineering | 33 | | Robinson | 34 | Clerical | 34 | | *Jasper* | NULL | NULL | NULL | | Steinberg | 33 | Engineering | 33 | | Rafferty | 31 | Sales | 31 | | NULL | NULL | *Marketing* | *35* | 一些數據庫系統(如 MySQL)并不直接支持全連接, 但它們可以通過左右外連接的并集(參:?[union](https://zh.wikipedia.org/w/index.php?title=Union_(SQL)&action=edit&redlink=1 "Union (SQL)（頁面不存在）"))來模擬實現. 和上面等價的實例: ~~~ SELECT * FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION SELECT * FROM employee RIGHT JOIN department ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL ~~~ SQLite 不支持右連接, 全外連接可以按照下面的方式模擬: ~~~ SELECT employee.*, department.* FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL ~~~ ## 自連接自連接就是和自身連接.[[2]](https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)#cite_note-2)?下面的例子是一個很好的說明. ### 示例構建一個查詢, 它試圖找到這樣的記錄: 每條記錄包含兩個雇員, 他們來自于同一個國家. 如果你有兩張雇員表(`Employee`), 那么只要第一張表的雇員和第二張表的雇員在同樣的國家的就行了, 你可以用一個通常的連接(相等連接)操作去得到這個表. 不過, 這里所有雇員信息都在一張單獨的大表里.[[3]](https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)#cite_note-3) 下面一個修改過的雇員表?`Employee`: 雇員表 (Employee) | EmployeeID | LastName | Country | DepartmentID | | --- | --- | --- | --- | | 123 | Rafferty | Australia | 31 | | 124 | Jones | Australia | 33 | | 145 | Steinberg | Australia | 33 | | 201 | Robinson | United States | 34 | | 305 | Smith | United Kingdom | 34 | | 306 | Jasper | United Kingdom | NULL | 示例解決方案的查詢可以寫成如下: ~~~ SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID; ~~~ 它執行后將生成下面的表: 通過 Country 自連接后的雇員表(Employee) | EmployeeID | LastName | EmployeeID | LastName | Country | | --- | --- | --- | --- | | 123 | Rafferty | 124 | Jones | Australia | | 123 | Rafferty | 145 | Steinberg | Australia | | 124 | Jones | 145 | Steinberg | Australia | | 305 | Smith | 306 | Jasper | United Kingdom | 關于這個例子, 請注意: * `F`?和?`S`?是雇員表(employee)的第一個和第二個拷貝的別名 * 條件?`F.Country = S.Country`?排除了在不同國家的雇員的組合. 這個例子僅僅期望得到在相同國家的雇員的組合. * 條件?`F.EmployeeID < S.EmployeeID`?排除了雇員號(`EmployeeID`)相同的組合. * `F.EmployeeID < S.EmployeeID`?排除了重復的組合. 沒有這個條件的話, 將生成類似下面表中的無用數據(僅以 United Kingdom 為例) | EmployeeID | LastName | EmployeeID | LastName | Country | | --- | --- | --- | --- | | 305 | Smith | 305 | Smith | United Kingdom | | 305 | Smith | 306 | Jasper | United Kingdom | | 306 | Jasper | 305 | Smith | United Kingdom | | 306 | Jasper | 306 | Jasper | United Kingdom | 只有當中的兩行滿足最初問題的要求, 第一項和最后一項對于本例來講毫無用處. ## 替代方式外連接查詢得到的結果也可以通過[關聯子查詢](https://zh.wikipedia.org/w/index.php?title=%E5%85%B3%E8%81%94%E5%AD%90%E6%9F%A5%E8%AF%A2&action=edit&redlink=1 "關聯子查詢（頁面不存在）")得到. 例如 ~~~ SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID ~~~ 也可以寫成如下樣子: ~~~ SELECT employee.LastName, employee.DepartmentID, (SELECT department.DepartmentName FROM department WHERE employee.DepartmentID = department.DepartmentID ) FROM employee ~~~ ## 實現 ### 連接算法執行一個連接操作, 存在三種基本的算法. #### [嵌套循環] 類似于C語言編程時的雙重循環。作為外層循環逐行掃描的表，稱為外部輸入表；針對外部輸入表的每一行，要逐行掃描檢查匹配的另一張表，稱為內部輸入表（相當于內層循環）。適用于外部輸入表的行數較少，內部輸入表建立了索引的情形。 #### 合并連接（MERGE JOIN）類似于兩個有序數組的合并。兩個輸入表都在合并列上排序；然后依序對兩張表逐行做連接或舍棄。如果預先建好了索引，合并連接的計算復雜度是線性的。 #### 哈希連接（HASH JOIN）適用于查詢的中間結果，通常是無索引的臨時表；以及中間結果的行數很大時。哈希連接選擇行數較小的輸入表作為生成輸入，對其連接列值應用哈希函數，把其行（的存儲位置）放入哈希桶中。