連接
SQL中的連接（JOIN）語句用於將資料庫中的兩個或多個表組合起來。由"連接"生成的集合，可以被儲存為表，或者當成表來使用。JOIN 語句的含義是把兩張表的屬性通過它們的值組合在一起。基於 ANSI 標準的 SQL 列出了五種 JOIN 方式：內連接（INNER），全外連接（FULL OUTER），左外連接（LEFT OUTER），右外連接（RIGHT OUTER）和交叉連接（CROSS）。在特定的情況下，一張表（基本表，視圖，或連接表）可以和自身進行連接，成為自連接（self-join）。
程式設計師用 JOIN 謂詞表示要得到"連接"後的集合。如果求值後謂詞為真，組合後的記錄就會按照預期的方式生成，如一個記錄集，或者一張臨時表。
範例用表
編輯通常會對關聯式資料庫進行規格化，以消除重複資訊，例如實體類型具有一對多關係時。例如，一個部門可能與許多雇員相關聯。將部門和員工的單獨表連接起來，可以有效地建立另一個表，將兩個表中的資訊組合在一起。
下文中解釋"連接"都將用到這裡的兩張表。表中的記錄（行）用於演示不同類型的"連接"和"連接謂詞"的作用。在下面兩張表中，Department.DepartmentID 是主鍵，Employee.DepartmentID 是外來鍵。
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Heisenberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| Williams | NULL |
| DepartmentID | DepartmentName |
|---|---|
| 31 | Sales |
| 33 | Engineering |
| 34 | Clerical |
| 35 | Marketing |
注: 雇員 "Williams" 不在 部門表中的任何一個部門。同樣，"市場部" 目前沒有員工列出。
這是用來建立上述表的SQL。
CREATE TABLE department
(
DepartmentID INT Primary key,
DepartmentName VARCHAR(20)
);
CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT references department(DepartmentID)
);
INSERT INTO department VALUES(31, 'Sales');
INSERT INTO department VALUES(33, 'Engineering');
INSERT INTO department VALUES(34, 'Clerical');
INSERT INTO department VALUES(35, 'Marketing');
INSERT INTO employee VALUES('Rafferty', 31);
INSERT INTO employee VALUES('Jones', 33);
INSERT INTO employee VALUES('Heisenberg', 33);
INSERT INTO employee VALUES('Robinson', 34);
INSERT INTO employee VALUES('Smith', 34);
INSERT INTO employee VALUES('Williams', NULL);
交叉連接
編輯交叉連接（cross join），又稱笛卡爾連接（cartesian join）或叉乘（Product），它是所有類型的內連接的基礎。把表視為行記錄的集合，交叉連接即返回這兩個集合的笛卡爾積。[1] 這其實等價於內連接的連結條件為"永真"，或連接條件不存在。
如果 A 和 B 是兩個集合，它們的交叉連接就記為: A × B。
用於交叉連接的 SQL 代碼在 FROM 列出表名，但並不包含任何過濾的連接謂詞。
顯式的交叉連接實例:
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 |
| Heisenberg | 33 | Sales | 31 |
| Smith | 34 | Sales | 31 |
| Robinson | 34 | Sales | 31 |
| Williams | NULL | Sales | 31 |
| Rafferty | 31 | Engineering | 33 |
| Jones | 33 | Engineering | 33 |
| Heisenberg | 33 | Engineering | 33 |
| Smith | 34 | Engineering | 33 |
| Robinson | 34 | Engineering | 33 |
| Williams | NULL | Engineering | 33 |
| Rafferty | 31 | Clerical | 34 |
| Jones | 33 | Clerical | 34 |
| Heisenberg | 33 | Clerical | 34 |
| Smith | 34 | Clerical | 34 |
| Robinson | 34 | Clerical | 34 |
| Williams | NULL | Clerical | 34 |
| Rafferty | 31 | Marketing | 35 |
| Jones | 33 | Marketing | 35 |
| Heisenberg | 33 | Marketing | 35 |
| Smith | 34 | Marketing | 35 |
| Robinson | 34 | Marketing | 35 |
| Williams | NULL | Marketing | 35 |
交叉連接不會應用任何謂詞去過濾結果表中的記錄。程式設計師可以用 WHERE 語句進一步過濾結果集。
在SQL:2011標準中，交叉連接是可選的F401「Extended joined table」包的一部分。
通常用於檢查伺服器的效能。
內連接
編輯
內連接（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 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Smith | 34 | Clerical | 34 |
| Heisenberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
注 雇員 "Williams" 和部門 "市場部" 都未出現。它們在預期得到的表中沒有任何匹配的記錄: "Williams" 沒有關聯的部門，而號碼為35的部門中沒有任何雇員。這樣，在"連接"後的表中，就沒有關於 Williams 或 市場部 的資訊了。相對於預期的結果，這個行為可能是一個微妙的Bug。外連接可能可以避免這種情況。
相等連結
編輯相等連接 （equi-join，或 equijoin），是比較連接（θ連接）的一種特例，它的連接謂詞只用了相等比較。使用其他比較運算子（如 <）的不是相等連接。前面的查詢已經展示了一個相等連接的實例:
SELECT *
FROM employee JOIN department
ON employee.DepartmentID = department.DepartmentID
SQL-92提供了一種可選的簡短符號去表達相等連接，它使用USING關鍵字 （Feature ID F402）:[2]
SELECT *
FROM employee INNER JOIN department
USING (DepartmentID)
USING 結構並不僅僅是語法糖，上面查詢的結果和使用顯式謂詞得到的查詢得到的結果是不同的。特別地，在 USING 部分列出的列（column）將在連接結果的臨時表中只出現一次，且無表名限定列名。在上面的例子中，連接結果的臨時表產生單獨的名為 DepartmentID 的列，而不是 employee.DepartmentID 或 department.DepartmentID。
MS SQL Server和Sybase不支援USING語句。
自然連接
編輯自然連接比相等連接的進一步特例化。兩表做自然連接時，兩表中的所有名稱相同的列都將被比較，這是隱式的。自然連接得到的結果表中，兩表中名稱相同的列只出現一次。
上面用於內連接的查詢實例可以用自然連接的方式表示如下:
SELECT *
FROM employee NATURAL JOIN department
用了 USING 語句後，在連接表中，DepartmentID 列只出現一次，且沒有表名作字首:
| DepartmentID | Employee.LastName | Department.DepartmentName |
|---|---|---|
| 34 | Smith | Clerical |
| 33 | Jones | Engineering |
| 34 | Robinson | Clerical |
| 33 | Heisenberg | Engineering |
| 31 | Rafferty | Sales |
在 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"。
外連接
編輯外連接並不要求連接的兩表的每一條記錄在對方表中都一條匹配的記錄。要保留所有記錄（甚至這條記錄沒有匹配的記錄也要保留）的表稱為保留表。 外連接可依據連接表保留左表，右表或全部表的行而進一步分為左外連接，右外連接和全連接。
（在這種情況下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 |
| Williams | NULL | NULL | NULL |
| Heisenberg | 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 |
| Heisenberg | 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 |
| Williams | NULL | NULL | NULL |
| Heisenberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
一些資料庫系統（如 MySQL）並不直接支援全連接，但它們可以通過左右外連接的併集（參: union）來類比實現。和上面等價的實例:
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
自連接
編輯自連接就是和自身連接。[3] 下面的例子是一個很好的說明。
範例
編輯構建一個查詢，它試圖找到這樣的記錄: 每條記錄包含兩個雇員，他們來自於同一個國家。如果你有兩張雇員表（Employee），那麼只要第一張表的雇員和第二張表的雇員在同樣的國家的就行了，你可以用一個通常的連接（相等連接）操作去得到這個表。不過，這裡所有雇員資訊都在一張單獨的大表里。[4]
下面一個修改過的雇員表 Employee:
| EmployeeID | LastName | Country | DepartmentID |
|---|---|---|---|
| 123 | Rafferty | Australia | 31 |
| 124 | Jones | Australia | 33 |
| 145 | Heisenberg | Australia | 33 |
| 201 | Robinson | United States | 34 |
| 305 | Smith | Germany | 34 |
| 306 | Williams | Germany | 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;
它執行後將生成下面的表:
| EmployeeID | LastName | EmployeeID | LastName | Country |
|---|---|---|---|---|
| 123 | Rafferty | 124 | Jones | Australia |
| 123 | Rafferty | 145 | Heisenberg | Australia |
| 124 | Jones | 145 | Heisenberg | Australia |
| 305 | Smith | 306 | Williams | Germany |
關於這個例子，請注意:
F和S是雇員表（employee）的第一個和第二個拷貝的別名- 條件
F.Country = S.Country排除了在不同國家的雇員的組合。這個例子僅僅期望得到在相同國家的雇員的組合。 - 條件
F.EmployeeID < S.EmployeeID排除了雇員號（EmployeeID）相同的組合。 F.EmployeeID < S.EmployeeID排除了重複的組合。沒有這個條件的話，將生成類似下面表中的無用資料（僅以 Germany 為例）
| EmployeeID | LastName | EmployeeID | LastName | Country |
|---|---|---|---|---|
| 305 | Smith | 305 | Smith | Germany |
| 305 | Smith | 306 | Williams | Germany |
| 306 | Williams | 305 | Smith | Germany |
| 306 | Williams | 306 | Williams | Germany |
只有當中的兩行滿足最初問題的要求，第一項和最後一項對於本例來講毫無用處。
替代方式
編輯外連接查詢得到的結果也可以通過關聯子查詢得到。 例如
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
實現
編輯連接演算法
編輯執行一個連接操作，存在三種基本的演算法。
巢狀迴圈（LOOP JOIN）
編輯巢狀迴圈類似於C語言編程時的雙重迴圈。作為外層迴圈逐行掃描的表，稱為外部輸入表；針對外部輸入表的每一行，要逐行掃描檢查匹配的另一張表，稱為內部輸入表（相當於內層迴圈）。適用於外部輸入表的行數較少，內部輸入表建立了索引的情形。
合併連接（MERGE JOIN）
編輯類似於兩個有序陣列的合併。兩個輸入表都在合併列上排序；然後依序對兩張表逐行做連接或捨棄。如果預先建好了索引，合併連接的計算複雜度是線性的。
雜湊連接（HASH JOIN）
編輯適用於查詢的中間結果，通常是無索引的臨時表；以及中間結果的行數很大時。雜湊連接選擇行數較小的輸入表作為生成輸入，對其連接列值應用雜湊函式，把其行（的儲存位置）放入雜湊桶中。
參見
編輯參考文獻
編輯參照
編輯- ^ SQL CROSS JOIN. [2019-03-30]. （原始內容存檔於2019-03-30）.
- ^ Simplifying Joins with the USING Keyword. [2019-03-30]. （原始內容存檔於2019-08-26）.
- ^ Shah 2005，第165頁
- ^ Adapted from Pratt 2005，第115–6頁
來源
編輯- Pratt, Phillip J., A Guide To SQL, Seventh Edition, Thomson Course Technology, 2005, ISBN 9780619216740
- Shah, Nilesh, Database Systems Using Oracle - A Simplified Guide to SQL and PL/SQL Second Edition International Edition, Pearson Education International, 2005 [2002], ISBN 0131911805
- Yu, Clement T.; Meng, Weiyi, Principles of Database Query Processing for Advanced Applications, Morgan Kaufmann, 1998 [2009-03-03], ISBN 9781558604346
外部連結
編輯- SQL SERVER - Introduction to JOINs - Basic of JOINs （頁面存檔備份，存於網際網路檔案館）
- SQL Inner Join with visual explanation （頁面存檔備份，存於網際網路檔案館）
- Sybase ASE 15 Joins （頁面存檔備份，存於網際網路檔案館）
- MySQL 5.0 Joins （頁面存檔備份，存於網際網路檔案館）
- Oracle Joins - Quick Reference
- PostgreSQL Join with Query Explain
- PostgreSQL 8.3 Joins （頁面存檔備份，存於網際網路檔案館）
- Joins in Microsoft SQL Server
- Joins in MaxDB 7.6 （頁面存檔備份，存於網際網路檔案館）
- Joins in Oracle 11g （頁面存檔備份，存於網際網路檔案館）
- Various join-algorithm implementations
- A Visual Explanation of SQL Joins （頁面存檔備份，存於網際網路檔案館）
- Another visual explanation of SQL joins, along with some set theory （頁面存檔備份，存於網際網路檔案館）
- SQL join types classified with examples （頁面存檔備份，存於網際網路檔案館）
- An alternative strategy to using FULL OUTER JOIN （頁面存檔備份，存於網際網路檔案館）