跳转到内容

连接:修订间差异

维基百科,自由的百科全书
删除的内容 添加的内容
Iridiumcao留言 | 贡献
 
(未显示44个用户的81个中间版本)
第1行: 第1行:
{{NoteTA
{{translating|time=2009-9-17}}
|G1=IT

}}
[[SQL]] 的连接(<code>JOIN</code>)语句将数据库中的两个或多个表组合起来.<ref>{{cite web|url=http://www.sqldatabasetutorial.com/sqltutorial/sqljoin-innerjoin.aspx|title=SQL Inner Join|publisher=www.SQLDatabaseTutorial.com}}</ref> 由"连接"生成的集合, 可以被保存为表, 或者当成表来使用. <code>JOIN</code> 语句的含义是把两张表的属性通过它们的值组合在一起. 基于 ANSI 标准的 SQL 列出了四种 <code>JOIN</code> 方式: 内连接(<code>INNER</code>), 外连接(<code>OUTER</code>), 左外连接(<code>LEFT</code>), 和右外连接(<code>RIGHT</code>). 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(''self-join'').
[[SQL]]中的连接(<code>JOIN</code>)语句用于将数据库中的两个或多个表组合起来。由"连接"生成的集合,可以被保存为表,或者当成表来使用。<code>JOIN</code> 语句的含义是把两张表的属性通过它们的值组合在一起。基于 ANSI 标准的 SQL 列出了五种 <code>JOIN</code> 方式:内连接(<code>INNER</code>),全外连接(<code>FULL OUTER</code>),左外连接(<code>LEFT OUTER</code>),右外连接(<code>RIGHT OUTER</code>)和交叉连接(<code>CROSS</code>)。在特定的情况下,一张表(基本表,视图,或连接表)可以和自身进行连接,成为自连接({{lang|en|self-join}})。


程序员用 <code>JOIN</code> [[谓词]]表示要得到"连接"后的集合. 如果evaluated predicate为真, 组合后的记录就会按照预期的方式生成, 如一个记录集, 或者一张临时表.
程序员用 <code>JOIN</code> [[谓词]]表示要得到"连接"后的集合如果求值后谓词为真组合后的记录就会按照预期的方式生成如一个记录集或者一张临时表


== 示例用表 ==
== 示例用表 ==
通常会对关系数据库进行[[数据库规范化|规范化]],以消除重复信息,例如实体类型具有一对多关系时。例如,一个部门可能与许多雇员相关联。将部门和员工的单独表连接起来,可以有效地创建另一个表,将两个表中的信息组合在一起。


下文中解释"连接"都将用到这里的两张表. 表中的记录()用于演示不同类型的"连接"和"连接谓词"的作用. 在下面两张表中, <code>Department.DepartmentID</code> 是主键, <code>Employee.DepartmentID</code> 是外键.
下文中解释"连接"都将用到这里的两张表表中的记录用于演示不同类型的"连接"和"连接谓词"的作用在下面两张表中<code>Department.DepartmentID</code> 是[[主键]],<code>Employee.DepartmentID</code> 是外键


{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+雇员表(Employee)
|+雇员表(Employee)
|-
|-
! LastName !! DepartmentID
! LastName !! DepartmentID
第18行: 第20行:
| Jones || 33
| Jones || 33
|-
|-
| Steinberg || 33
| Heisenberg || 33
|-
|-
| Robinson || 34
| Robinson || 34
第24行: 第26行:
| Smith|| 34
| Smith|| 34
|-
|-
| Jasper || {{null result}}
| Williams || {{null result}}
|}
|}


{| class="wikitable" style="text-align:center; float:left; margin-left:5px"
{| class="wikitable" style="text-align:center; float:left; margin-left:5px"
|+部门表(Department)
|+部门表(Department)
|-
|-
! DepartmentID !! DepartmentName
! DepartmentID !! DepartmentName
|-
|-
| 31 || Sales
| 31 || Sales
|-
|-
| 33 || Engineering
| 33 || Engineering
第41行: 第43行:
|}
|}


<br clear="all" />
{{clear}}

注: 雇员 "Williams" 不在 部门表中的任何一个部门。同样,"市場部" 目前没有员工列出。

这是用来创建上述表的SQL。
<syntaxhighlight lang="sql" line="1">
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);
</syntaxhighlight>

== 交叉连接 ==


'''交叉连接'''({{lang|en|cross join}}),又称'''[[笛卡爾连接]]'''({{lang|en|cartesian join}})或'''[[叉乘]]'''({{lang|en|Product}}),它是所有类型的内连接的基础。把表视为行记录的集合,交叉连接即返回这两个集合的[[笛卡尔积]]。<ref>{{Cite web |url=http://www.sqlguides.com/sql_cross_join.php |title=SQL CROSS JOIN |accessdate=2019-03-30 |archive-date=2019-03-30 |archive-url=https://web.archive.org/web/20190330034722/https://www.sqlguides.com/sql_cross_join.php |dead-url=yes }}</ref> 这其实等价于内连接的链接条件为"永真",或连接条件不存在。
注: "Marketing" 部门目前没有员工列出. 同样, 雇员 "Jasper" 不在 Department 表中的任何一个部门.


如果 A 和 B 是两个集合,它们的交叉连接就记为: A × B。
== 内连接 ==

用于交叉连接的 SQL 代码在 <code>[[From (SQL)|FROM]]</code> 列出表名,但并不包含任何过滤的连接谓词。

显式的交叉连接实例:
<syntaxhighlight lang=sql>
SELECT *
FROM employee CROSS JOIN department
</syntaxhighlight>

隐式的交叉连接实例:
<syntaxhighlight lang="sql">
SELECT *
FROM employee, department;
</syntaxhighlight>

{| class="wikitable" style="text-align:center"
|-
! 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 result}} || Sales || 31
|-
| Rafferty || 31 || Engineering || 33
|-
| Jones || 33 || Engineering || 33
|-
| Heisenberg || 33 || Engineering || 33
|-
| Smith || 34 || Engineering || 33
|-
| Robinson || 34 || Engineering || 33
|-
| Williams || {{null result}} || Engineering || 33
|-
| Rafferty || 31 || Clerical || 34
|-
| Jones || 33 || Clerical || 34
|-
| Heisenberg || 33 || Clerical || 34
|-
| Smith || 34 || Clerical || 34
|-
| Robinson || 34 || Clerical || 34
|-
| Williams || {{null result}} || Clerical || 34
|-
| Rafferty || 31 || Marketing || 35
|-
| Jones || 33 || Marketing || 35
|-
| Heisenberg || 33 || Marketing || 35
|-
| Smith || 34 || Marketing || 35
|-
| Robinson || 34 || Marketing || 35
|-
| Williams || {{null result}} || Marketing || 35
|}


