反饋

連接

（編程術語）

SQL中的連接（JOIN）語句用於將數據庫中的兩個或多個表組合起來。由連接生成的集合, 可以被保存為表, 或者當成表來使用. JOIN 語句的含義是把兩張表的屬性通過它們的值組合在一起. 基於 ANSI 標準的 SQL 列出了五種 JOIN 方式：內連接(INNER), 全外連接(FULL OUTER), 左外連接(LEFT OUTER), 右外連接(RIGHT OUTER)和交叉連接(CROSS). 在特定的情況下, 一張表(基本表, 視圖, 或連接表)可以和自身進行連接, 成為自連接(self-join)。

程序員用 JOIN 謂詞表示要得到連接後的集合。如果evaluated predicate為真，組合後的記錄就會按照預期的方式生成，如一個記錄集，或者一張臨時表，

中文名: 連接

外文名: Join (SQL)
領域: SQL數據庫

連接內連接

內連接(inner join)是應用程序中用的普遍的"連接"操作，它一般都是默認連接類型。內連接基於連接謂詞將兩張表(如 A 和 B)的列組合在一起，產生新的結果表。查詢會將 A 表的每一行和 B 表的每一行進行比較，並找出滿足連接謂詞的組合。當連接謂詞被滿足，A 和 B 中匹配的行會按列組合(並排組合)成結果集中的一行。連接產生的結果集，可以定義為首先對兩張表做笛卡爾積(交叉連接) -- 將 A 中的每一行和 B 中的每一行組合，然後返回滿足連接謂詞的記錄。實際上 SQL 產品會盡可能用其他方式去實現連接，笛卡爾積運算是非常沒效率的.

SQL 定義了兩種不同語法方式去表示"連接"。首先是"顯式連接符號"，它顯式地使用關鍵字JOIN，其次是"隱式連接符號"，它使用所謂的"隱式連接符號"。隱式連接符號把需要連接的表放到SELECT語句的FROM部分，並用逗號隔開。這樣就構成了一個"交叉連接"，WHERE語句可能放置一些過濾謂詞(過濾條件)。那些過濾謂詞在功能上等價於顯式連接符號. SQL 89標準只支持內部連接與交叉連接，因此只有隱式連接這種表達方式；SQL 92標準增加了對外部連接的支持，這才有了JOIN表達式。^[1]

內連接"可以進一步被分為: 相等連接，自然連接，和交叉連接(見下).

程序要應該特別注意連接依據的列可能包含 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。外連接可能可以避免這種情況。

連接相等鏈接

相等連接 (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結構並不僅僅是語法糖，上面查詢的結果和使用顯式謂詞得到的查詢得到的結果是不同的。特別地，在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裏用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)，又稱笛卡爾連接(cartesian join)或叉乘(Product)，它是所有類型的內連接的基礎。把表視為行記錄的集合，交叉連接即返回這兩個集合的笛卡爾積。這其實等價於內連接的鏈接條件為"永真"，或連接條件不存在.

如果 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	銷售部	31
Jones	33	銷售部	31
Steinberg	33	銷售部	31
Smith	34	銷售部	31
Robinson	34	銷售部	31
Jasper	NULL	銷售部	31
Rafferty	31	工程部	33
Jones	33	工程部	33
Steinberg	33	工程部	33
Smith	34	工程部	33
Robinson	34	工程部	33
Jasper	NULL	工程部	33
Rafferty	31	秘書	34
Jones	33	秘書	34
Steinberg	33	秘書	34
Smith	34	秘書	34
Robinson	34	秘書	34
Jasper	NULL	秘書	34
Rafferty	31	市場部	35
Jones	33	市場部	35
Steinberg	33	市場部	35
Smith	34	市場部	35
Robinson	34	市場部	35
Jasper	NULL	市場部	35

交叉連接不會應用任何謂詞去過濾結果表中的記錄。程序員可以用WHERE語句進一步過濾結果集.^[1]

連接外連接

外連接並不要求連接的兩表的每一條記錄在對方表中都一條匹配的記錄。要保留所有記錄（甚至這條記錄沒有匹配的記錄也要保留）的表稱為保留表。外連接可依據連接表保留左表, 右表或全部表的行而進一步分為左外連接, 右外連接和全連接.

(在這種情況下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

實際上顯式的右連接很少使用, 因為它總是可以被替換成左連接--換換表的位置就可以了, 另外, 右連接相對於左連接並沒有什麼額外的功能. 上表同樣可以使用左連接得到:^[1]

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)來模擬實現. 和上面等價的實例:

SELECT *FROM   employee        LEFT JOIN department           ON employee.DepartmentID = department.DepartmentIDUNIONSELECT *FROM   employee       RIGHT JOIN department          ON employee.DepartmentID = department.DepartmentIDWHERE  employee.DepartmentID IS NULL

SQLite 不支持右連接, 全外連接可以按照下面的方式模擬:

SELECT employee.*, department.*FROM   employee        LEFT JOIN department           ON employee.DepartmentID = department.DepartmentIDUNIONSELECT employee.*, department.*FROM   department       LEFT JOIN employee          ON employee.DepartmentID = department.DepartmentIDWHERE  employee.DepartmentID IS NULL

連接自連接

自連接就是和自身連接.下面的例子是一個很好的説明.

示例

構建一個查詢, 它試圖找到這樣的記錄: 每條記錄包含兩個僱員, 他們來自於同一個國家. 如果你有兩張僱員表(Employee), 那麼只要第一張表的僱員和第二張表的僱員在同樣的國家的就行了, 你可以用一個通常的連接(相等連接)操作去得到這個表. 不過, 這裏所有僱員信息都在一張單獨的大表裏.

下面一個修改過的僱員表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.CountryFROM Employee F, Employee SWHERE F.Country = S.CountryAND F.EmployeeID < S.EmployeeIDORDER 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

只有當中的兩行滿足最初問題的要求, 第一項和最後一項對於本例來講毫無用處。^[2]

連接替代方式

外連接查詢得到的結果也可以通過關聯子查詢得到. 例如

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）

適用於查詢的中間結果，通常是無索引的臨時表；以及中間結果的行數很大時。哈希連接選擇行數較小的輸入表作為生成輸入，對其連接列值應用哈希函數，把其行（的存儲位置）放入哈希桶中。^[3]

參考資料

1. Pratt, Phillip J, A Guide To SQL, Seventh Edition, Thomson Course Technology, 2005, ISBN 9780619216740
2. 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
3. Yu, Clement T.; Meng, Weiyi, Principles of Database Query Processing for Advanced Applications, Morgan Kaufmann, 1998 [2009-03-03], ISBN 9781558604346

詞條統計

瀏覽次數：次
編輯次數：11次歷史版本
最近更新：寒927 （2023-04-29）

1 內連接: 1.1 相等鏈接; 1.2 自然連接; 1.3 交叉連接
2 外連接: 2.1 左外連接; 2.2 右外連接; 2.3 全連接
3 自連接
4 替代方式
5 連接算法

連接