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 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](https://zh.wikipedia.org/wiki/Bug "Bug")。外连接可能可以避免这种情况.
### 相等链接[[编辑](https://zh.wikipedia.org/w/index.php?title=%E8%BF%9E%E6%8E%A5_(SQL)&action=edit§ion=3 "编辑小节：相等链接")]
相等连接 (**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` 结构并不仅仅是[语法糖](https://zh.wikipedia.org/wiki/%E8%AF%AD%E6%B3%95%E7%B3%96 "语法糖")，上面查询的结果和使用显式谓词得到的查询得到的结果是不同的。特别地，在 `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](https://zh.wikipedia.org/wiki/Oracle "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**)，又称**[笛卡尔连接](https://zh.wikipedia.org/w/index.php?title=%E7%AC%9B%E5%8D%A1%E7%88%BE%E8%BF%9E%E6%8E%A5&action=edit&redlink=1 "笛卡尔连接（页面不存在）")**(**cartesian join**)或**[叉乘](https://zh.wikipedia.org/wiki/%E5%8F%89%E4%B9%98 "叉乘")**(**Product**)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的[笛卡尔积](https://zh.wikipedia.org/wiki/%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF "笛卡尔积")。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.
如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B.
用于交叉连接的 SQL 代码在 `[FROM](https://zh.wikipedia.org/w/index.php?title=From_(SQL)&action=edit&redlink=1 "From (SQL)（页面不存在）")` 列出表名，但并不包含任何过滤的连接谓词.
显式的交叉连接实例:
~~~
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](https://zh.wikipedia.org/w/index.php?title=Union_(SQL)&action=edit&redlink=1 "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 不支持右连接, 全外连接可以按照下面的方式模拟:
~~~
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]](https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)#cite_note-2) 下面的例子是一个很好的说明.
### 示例
构建一个查询, 它试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家. 如果你有两张雇员表(`Employee`), 那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了, 你可以用一个通常的连接(相等连接)操作去得到这个表. 不过, 这里所有雇员信息都在一张单独的大表里.[[3]](https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)#cite_note-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 |
关于这个例子, 请注意:
* `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 |
只有当中的两行满足最初问题的要求, 第一项和最后一项对于本例来讲毫无用处.
## 替代方式
外连接查询得到的结果也可以通过[关联子查询](https://zh.wikipedia.org/w/index.php?title=%E5%85%B3%E8%81%94%E5%AD%90%E6%9F%A5%E8%AF%A2&action=edit&redlink=1 "关联子查询（页面不存在）")得到. 例如
~~~
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
~~~
## 实现
### 连接算法
执行一个连接操作, 存在三种基本的算法.
#### [嵌套循环]
类似于C语言编程时的双重循环。作为外层循环逐行扫描的表，称为外部输入表；针对外部输入表的每一行，要逐行扫描检查匹配的另一张表，称为内部输入表（相当于内层循环）。适用于外部输入表的行数较少，内部输入表建立了索引的情形。
#### 合并连接（MERGE JOIN）
类似于两个有序数组的合并。两个输入表都在合并列上排序；然后依序对两张表逐行做连接或舍弃。如果预先建好了索引，合并连接的计算复杂度是线性的。
#### 哈希连接（HASH JOIN）
适用于查询的中间结果，通常是无索引的临时表；以及中间结果的行数很大时。哈希连接选择行数较小的输入表作为生成输入，对其连接列值应用哈希函数，把其行（的存储位置）放入哈希桶中。
- 数据库
- CAP定理
- 关系模型
- 关系数据库
- NoSQL
- ODBC
- JDBC
- ODBC、JDBC和四种驱动类型
- mysql
- 安装与配置
- CentOS 7 安装 MySQL
- 优化
- 比较全面的MySQL优化参考
- 1、硬件层相关优化
- 1.1、CPU相关
- 1.2、磁盘I/O相关
- 2、系统层相关优化
- 2.1、文件系统层优化
- 2.2、其他内核参数优化
- 3、MySQL层相关优化
- 3.1、关于版本选择
- 3.2、关于最重要的参数选项调整建议
- 3.3、关于Schema设计规范及SQL使用建议
- 3.4、其他建议
- 后记
- Mysql设计与优化专题
- ER图，数据建模与数据字典
- 数据中设计中的范式与反范式
- 字段类型与合理的选择字段类型
- 表的垂直拆分和水平拆分
- 详解慢查询
- mysql的最佳索引攻略
- 高手详解SQL性能优化十条经验
- 优化SQL查询：如何写出高性能SQL语句
- MySQL索引原理及慢查询优化
- 数据库SQL优化大总结之 百万级数据库优化方案
- 数据库性能优化之SQL语句优化1
- 【重磅干货】看了此文，Oracle SQL优化文章不必再看！
- MySQL 对于千万级的大表要怎么优化？
- MySQL 数据库设计总结
- MYSQL性能优化的最佳20+条经验
- 数据操作
- 数据语句操作类型
- DCL
- 修改Mysql数据库名的5种方法
- DML
- 连接
- 连接2
- DDL
- 数据类型
- 字符集
- 表引擎
- 索引
- MySQL理解索引、添加索引的原则
- mysql建索引的几大原则
- 浅谈mysql的索引设计原则以及常见索引的区别
- 常用工具简介
- QA
- MySQL主机127.0.0.1与localhost区别总结
- 视图（view）
- 触发器
- 自定义函数和存储过程的使用
- 事务（transaction）
- 范式与反范式
- 常用函数
- MySQL 数据类型 详解
- Mysql数据库常用分库和分表方式
- 隔离级别
- 五分钟搞清楚MySQL事务隔离级别
- mysql隔离级别及事务传播
- 事务隔离级别和脏读的快速入门
- 数据库引擎中的隔离级别
- 事务隔离级别
- Innodb中的事务隔离级别和锁的关系
- MySQL 四种事务隔离级的说明
- Innodb锁机制：Next-Key Lock 浅谈
- SQL函数和存储过程的区别
- mongo
- MongoDB设置访问权限、设置用户
- redis
- ORM
- mybatis
- $ vs #
- mybatis深入理解(一)之 # 与 $ 区别以及 sql 预编译
- 电商设计
- B2C电子商务系统研发——概述篇
- B2C电子商务系统研发——商品数据模型设计
- B2C电子商务系统研发——商品模块E-R图建模
- B2C电子商务系统研发——商品SKU分析和设计（一）
- B2C电子商务系统研发——商品SKU分析和设计（二）
- 数据库命名规范--通用