交叉连接不会应用任何谓词去过滤结果表中的记录。程序员可以用 <code>WHERE</code> 语句进一步过滤结果集。
'''内连接'''('''inner join''')是应用程序中用的普遍的"连接"操作, 它一般都是默认的连接类型. 内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起, 产生新的结果表. 查询会将 A 表的每一行和 B 表的每一行进行比较, 并找出满足连接谓词的组合. 当连接谓词被满足, A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行. 连接产生的结果集, 可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合, 然后返回满足连接谓词的记录. 实际上 SQL 产品会尽可能用其他方式去实现连接, 笛卡尔积运算是非常没效率的.


在SQL:2011标准中,交叉连接是可选的F401“Extended joined table”包的一部分。
SQL 定义了两种不同语法方式去表示"连接". 首先是"显示连接符号", 它显示地使用关键字 <code>JOIN</code>, 其次是"隐式连接符号", 它使用所谓的"隐式连接符号". 隐式连接符号把需要连接的表放到 <code>SELECT</code> 语句的 <code>FROM</code> 部分, 并用逗号隔开. 这样就构成了一个"交叉连接", <code>WHERE</code> 语句可能放置一些过滤谓词(过滤条件). 那些过滤谓词在功能上等价于显式连接符号.


通常用于检查服务器的性能。
内连接"可以进一步被分为: 相等连接, 自然连接, 和交叉连接(见下).


== 内连接 ==
程序要应该特别注意连接依据的列可能包含 NULL 值, NULL 值不与任何值匹配(甚至和它本身) -- 除非连接条件中显式地使用 <code>IS NULL</code> 或 <code>IS NOT NULL</code> 等谓词.
[[File:SQL Join - 07 A Inner Join B.svg|alt=显示内部重叠部分填充的维恩图。|thumb|表示表A和表B之间的内部连接SQL语句的维恩图。]]
'''内连接'''({{lang|en|inner join}})是应用程序中用的普遍的"连接"操作,它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起,产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较,并找出满足连接谓词的组合。当连接谓词被满足,A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集,可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合,然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接,笛卡尔积运算是非常没效率的。


SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号",它显式地使用关键字 <code>JOIN</code>,其次是"隐式连接符号",它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 <code>SELECT</code> 语句的 <code>FROM</code> 部分,并用逗号隔开。这样就构成了一个"交叉连接",<code>WHERE</code> 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号。SQL 89标准只支持内部连接与交叉连接,因此只有隐式连接这种表达方式;SQL 92标准增加了对外部连接的支持,这才有了<code>JOIN</code>表达式。
例如, 下面的查询通过 Employee 表和 Department 表共有的属性 DepartmentID 连接两表. 在两表 DepartmentID 匹配之处(如连接谓词被满足), 查询将组合两表的 ''LastName'', ''DepartmentID'' 和''DepartmentName'' 等列, 把它们放到结果表的一行(一条记录)里. 当 DepartmentID 不匹配, 就不会往结果表中生成任何数据.

内连接"可以进一步被分为: 相等连接,自然连接,和交叉连接(见下)。

程序要应该特别注意连接依据的列可能包含 NULL 值,NULL 值不与任何值匹配(甚至和它本身) -- 除非连接条件中显式地使用 <code>IS NULL</code> 或 <code>IS NOT NULL</code> 等谓词。

例如,下面的查询通过 Employee 表和 Department 表共有的属性 DepartmentID 连接两表。在两表 DepartmentID 匹配之处(如连接谓词被满足),查询将组合两表的 ''LastName'',''DepartmentID'' 和''DepartmentName'' 等列,把它们放到结果表的一行(一条记录)里。当 DepartmentID 不匹配,就不会往结果表中生成任何数据。


显式的内连接实例:
显式的内连接实例:
<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM employee
FROM employee
INNER JOIN department
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


等价于:
等价于:
<source lang=sql>
<syntaxhighlight lang="sql">
SELECT *
SELECT *
FROM employee, department
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


显式的内连接的输出结果:
显式的内连接的输出结果:
第84行: 第192行:
| Smith || 34 || Clerical || 34
| Smith || 34 || Clerical || 34
|-
|-
| Steinberg || 33 || Engineering || 33
| Heisenberg || 33 || Engineering || 33
|-
|-
| Rafferty || 31 || Sales || 31
| Rafferty || 31 || Sales || 31
第90行: 第198行:
|}
|}


'''注''' 雇员 "Jasper" 和部门 "Marketing" 都未出现. 它们在预期得到的表中没有任何匹配的记录: "Jasper" 没有关联的部门, 而号码为35的部门中没有任何雇员. 这样, 在"连接"后的表中, 就没有关于 JasperMarketing 的信息了. 相对于预期的结果, 这个行为可能是一个微妙的臭虫(bug). 外连接可能可以避免这种情况.
'''注''' 雇员 "Williams" 和部门 "市場部" 都未出现它们在预期得到的表中没有任何匹配的记录: "Williams" 没有关联的部门而号码为35的部门中没有任何雇员这样在"连接"后的表中就没有关于 Williams市場部 的信息了相对于预期的结果这个行为可能是一个微妙的[[Bug]]。外连接可能可以避免这种情况


==== 相等链接 ====
=== 相等链接 ===


