連接
SQL 的連接(JOIN
)語句將數據庫中的兩個或多個表組合起來.[1] 由"連接"生成的集合, 可以被保存為表, 或者當成表來使用. JOIN
語句的含義是把兩張表的屬性通過它們的值組合在一起. 基於 ANSI 標準的 SQL 列出了四種 JOIN
方式: 內連接(INNER
), 外連接(OUTER
), 左外連接(LEFT
), 和右外連接(RIGHT
). 在特定的情況下, 一張表(基本表, 視圖, 或連接表)可以和自身進行連接, 成為自連接(self-join).
程序員用 JOIN
謂詞表示要得到"連接"後的集合. 如果evaluated predicate為真, 組合後的記錄就會按照預期的方式生成, 如一個記錄集, 或者一張臨時表.
示例用表
下文中解釋"連接"都將用到這裡的兩張表. 表中的記錄(行)用於演示不同類型的"連接"和"連接謂詞"的作用. 在下面兩張表中, Department.DepartmentID
是主鍵, Employee.DepartmentID
是外鍵.
LastName | DepartmentID |
---|---|
Rafferty | 31 |
Jones | 33 |
Steinberg | 33 |
Robinson | 34 |
Smith | 34 |
Jasper | NULL |
DepartmentID | 部門 |
---|---|
31 | 銷售部 |
33 | 工程部 |
34 | 書記 |
35 | 市場部 |
注: "市場部" 目前沒有員工列出. 同樣, 雇員 "Jasper" 不在 部門表中的任何一個部門.
內連接
內連接(inner join)是應用程序中用的普遍的"連接"操作,它一般都是默認的連接類型。內連接基於連接謂詞將兩張表(如 A 和 B)的列組合在一起,產生新的結果表。查詢會將 A 表的每一行和 B 表的每一行進行比較,並找出滿足連接謂詞的組合。當連接謂詞被滿足,A 和 B 中匹配的行會按列組合(並排組合)成結果集中的一行。連接產生的結果集,可以定義為首先對兩張表做笛卡爾積(交叉連接) -- 將 A 中的每一行和 B 中的每一行組合,然後返回滿足連接謂詞的記錄。實際上 SQL 產品會儘可能用其他方式去實現連接,笛卡爾積運算是非常沒效率的.
SQL 定義了兩種不同語法方式去表示"連接"。首先是"顯式連接符號",它顯式地使用關鍵字 JOIN
,其次是"隱式連接符號",它使用所謂的"隱式連接符號"。隱式連接符號把需要連接的表放到 SELECT
語句的 FROM
部分,並用逗號隔開。這樣就構成了一個"交叉連接",WHERE
語句可能放置一些過濾謂詞(過濾條件)。那些過濾謂詞在功能上等價於顯式連接符號.
內連接"可以進一步被分為: 相等連接,自然連接,和交叉連接(見下).
程序要應該特別注意連接依據的列可能包含 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 | 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 語言中, 外連接沒有隱式的連接符號.
左外連接
左外連接(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)來模擬實現. 和上面等價的實例:
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] 下面的例子是一個很好的說明.
示例
構建一個查詢, 它試圖找到這樣的記錄: 每條記錄包含兩個雇員, 他們來自於同一個國家. 如果你有兩張雇員表(Employee
), 那麼只要第一張表的雇員和第二張表的雇員在同樣的國家的就行了, 你可以用一個通常的連接(相等連接)操作去得到這個表. 不過, 這裡所有雇員信息都在一張但對大表里.[3]
下面一個修改過的雇員表 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;
它執行後將生成下面的表:
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 |
只有當中的兩行滿足最初問題的要求, 第一項和最後一項對於本例來講毫無用處.
替代方式
外連接查詢得到的結果也可以通過關聯子查詢得到. 例如
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
實現
連接算法
執行一個連接操作, 存在三種基本的算法.
嵌套循環
合併連接
哈希連接
參見
腳註
- ^ SQL Inner Join. www.SQLDatabaseTutorial.com.
- ^ Shah 2005,第165頁
- ^ Adapted from Pratt 2005,第115–6頁
引用
模板參量錯誤!(代碼34)
|
外連接
- 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