本頁使用了標題或全文手工轉換

連線 (SQL)

維基百科,自由的百科全書
跳到: 導覽搜尋

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 NULLIS NOT NULL 等謂詞.

例如,下面的查詢通過 Employee 表和 Department 表共有的屬性 DepartmentID 連線兩表。在兩表 DepartmentID 符合之處(如連線謂詞被滿足),查詢將組合兩表的 LastNameDepartmentIDDepartmentName 等列,把它們放到結果表的一行(一條記錄)里。當 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.DepartmentIDdepartment.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 USINGNATURAL 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 語言中, 外連線沒有隱式的連線符號.

外部連線既包含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)來模擬實現. 和上面等價的例項:

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:

僱員表 (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


關於這個例子, 請注意:

  • FS 是僱員表(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

實現[編輯]

連線演算法[編輯]

執行一個連線操作, 存在三種基本的演算法.

巢狀迴圈(LOOP JOIN)[編輯]

類似於C語言編程時的雙重迴圈。作為外層迴圈逐行掃描的表,稱為外部輸入表;針對外部輸入表的每一行,要逐行掃描檢查符合的另一張表,稱為內部輸入表(相當於內層迴圈)。適用於外部輸入表的行數較少,內部輸入表建立了索引的情形。

合併連線(MERGE JOIN)[編輯]

類似於兩個有序陣列的合併。兩個輸入表都在合併列上排序;然後依序對兩張表逐行做連線或捨棄。如果預先建好了索引,合併連線的計算複雜度是線性的。

雜湊連線(HASH JOIN)[編輯]

適用於查詢的中間結果,通常是無索引的臨時表;以及中間結果的行數很大時。雜湊連線選擇行數較小的輸入表作為生成輸入,對其連線列值應用雜湊函數,把其行(的儲存位置)放入雜湊桶中。

參見[編輯]

註腳[編輯]

  1. ^ SQL Inner Join. www.SQLDatabaseTutorial.com. 
  2. ^ Shah 2005,第165頁
  3. ^ Adapted from Pratt 2005,第115–6頁

參照[編輯]

外部連結[編輯]

數據庫管理系統(DBMS) 檢視 · 討論 · 編輯

概念
數據庫 · 數據模型 · 數據庫儲存結構英語Database storage structures · 關係 (數據庫) · 關係模型 · 分散式數據庫 · ACID ( 原子性英語Atomicity (database systems) · 一致性· 隔離性· 永續性英語Durability (database systems) ) · Null值
關係模型 · 數據庫規範化 · 數據庫完整性 · 實體完整性 · 參照完整性英語Referential integrity · 關聯式資料庫管理系統 
主鍵 · 外來鍵 · 代理鍵 · 超鍵 · 候選鍵 

數據庫元件
觸發器 · 視圖 · 數據庫表 · 指標 (數據庫) · 事務記錄檔英語Transaction log · 數據庫事務 · 並行控制 · 樂觀鎖 · 悲觀鎖 · 數據庫索引 
儲存程式 · 數據庫分割英語Partition (database)

SQL
分類: 數據查詢語言(DQL) - 數據定義語言(DDL) - 數據操縱語言(DML) - 數據控制語言(DCL)
指令:SELECT · INSERT · UPDATE · MERGE · DELETE · JOIN · UNION英語Union (SQL) · CREATE · DROP · Begin work · COMMIT · ROLLBACK · TRUNCATE · ALTER
安全: SQL資料隱碼攻擊 · 參數化查詢

數據庫管理系統的實現

實現類型
關聯式資料庫 · 檔案型數據庫 · Deductive · 維度化數據庫 · 階層式 · 圖形數據庫 · NoSQL· 物件數據庫 · 物件關聯式資料庫 · Temporal · XML數據庫

數據庫產品
物件型對比) · 關係型對比

數據庫元件
數據查詢語言 · 查詢最佳化器 · 查詢計劃 · 嵌入式SQL · ODBC · JDBC · OLE DB