相等连接 ('''equi-join''', 或 '''equijoin'''), 是比较连接(''θ连接'')的一种特例, 它的连接谓词只用了相等比较. 使用其他比较操作符(如 <code>&lt;</code>)的不是相等连接. 前面的查询已经展示了一个相等连接的实例:
相等连接 '''equi-join'''或 '''equijoin'''),是比较连接''θ连接''的一种特例它的连接谓词只用了相等比较使用其他比较操作符如 <code>&lt;</code>的不是相等连接前面的查询已经展示了一个相等连接的实例:
<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM employee
FROM employee JOIN department
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
ON employee.DepartmentID = department.DepartmentID
</source>


SQL-92提供了一种可选的简短符号去表达相等连接,它使用<code>USING</code>关键字 (Feature ID F402):<ref>{{Cite web |url=http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm |title=Simplifying Joins with the USING Keyword |accessdate=2019-03-30 |archive-date=2019-08-26 |archive-url=https://web.archive.org/web/20190826065040/http://www.java2s.com/Tutorial/Oracle/0140__Table-Joins/SimplifyingJoinswiththeUSINGKeyword.htm |dead-url=yes }}</ref>
SQL 提供了一种可选的简短符号去表达相等连接, 它使用 <code>USING</code> 关键字 (Feature ID F402):
<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM employee
FROM employee INNER JOIN department
USING (DepartmentID)
INNER JOIN department
</syntaxhighlight>
USING (DepartmentID)
</source>


<code>USING</code> 结构并不仅仅是[[语法糖]], 上面查询的结果和使用显式谓词得到的查询得到的结果是不同的. 特别地, 在 <code>USING</code> 部分列出的列(column)只出现一次, 名称无表名修饰.在上面的例子中, 将产生单独的名为 <code>DepartmentID</code> 的列, 而不是 <code>employee.DepartmentID</code> 或 <code>department.DepartmentID</code>.
<code>USING</code> 结构并不仅仅是[[语法糖]]上面查询的结果和使用显式谓词得到的查询得到的结果是不同的特别地在 <code>USING</code> 部分列出的列(column)在连接结果的临时表中只出现一次且无表名限定列名。在上面的例子中,连接结果的临时表产生单独的名为 <code>DepartmentID</code> 的列而不是 <code>employee.DepartmentID</code> 或 <code>department.DepartmentID</code>


MS SQL Server和Sybase不支持<code>USING</code>语句。
<code>USING</code> 语句先以被 MySQL, Oracle, PostgreSQL, SQLite, 和 DB2/400 等产品支持.


==== 自然连接 ====
=== 自然连接 ===


[[自然连接]]比相等连接的进一步特例化. 两表做自然连接时, 两表中的所有名称相同的列都将被比较, 这是隐式的. 自然连接得到的结果表中, 两表中名称相同的列只出现一次.
[[自然连接]]比相等连接的进一步特例化两表做自然连接时两表中的所有名称相同的列都将被比较这是隐式的自然连接得到的结果表中两表中名称相同的列只出现一次


上面用于内连接的查询实例可以用自然连接的方式表示如下:
上面用于内连接的查询实例可以用自然连接的方式表示如下:
<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM employee NATURAL JOIN department
FROM employee NATURAL JOIN department
</syntaxhighlight>
</source>


用了 <code>USING</code> 语句后, 在连接表中, DepartmentID 列只出现一次, 且没有表名作前缀:
用了 <code>USING</code> 语句后在连接表中,DepartmentID 列只出现一次且没有表名作前缀:


{| class="wikitable" style="text-align:center"
{| class="wikitable" style="text-align:center"
第136行: 第242行:
| 34 || Robinson || Clerical
| 34 || Robinson || Clerical
|-
|-
| 33 || Steinberg || Engineering
| 33 || Heisenberg || Engineering
|-
|-
| 31 || Rafferty || Sales
| 31 || Rafferty || Sales
|}
|}


在 [[Oracle]] 里用 <code>JOIN USING</code> 或 <code>NATURAL JOIN</code> 时, 如果两表共有的列的名称前加上某表名作为前缀, 则会报编译错误: "ORA-25154: column part of USING clause cannot have qualifier" 或 "ORA-25155: column used in NATURAL join cannot have qualifier".
在 [[Oracle]] 里用 <code>JOIN USING</code> 或 <code>NATURAL JOIN</code> 时如果两表共有的列的名称前加上某表名作为前缀则会报编译错误: "ORA-25154: column part of USING clause cannot have qualifier" 或 "ORA-25155: column used in NATURAL join cannot have qualifier"


==== 交叉连接 ====
== 连接 ==
[[外连接]]并不要求连接的两表的每一条记录在对方表中都一条匹配的记录。要保留所有记录(甚至这条记录没有匹配的记录也要保留)的表称为'''保留表'''。 外连接可依据连接表保留左表,右表或全部表的行而进一步分为左外连接,右外连接和全连接。


(在这种情况下''left''<''左''> 和 ''right''<''右''> 表示 <code>JOIN</code> 关键字的两边。)
'''交叉连接'''('''cross join'''), 又称'''[[笛卡尔连接]]'''('''cartesian join''')或'''[[叉乘]]'''('''Product'''), 它是所有类型的内连接的基础. 把表视为行记录的集合, 交叉连接即返回这两个集合的[[笛卡尔积]]. 这其实等价于内连接的链接条件为"永真", 或连接条件不存在.


在标准的 SQL 语言中,外连接没有隐式的连接符号。
如果 A 和 B 是两个集合, 它们的交叉连接就记为: A × B.


外部连接既包含ON子句又包含WHERE子句时,应当只把表之间的连接条件写在ON子句中,对表中数据的筛选必须写在WHERE子句中。而内部连接的各条件表达式既可以放在ON子句又可以放在WHERE子句中。这是因为对于外部连接,保留表中被ON子句筛除掉的行要被添加回来,在此操作之后才会用WHERE子句去筛选连接结果中的各行。
用于交叉连接的 SQL 代码在 <code>[[From (SQL)|FROM]]</code> 列出表名, 但并不包含任何过滤的连接谓词.

显式的交叉连接实例:
<source lang=sql>
SELECT *
FROM employee CROSS JOIN department
</source>

隐式的交叉连接实例:
<source lang=sql>
SELECT *
FROM employee, department;
</source>

{| class="wikitable" style="text-align:center"
|-
! 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 result}} || Sales || 31
|-
| Rafferty || 31 || Engineering || 33
|-
| Jones || 33 || Engineering || 33
|-
| Steinberg || 33 || Engineering || 33
|-
| Smith || 34 || Engineering || 33
|-
| Robinson || 34 || Engineering || 33
|-
| Jasper || {{null result}} || Engineering || 33
|-
| Rafferty || 31 || Clerical || 34
|-
| Jones || 33 || Clerical || 34
|-
| Steinberg || 33 || Clerical || 34
|-
| Smith || 34 || Clerical || 34
|-
| Robinson || 34 || Clerical || 34
|-
| Jasper || {{null result}} || Clerical || 34
|-
| Rafferty || 31 || Marketing || 35
|-
| Jones || 33 || Marketing || 35
|-
| Steinberg || 33 || Marketing || 35
|-
| Smith || 34 || Marketing || 35
|-
| Robinson || 34 || Marketing || 35
|-
| Jasper || {{null result}} || Marketing || 35
|}

