連接

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。

```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），它是所有類型的內連接的基礎。把表視為行記錄的集合，交叉連接即返回這兩個集合的笛卡爾積。這其實等價於內連接的連結條件為"永真"，或連接條件不存在。

如果 A 和 B 是兩個集合，它們的交叉連接就記為: A × B。

用於交叉連接的 SQL 代碼在 `FROM` 列出表名，但並不包含任何過濾的連接謂詞。

顯式的交叉連接實例:

```sql
SELECT *
FROM   employee CROSS JOIN department
```

隱式的交叉連接實例:

```sql
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 不匹配，就不會往結果表中生成任何數據。

顯式的內連接實例:

```sql
SELECT *
FROM   employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
```

等價於:

```sql
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），是比較連接（θ連接）的一種特例，它的連接謂詞只用了相等比較。使用其他比較操作符（如 `&lt;`）的不是相等連接。前面的查詢已經展示了一個相等連接的實例:

```sql
SELECT *
FROM employee JOIN department
  ON employee.DepartmentID = department.DepartmentID
```

SQL-92提供了一種可選的簡短符號去表達相等連接，它使用`USING`關鍵字 （Feature ID F402）:

```sql
SELECT *
FROM employee INNER JOIN department
USING (DepartmentID)
```

`USING` 結構並不僅僅是語法糖，上面查詢的結果和使用顯式謂詞得到的查詢得到的結果是不同的。特別地，在 `USING` 部分列出的列（column）將在連接結果的臨時表中只出現一次，且無表名限定列名。在上面的例子中，連接結果的臨時表產生單獨的名為 `DepartmentID` 的列，而不是 `employee.DepartmentID` 或 `department.DepartmentID`。

MS SQL Server和Sybase不支持`USING`語句。

### 自然連接

自然連接比相等連接的進一步特例化。兩表做自然連接時，兩表中的所有名稱相同的列都將被比較，這是隱式的。自然連接得到的結果表中，兩表中名稱相同的列只出現一次。

上面用於內連接的查詢實例可以用自然連接的方式表示如下:

```sql
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）。如果左表的一行在右表中存在多個匹配行，那麼左表的行會複製和右表匹配行一樣的數量，並進行組合生成連接結果。

如，這允許我們去找到雇員的部門時，顯示所有雇員，即使這個雇員還沒有關聯的部門。（在上面的內連接部分由一個相反的例子，沒有關聯的部門號的雇員在結果中是不顯示的）。

左外連接實例:

（相對於內連接增添的行用斜體標出）

```sql
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）。

例如，這允許我們在找每一個雇員以及他的部門資訊時，當這個部門裡沒有任何雇員時，也把部門顯示出來。

右連接的實例:

（相對於內連接增添的行用斜體標出）

```sql
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

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

```sql
SELECT *
FROM department LEFT OUTER JOIN employee
  ON employee.DepartmentID = department.DepartmentID
```

### 全連接

全連接是左右外連接的併集。連接表包含被連接的表的所有記錄，如果缺少匹配的記錄，即以 NULL 填充。

如，這允許我們查看每一個在部門裡的員工和每一個擁有雇員的部門，同時，還能看到不在任何部門的員工以及沒有任何員工的部門。

全連接實例:

```sql
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）來模擬實現。和上面等價的實例:

```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 不支持右連接，全外連接可以按照下面的方式模擬:

```sql
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
```

## 自連接

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

### 示例

構建一個查詢，它試圖找到這樣的記錄: 每條記錄包含兩個雇員，他們來自於同一個國家。如果你有兩張雇員表（`Employee`），那麼只要第一張表的雇員和第二張表的雇員在同樣的國家的就行了，你可以用一個通常的連接（相等連接）操作去得到這個表。不過，這裡所有雇員資訊都在一張單獨的大表里。

下面一個修改過的雇員表 `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

示例解決方案的查詢可以寫成如下:

```sql
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

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

## 替代方式

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

例如

```sql
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM   employee LEFT OUTER JOIN department
          ON employee.DepartmentID = department.DepartmentID
```

也可以寫成如下樣子:

```sql
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 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