交叉连接不会应用任何谓词去过滤结果表中的记录. 程序员可以用 <code>WHERE</code> 语句进一步过滤结果集.

== 外连接 ==
[[外连接]]并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 -- 甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.

(在这种情况下''left''<''左''> 和 ''right''<''右''> 表示 <code>JOIN</code> 关键字的两边.)

在标准的 SQL 语言中, 外连接没有隐式的连接符号.


=== 左外连接 ===
=== 左外连接 ===
[[File:SQL Join - 01 A Left Join B.svg|alt=显示左边圆圈和重叠部分填充的维恩图。|thumb|表示表A和表B之间的左连接SQL语句的维恩图。]]
'''左外连接'''({{lang|en|left outer join}}),亦简称为'''左连接'''({{lang|en|left join}}),若 A 和 B 两表进行左外连接,那么结果表中将包含"左表"(即表 A)的所有记录,即使那些记录在"右表" B 没有符合连接条件的匹配。这意味着即使 <code>ON</code> 语句在 B 中的匹配项是0条,连接操作还是会返回一条记录,只不过这条记录中来自于 B 的每一列的值都为 NULL。这意味着'''左外连接'''会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录,来自于右表的所有列的值设为 NULL)。如果左表的一行在右表中存在多个匹配行,那么左表的行会复制和右表匹配行一样的数量,并进行组合生成连接结果。


如,这允许我们去找到雇员的部门时,显示所有雇员,即使这个雇员还没有关联的部门。(在上面的内连接部分由一个相反的例子,没有关联的部门号的雇员在结果中是不显示的)。
The result of a ''left outer join'' (or simply '''left join''') for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the <code>ON</code> clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a '''left outer join''' returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.


左外连接实例:
For example, this allows us to find an employee's department, but still shows the employee(s) even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments are excluded from the result).
(相对于内连接增添的行用斜体标出)


<syntaxhighlight lang=sql>
Example of a left outer join, with the additional result row italicized:
SELECT *

FROM employee LEFT OUTER JOIN department
<source lang=sql>
ON employee.DepartmentID = department.DepartmentID;
SELECT *
</syntaxhighlight>
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
</source>


{| class="wikitable" style="text-align:center"
{| class="wikitable" style="text-align:center"
第250行: 第284行:
| Smith || 34 || Clerical || 34
| Smith || 34 || Clerical || 34
|-
|-
| ''Jasper'' || {{null result}} || {{null result}} || {{null result}}
| ''Williams'' || {{null result}} || {{null result}} || {{null result}}
|-
|-
| Steinberg || 33 || Engineering || 33
| Heisenberg || 33 || Engineering || 33
|}
|}


=== 右外连接 ===
=== 右外连接 ===
[[File:SQL Join - 03 A Right Join B.svg|alt=显示右边圆圈和重叠部分填充的维恩图。|thumb|表示表A和表B之间的右连接SQL语句的维恩图。
]]
'''右外连接''',亦简称'''右连接''',它与左外连接完全类似,只不过是作连接的表的顺序相反而已。如果 A 表右连接 B 表,那么"右表" B 中的每一行在连接表中至少会出现一次。如果 B 表的记录在"左表" A 中未找到匹配行,连接表中来源于 A 的列的值设为 NULL。


右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的,来源于左表的列值设为 NULL)。
A '''[[right outer join]]''' (or '''[[right join]]''') closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.


例如,这允许我们在找每一个雇员以及他的部门信息时,当这个部门里没有任何雇员时,也把部门显示出来。
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).


右连接的实例:
For example, this allows us to find each employee and his or her department, but still show departments that have no employees.
(相对于内连接增添的行用斜体标出)


<syntaxhighlight lang=sql>
Example right outer join, with the additional result row italicized:

<source lang=sql>
SELECT *
SELECT *
FROM employee RIGHT OUTER JOIN department
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


{| class="wikitable" style="text-align:center"
{| class="wikitable" style="text-align:center"
第280行: 第316行:
| Robinson || 34 || Clerical || 34
| Robinson || 34 || Clerical || 34
|-
|-
| Steinberg || 33 || Engineering || 33
| Heisenberg || 33 || Engineering || 33
|-
|-
| Rafferty || 31 || Sales || 31
| Rafferty || 31 || Sales || 31
第287行: 第323行:
|}
|}


实际上显式的右连接很少使用,因为它总是可以被替换成左连接--换换表的位置就可以了,另外,右连接相对于左连接并没有什么额外的功能。上表同样可以使用左连接得到:
In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins (with the table order switched) and provide no additional functionality. The result above is produced also with a left outer join:


<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM department LEFT OUTER JOIN employee
FROM department LEFT OUTER JOIN employee
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


=== 全连接 ===
=== 全连接 ===
[[File:SQL Join - 05b A Full Join B.svg|alt=显示右圆、左圆和重叠部分填充的维恩图。|thumb|表示表A和表B之间的全连接SQL语句的维恩图。]]
'''全连接'''是左右外连接的并集。连接表包含被连接的表的所有记录,如果缺少匹配的记录,即以 NULL 填充。


如,这允许我们查看每一个在部门里的员工和每一个拥有雇员的部门,同时,还能看到不在任何部门的员工以及没有任何员工的部门。
A '''[[full outer join]]''' combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.


全连接实例:
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.


<syntaxhighlight lang=sql>
Example full outer join:

<source lang=sql>
SELECT *
SELECT *
FROM employee
FROM employee
FULL OUTER JOIN department
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


{| class="wikitable" style="text-align:center"
{| class="wikitable" style="text-align:center"
第319行: 第355行:
| Robinson || 34 || Clerical || 34
| Robinson || 34 || Clerical || 34
|-
|-
| ''Jasper'' || {{null result}} || {{null result}} || {{null result}}
| ''Williams'' || {{null result}} || {{null result}} || {{null result}}
|-
|-
| Steinberg || 33 || Engineering || 33
| Heisenberg || 33 || Engineering || 33
|-
|-
| Rafferty || 31 || Sales || 31
| Rafferty || 31 || Sales || 31
第328行: 第364行:
|}
|}


一些数据库系统(如 MySQL)并不直接支持全连接,但它们可以通过左右外连接的并集(参: [[Union (SQL)|union]])来模拟实现。和上面等价的实例:
Some database systems (like MySQL) do not support this functionality directly, but they can emulate it through the use of left and right outer joins and [[Union (SQL)|union]]s. The same example can appear as follows:

<source lang=sql>
<syntaxhighlight lang=sql>
SELECT *
SELECT *
FROM employee
FROM employee
第340行: 第377行:
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
WHERE employee.DepartmentID IS NULL
</syntaxhighlight>
</source>


SQLite 不支持右连接,全外连接可以按照下面的方式模拟:
SQLite does not support right join, so outer join can be emulated as follows:


<source lang=sql>
<syntaxhighlight lang=sql>
SELECT employee.*, department.*
SELECT employee.*, department.*
FROM employee
FROM employee
第355行: 第392行:
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
WHERE employee.DepartmentID IS NULL
</syntaxhighlight>
</source>


== 自连接 ==
== 自连接 ==


A self-join is joining a table to itself.<ref>{{Harvnb|Shah|2005|p=165}}</ref> This is best illustrated by the following example.
自连接就是和自身连接。<ref>{{Harvnb|Shah|2005|p=165}}</ref> 下面的例子是一个很好的说明。


=== 示例 ===
=== 示例 ===


构建一个查询,它试图找到这样的记录: 每条记录包含两个雇员,他们来自于同一个国家。如果你有两张雇员表(<code>Employee</code>),那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了,你可以用一个通常的连接(相等连接)操作去得到这个表。不过,这里所有雇员信息都在一张单独的大表里。<ref> Adapted from {{harvnb|Pratt|2005|pp=115–6}}</ref>
A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table. <ref> Adapted from {{harvnb|Pratt|2005|pp=115&ndash;6}}</ref>


Considering a modified <code>Employee</code> table such as the following:
下面一个修改过的雇员表 <code>Employee</code>:
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+雇员表 (Employee)
|+Employee Table
|-
|-
! EmployeeID !! LastName !! Country !! DepartmentID
! EmployeeID !! LastName !! Country !! DepartmentID
第375行: 第412行:
| 124 || Jones || Australia || 33
| 124 || Jones || Australia || 33
|-
|-
| 145 || Steinberg || Australia || 33
| 145 || Heisenberg || Australia || 33
|-
|-
| 201 || Robinson || United States || 34
| 201 || Robinson || United States || 34
|-
|-
| 305 || Smith || United Kingdom || 34
| 305 || Smith || Germany || 34
|-
|-
| 306 || Jasper || United Kingdom || {{null result}}
| 306 || Williams || Germany || {{null result}}
|}
|}
<br clear="all" />
{{clear}}


示例解决方案的查询可以写成如下:
An example solution query could be as follows:


<source lang=sql>
<syntaxhighlight lang=sql>
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
FROM Employee F, Employee S
第393行: 第430行:
AND F.EmployeeID < S.EmployeeID
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
ORDER BY F.EmployeeID, S.EmployeeID;
</syntaxhighlight>
</source>


它执行后将生成下面的表:
Which results in the following table being generated.
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+通过 Country 自连接后的雇员表(Employee)
|+Employee Table after Self-join by Country
|-
|-
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
第403行: 第440行:
| 123 || Rafferty || 124 || Jones || Australia
| 123 || Rafferty || 124 || Jones || Australia
|-
|-
| 123 || Rafferty || 145 || Steinberg || Australia
| 123 || Rafferty || 145 || Heisenberg || Australia
|-
|-
| 124 || Jones || 145 || Steinberg || Australia
| 124 || Jones || 145 || Heisenberg || Australia
|-
|-
| 305 || Smith || 306 || Jasper || United Kingdom
| 305 || Smith || 306 || Williams || Germany
|}
|}
<br clear="all" />
{{clear}}
关于这个例子,请注意:
For this example, note that:
*<code>F</code> and <code>S</code> are [[alias (SQL)|aliases]] for the first and second copies of the employee table.
* <code>F</code> <code>S</code> 是雇员表(employee)的第一个和第二个拷贝的别名
*The condition <code>F.Country = S.Country</code> excludes pairings between employees in different countries. The example question only wanted pairs of employees in the same country.
* 条件 <code>F.Country = S.Country</code> 排除了在不同国家的雇员的组合。这个例子仅仅期望得到在相同国家的雇员的组合。
*The condition <code>F.EmployeeID < S.EmployeeID</code> excludes pairings where the <code>EmployeeID</code>s are the same.
* 条件 <code>F.EmployeeID < S.EmployeeID</code> 排除了雇员号(<code>EmployeeID</code>)相同的组合。
*<code>F.EmployeeID < S.EmployeeID</code> also excludes duplicate pairings. Without it only the following less useful part of the table would be generated (for the United Kingdom only shown):
* <code>F.EmployeeID < S.EmployeeID</code> 排除了重复的组合。没有这个条件的话,将生成类似下面表中的无用数据(仅以 Germany 为例)

{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
|-
|-
| 305 || Smith || 305 || Smith || United Kingdom
| 305 || Smith || 305 || Smith || Germany
|-
|-
| 305 || Smith || 306 || Jasper || United Kingdom
| 305 || Smith || 306 || Williams || Germany
|-
|-
| 306 || Jasper || 305 || Smith || United Kingdom
| 306 || Williams || 305 || Smith || Germany
|-
|-
| 306 || Jasper || 306 || Jasper || United Kingdom
| 306 || Williams || 306 || Williams || Germany
|}
|}
<br clear="all" />
{{clear}}
只有当中的两行满足最初问题的要求,第一项和最后一项对于本例来讲毫无用处。
Only one of the two middle pairings is needed to satisfy the original question, and the topmost and bottommost are of no interest at all in this example.


== 替方式 ==
== 替方式 ==


外连接查询得到的结果也可以通过[[关联子查询]]得到。
The effect of outer joins can also be obtained using [[correlated subquery|correlated subqueries]]. For example
例如


<source lang=sql>
<syntaxhighlight lang=sql>
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee LEFT OUTER JOIN department
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
ON employee.DepartmentID = department.DepartmentID
</syntaxhighlight>
</source>


也可以写成如下样子:
can also be written as


<source lang=sql>
<syntaxhighlight lang=sql>
SELECT employee.LastName, employee.DepartmentID,
SELECT employee.LastName, employee.DepartmentID,
(SELECT department.DepartmentName
(SELECT department.DepartmentName
第447行: 第486行:
WHERE employee.DepartmentID = department.DepartmentID )
WHERE employee.DepartmentID = department.DepartmentID )
FROM employee
FROM employee
</syntaxhighlight>
</source>


== 实现 ==
== 实现 ==

Much work in database-systems has aimed at efficient implementation of joins, because relational systems commonly call for joins, yet face difficulties in optimising their efficient execution. The problem arises because (inner) joins operate both [[commutative]]ly and [[associative]]ly. In practice, this means that the user merely supplies the list of tables for joining and the join conditions to use, and the database system has the task of determining the most efficient way to perform the operation. A [[query optimizer]] determines how to execute a query containing joins. A query optimizer has two basic freedoms:

# '''Join order''': Because joins function commutatively and associatively, the order in which the system joins tables does not change the final result-set of the query. However, join-order '''does''' have an enormous impact on the cost of the join operation, so choosing the best join order becomes very important.
# '''Join method''': Given two tables and a join condition, multiple [[algorithm]]s can produce the result-set of the join. Which algorithm runs most efficiently depends on the sizes of the input tables, the number of rows from each table that match the join condition, and the operations required by the rest of the query.

Many join-algorithms treat their inputs differently. One can refer to the inputs to a join as the "outer" and "inner" join operands, or "left" and "right", respectively. In the case of nested loops, for example, the database system will scan the entire inner relation for each row of the outer relation.

One can classify query-plans involving joins as follows:<ref name=Yu1998>{{Harvnb|Yu|Meng|1998|p=213}}
</ref>

; left-deep : using a base table (rather than another join) as the inner operand of each join in the plan
; right-deep : using a base table as the outer operand of each join in the plan
; bushy : neither left-deep nor right-deep; both inputs to a join may themselves result from joins

These names derive from the appearance of the [[query plan]] if drawn as a [[Tree data structure|tree]], with the outer join relation on the left and the inner relation on the right (as convention dictates).


=== 连接算法 ===
=== 连接算法 ===


执行一个连接操作,存在三种基本的算法。
Three fundamental algorithms exist for performing a join operation.

==== Nested loops ====

{{main2|Nested loop join|block nested loop}}

Use of nested loops produces the simplest join-algorithm. For each [[tuple]] in the outer join relation, the system scans the entire inner-join relation and appends any tuples that match the join-condition to the result set. Naturally, this algorithm performs poorly with large join-relations: inner or outer or both. An [[index (database)|index]] on columns in the inner relation in the join-predicate can enhance performance.

The block nested loops (BNL) approach offers a refinement to this technique: for every block in the outer relation, the system scans the entire inner relation. For each match between the current inner tuple and one of the tuples in the current block of the outer relation, the system adds a tuple to the join result-set. This variant means doing more computation for each tuple of the inner relation, but far fewer scans of the inner relation.

==== Merge join ====

If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus:

:# Consider the current "group" of tuples from the inner relation; a group consists of a set of contiguous tuples in the inner relation with the same value in the join attribute.
:# For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.

Merge joins offer one reason why many optimizers keep track of the sort order produced by query plan operators—if one or both input relations to a merge join arrives already sorted on the join attribute, the system need not perform an additional sort. Otherwise, the DBMS will need to perform the sort, usually using an [[external sort]] to avoid consuming too much memory.

{{seealso|Sort-Merge Join}}

==== Hash join ====


==== 嵌套-{}-循环(LOOP JOIN) ====
{{main|Hash join}}
[[嵌套循环连接|嵌套-{}-循环]]类似于C语言编程时的双重循环。作为外层循环逐行扫描的表,称为外部输入表;针对外部输入表的每一行,要逐行扫描检查匹配的另一张表,称为内部输入表(相当于内层循环)。适用于外部输入表的行数较少,内部输入表建立了索引的情形。


==== 合并连接(MERGE JOIN) ====
A hash join algorithm can only produce equi-joins. The database system pre-forms access to the tables concerned by building [[hash table]]s on the join-attributes. The lookup in hash tables operates much faster than through index trees. However, one can compare hashed values only for equality, not for other relationships.
类似于两个有序数组的合并。两个输入表都在合并列上排序;然后依序对两张表逐行做连接或舍弃。如果预先建好了索引,合并连接的计算复杂度是线性的。
==== 哈希连接(HASH JOIN) ====
适用于查询的中间结果,通常是无索引的临时表;以及中间结果的行数很大时。哈希连接选择行数较小的输入表作为生成输入,对其连接列值应用哈希函数,把其行(的存储位置)放入哈希桶中。


== See also ==
== 参见 ==


* [[Composition of relations]]
* {{le|Composition of relations}}


== Notes ==
== 参考文献 ==
=== 引用 ===
{{reflist|2}}
{{Reflist|30em}}


{{More footnotes|date=2009年4月}}
== References ==


=== 来源 ===
{{More footnotes|date=April 2009}}
{{refbegin}}
*{{citation
* {{citation
|last= Pratt
|last= Pratt
|first= Phillip J
|first= Phillip J.
|title= A Guide To SQL, Seventh Edition
|title= A Guide To SQL, Seventh Edition
|publisher= Thomson Course Technology
|publisher= Thomson Course Technology
第514行: 第522行:
|isbn= 9780619216740
|isbn= 9780619216740
}}
}}
*{{citation
* {{citation
|last=Shah
|last=Shah
|first= Nilesh
|first= Nilesh
第524行: 第532行:
|isbn= 0131911805
|isbn= 0131911805
}}
}}
*{{citation
* {{citation
|url= http://books.google.com/books?id=aBHRDhrrehYC
|url = http://books.google.com/books?id=aBHRDhrrehYC
|accessdate= 2009-03-03
|accessdate= 2009-03-03
|title= Principles of Database Query Processing for Advanced Applications
|title= Principles of Database Query Processing for Advanced Applications
第536行: 第544行:
|isbn= 9781558604346
|isbn= 9781558604346
}}
}}
{{refend}}


== External links ==
== 外部链接 ==
* [http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ SQL SERVER - Introduction to JOINs - Basic of JOINs]
* [http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ SQL SERVER - Introduction to JOINs - Basic of JOINs] {{Wayback|url=http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ |date=20100213135210 }}
* [http://www.sqldatabasetutorial.com/sqltutorial/sqljoin-innerjoin.aspx SQL Inner Join with visual explanation]
* [http://www.sqldatabasetutorial.com/sqltutorial/sqljoin-innerjoin.aspx SQL Inner Join with visual explanation] {{Wayback|url=http://www.sqldatabasetutorial.com/sqltutorial/sqljoin-innerjoin.aspx |date=20090206030315 }}
* [http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug138.htm Sybase ASE 15 Joins]
* [http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug138.htm Sybase ASE 15 Joins] {{Wayback|url=http://infocenter.sybase.com/help/index.jsp?topic=%2Fcom.sybase.help.ase_15.0.sqlug%2Fhtml%2Fsqlug%2Fsqlug138.htm |date=20190827081643 }}
* [http://dev.mysql.com/doc/refman/5.0/en/join.html MySQL 5.0 Joins]
* [http://dev.mysql.com/doc/refman/5.0/en/join.html MySQL 5.0 Joins] {{Wayback|url=http://dev.mysql.com/doc/refman/5.0/en/join.html |date=20090901020509 }}
* [http://www.oraclepassport.com/OracleJoins.html Oracle Joins - Quick Reference]
* [https://web.archive.org/web/20090911223447/http://www.oraclepassport.com/OracleJoins.html Oracle Joins - Quick Reference]
* [http://www.postgresqlguide.com/retrieving-data-from-multiple-tables-by-using-sql-join.aspx PostgreSQL Join with Query Explain]
* [https://web.archive.org/web/20090928025753/http://www.postgresqlguide.com/retrieving-data-from-multiple-tables-by-using-sql-join.aspx PostgreSQL Join with Query Explain]
* [http://www.postgresql.org/docs/8.3/zhwiki/static/tutorial-join.html PostgreSQL 8.3 Joins]
* [http://www.postgresql.org/docs/8.3/zhwiki/static/tutorial-join.html PostgreSQL 8.3 Joins] {{Wayback|url=http://www.postgresql.org/docs/8.3/zhwiki/static/tutorial-join.html |date=20090919030714 }}
* [http://msdn2.microsoft.com/en-us/library/ms191517.aspx Joins in Microsoft SQL Server]
* [https://web.archive.org/web/20081010173643/http://msdn2.microsoft.com/en-us/library/ms191517.aspx Joins in Microsoft SQL Server]
* [http://maxdb.sap.com/currentdoc/45/f31c38e95511d5995d00508b5d5211/content.htm Joins in MaxDB 7.6]
* [http://maxdb.sap.com/currentdoc/45/f31c38e95511d5995d00508b5d5211/content.htm Joins in MaxDB 7.6] {{Wayback|url=http://maxdb.sap.com/currentdoc/45/f31c38e95511d5995d00508b5d5211/content.htm |date=20070705110455 }}
* [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm Joins in Oracle 11g]
* [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm Joins in Oracle 11g] {{Wayback|url=http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm |date=20090228013842 }}
* [http://www.partow.net/programming/databasealgos/index.html Various join-algorithm implementations]
* [https://web.archive.org/web/20090924072123/http://www.partow.net/programming/databasealgos/index.html Various join-algorithm implementations]
* [http://www.codinghorror.com/blog/archives/000976.html A Visual Explanation of SQL Joins]
* [http://www.codinghorror.com/blog/archives/000976.html A Visual Explanation of SQL Joins] {{Wayback|url=http://www.codinghorror.com/blog/archives/000976.html |date=20090918033712 }}
* [http://www.halfgaar.net/sql-joins-are-easy Another visual explanation of SQL joins, along with some set theory]
* [http://www.halfgaar.net/sql-joins-are-easy Another visual explanation of SQL joins, along with some set theory] {{Wayback|url=http://www.halfgaar.net/sql-joins-are-easy |date=20090904072015 }}
* [http://www.gplivna.eu/papers/sql_join_types.htm SQL join types classified with examples]
* [http://www.gplivna.eu/papers/sql_join_types.htm SQL join types classified with examples] {{Wayback|url=http://www.gplivna.eu/papers/sql_join_types.htm |date=20090903065618 }}
* [http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx An alternative strategy to using FULL OUTER JOIN]
* [http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx An alternative strategy to using FULL OUTER JOIN] {{Wayback|url=http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx |date=20090804164543 }}
{{databases}}


{{-}}
{{DEFAULTSORT:Join (Sql)}}
{{SQL}}
[[Category:SQL keywords]]
{{Databases}}
[[Category:Articles with example SQL code]]


[[cs:JOIN]]
{{DEFAULTSORT:JOIN}}
[[en:Join_(SQL)]]
[[Category:SQL关键字]]
[[es:Join]]
[[fr:Jointure (informatique)]]
[[it:Join (SQL)]]
[[no:Join (SQL)]]
[[ru:Алгоритм соединения (СУБД)]]
[[uk:Об'єднання (SQL)]]
[[vi:Join (SQL)]]

2022年7月20日 (三) 15:35的最新版本

SQL中的连接(JOIN)语句用于将数据库中的两个或多个表组合起来。由"连接"生成的集合,可以被保存为表,或者当成表来使用。JOIN 语句的含义是把两张表的属性通过它们的值组合在一起。基于 ANSI 标准的 SQL 列出了五种 JOIN 方式:内连接(INNER),全外连接(FULL OUTER),左外连接(LEFT OUTER),右外连接(RIGHT OUTER)和交叉连接(CROSS)。在特定的情况下,一张表(基本表,视图,或连接表)可以和自身进行连接,成为自连接(self-join)。

程序员用 JOIN 谓词表示要得到"连接"后的集合。如果求值后谓词为真,组合后的记录就会按照预期的方式生成,如一个记录集,或者一张临时表。

示例用表

[编辑]

通常会对关系数据库进行规范化,以消除重复信息,例如实体类型具有一对多关系时。例如,一个部门可能与许多雇员相关联。将部门和员工的单独表连接起来,可以有效地创建另一个表,将两个表中的信息组合在一起。

下文中解释"连接"都将用到这里的两张表。表中的记录(行)用于演示不同类型的"连接"和"连接谓词"的作用。在下面两张表中,Department.DepartmentID主键Employee.DepartmentID 是外键。

雇员表(Employee)
LastName DepartmentID
Rafferty 31
Jones 33
Heisenberg 33
Robinson 34
Smith 34
Williams NULL
部门表(Department)
DepartmentID DepartmentName
31 Sales
33 Engineering
34 Clerical
35 Marketing

注: 雇员 "Williams" 不在 部门表中的任何一个部门。同样,"市場部" 目前没有员工列出。

这是用来创建上述表的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),它是所有类型的内连接的基础。把表视为行记录的集合,交叉连接即返回这两个集合的笛卡尔积[1] 这其实等价于内连接的链接条件为"永真",或连接条件不存在。

如果 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
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”包的一部分。

通常用于检查服务器的性能。

内连接

[编辑]
显示内部重叠部分填充的维恩图。
表示表A和表B之间的内部连接SQL语句的维恩图。

内连接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 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),是比较连接(θ连接)的一种特例,它的连接谓词只用了相等比较。使用其他比较操作符(如 <)的不是相等连接。前面的查询已经展示了一个相等连接的实例:

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

SQL-92提供了一种可选的简短符号去表达相等连接,它使用USING关键字 (Feature ID F402):[2]

SELECT *
FROM employee INNER JOIN department 
USING (DepartmentID)

USING 结构并不仅仅是语法糖,上面查询的结果和使用显式谓词得到的查询得到的结果是不同的。特别地,在 USING 部分列出的列(column)将在连接结果的临时表中只出现一次,且无表名限定列名。在上面的例子中,连接结果的临时表产生单独的名为 DepartmentID 的列,而不是 employee.DepartmentIDdepartment.DepartmentID

MS SQL Server和Sybase不支持USING语句。

自然连接

[编辑]

自然连接比相等连接的进一步特例化。两表做自然连接时,两表中的所有名称相同的列都将被比较,这是隐式的。自然连接得到的结果表中,两表中名称相同的列只出现一次。

上面用于内连接的查询实例可以用自然连接的方式表示如下:

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 USINGNATURAL 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子句去筛选连接结果中的各行。

左外连接

[编辑]
显示左边圆圈和重叠部分填充的维恩图。
表示表A和表B之间的左连接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
Williams NULL NULL NULL
Heisenberg 33 Engineering 33

右外连接

[编辑]
显示右边圆圈和重叠部分填充的维恩图。
表示表A和表B之间的右连接SQL语句的维恩图。

右外连接,亦简称右连接,它与左外连接完全类似,只不过是作连接的表的顺序相反而已。如果 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
Heisenberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35

实际上显式的右连接很少使用,因为它总是可以被替换成左连接--换换表的位置就可以了,另外,右连接相对于左连接并没有什么额外的功能。上表同样可以使用左连接得到:

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

全连接

[编辑]
显示右圆、左圆和重叠部分填充的维恩图。
表示表A和表B之间的全连接SQL语句的维恩图。

全连接是左右外连接的并集。连接表包含被连接的表的所有记录,如果缺少匹配的记录,即以 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
Williams NULL NULL NULL
Heisenberg 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

自连接

[编辑]

自连接就是和自身连接。[3] 下面的例子是一个很好的说明。

示例

[编辑]

构建一个查询,它试图找到这样的记录: 每条记录包含两个雇员,他们来自于同一个国家。如果你有两张雇员表(Employee),那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了,你可以用一个通常的连接(相等连接)操作去得到这个表。不过,这里所有雇员信息都在一张单独的大表里。[4]

下面一个修改过的雇员表 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

示例解决方案的查询可以写成如下:

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 Heisenberg Australia
124 Jones 145 Heisenberg Australia
305 Smith 306 Williams Germany

关于这个例子,请注意:

  • FS 是雇员表(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

只有当中的两行满足最初问题的要求,第一项和最后一项对于本例来讲毫无用处。

替代方式

[编辑]

外连接查询得到的结果也可以通过关联子查询得到。 例如

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 CROSS JOIN. [2019-03-30]. (原始内容存档于2019-03-30). 
  2. ^ Simplifying Joins with the USING Keyword. [2019-03-30]. (原始内容存档于2019-08-26). 
  3. ^ Shah 2005,第165頁
  4. ^ Adapted from Pratt 2005,第115–6頁

来源

[编辑]

外部链接

[编辑]