连接:修订间差异
Iridiumcao(留言 | 贡献) |
|||
(未显示44个用户的80个中间版本) | |||
第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> [[谓词]]表示要得到"连接"后的集合 |
程序员用 <code>JOIN</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) |
||
|- |
|- |
||
! LastName !! DepartmentID |
! LastName !! DepartmentID |
||
第18行: | 第20行: | ||
| Jones || 33 |
| Jones || 33 |
||
|- |
|- |
||
| |
| Heisenberg || 33 |
||
|- |
|- |
||
| Robinson || 34 |
| Robinson || 34 |
||
第24行: | 第26行: | ||
| Smith|| 34 |
| Smith|| 34 |
||
|- |
|- |
||
| |
| 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) |
||
|- |
|- |
||
! DepartmentID !! DepartmentName |
! DepartmentID !! DepartmentName |
||
|- |
|- |
||
| 31 || Sales |
| 31 || Sales |
||
|- |
|- |
||
| 33 || Engineering |
| 33 || Engineering |
||
第41行: | 第43行: | ||
|} |
|} |
||
{{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 不匹配,就不会往结果表中生成任何数据。 |
|||
显式的内连接实例: |
显式的内连接实例: |
||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM employee |
FROM employee |
||
INNER JOIN department |
|||
ON employee.DepartmentID = department.DepartmentID |
|||
</syntaxhighlight> |
|||
</source> |
|||
等价于: |
等价于: |
||
< |
<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 |
||
|- |
|- |
||
| |
| Heisenberg || 33 || Engineering || 33 |
||
|- |
|- |
||
| Rafferty || 31 || Sales || 31 |
| Rafferty || 31 || Sales || 31 |
||
第90行: | 第198行: | ||
|} |
|} |
||
'''注''' 雇员 " |
'''注''' 雇员 "Williams" 和部门 "市場部" 都未出现。它们在预期得到的表中没有任何匹配的记录: "Williams" 没有关联的部门,而号码为35的部门中没有任何雇员。这样,在"连接"后的表中,就没有关于 Williams 或 市場部 的信息了。相对于预期的结果,这个行为可能是一个微妙的[[Bug]]。外连接可能可以避免这种情况。 |
||
=== 相等链接 === |
|||
相等连接 |
相等连接 ('''equi-join''',或 '''equijoin'''),是比较连接(''θ连接'')的一种特例,它的连接谓词只用了相等比较。使用其他比较操作符(如 <code><</code>)的不是相等连接。前面的查询已经展示了一个相等连接的实例: |
||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM |
FROM employee 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): |
|||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM |
FROM employee INNER JOIN department |
||
USING (DepartmentID) |
|||
INNER JOIN department |
|||
</syntaxhighlight> |
|||
USING (DepartmentID) |
|||
</source> |
|||
<code>USING</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 等产品支持. |
|||
=== 自然连接 === |
|||
[[自然连接]]比相等连接的进一步特例化 |
[[自然连接]]比相等连接的进一步特例化。两表做自然连接时,两表中的所有名称相同的列都将被比较,这是隐式的。自然连接得到的结果表中,两表中名称相同的列只出现一次。 |
||
上面用于内连接的查询实例可以用自然连接的方式表示如下: |
上面用于内连接的查询实例可以用自然连接的方式表示如下: |
||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM employee NATURAL JOIN department |
FROM employee NATURAL JOIN department |
||
</syntaxhighlight> |
|||
</source> |
|||
用了 <code>USING</code> 语句后 |
用了 <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 || |
| 33 || Heisenberg || Engineering |
||
|- |
|- |
||
| 31 || Rafferty || Sales |
| 31 || Rafferty || Sales |
||
|} |
|} |
||
在 [[Oracle]] 里用 <code>JOIN USING</code> 或 <code>NATURAL JOIN</code> 时 |
在 [[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)。如果左表的一行在右表中存在多个匹配行,那么左表的行会复制和右表匹配行一样的数量,并进行组合生成连接结果。 |
|||
如,这允许我们去找到雇员的部门时,显示所有雇员,即使这个雇员还没有关联的部门。(在上面的内连接部分由一个相反的例子,没有关联的部门号的雇员在结果中是不显示的)。 |
|||
''左外连接''(''left outer join''), 亦简称为'''左连接'''('''left join'''), 若 A 和 B 两表进行左外连接, 那么结果表中将包含"左表"(即表 A)的所有记录, 即使那些记录在"右表" B 没有符合连接条件的匹配. 这意味着即使 <code>ON</code> 语句在 B 中的匹配项是0条, 连接操作还是会返回一条记录, 只不过这条记录的中来自于 B 的每一列的值都为 NULL. 这意味着'''左外连接'''会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果. |
|||
如, 这允许我们去找到雇员的部门时, 显示所有雇员, 即使这个雇员还没有关联的部门. (在上面的内连接部分由一个相反的例子, 没有关联的部门号的雇员在结果中是不显示的). |
|||
左外连接实例: |
左外连接实例: |
||
(相对于内连接增添的行用斜体标出) |
|||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM |
FROM employee LEFT OUTER JOIN department |
||
ON employee.DepartmentID = department.DepartmentID; |
|||
</syntaxhighlight> |
|||
</source> |
|||
{| class="wikitable" style="text-align:center" |
{| class="wikitable" style="text-align:center" |
||
第251行: | 第284行: | ||
| Smith || 34 || Clerical || 34 |
| Smith || 34 || Clerical || 34 |
||
|- |
|- |
||
| '' |
| ''Williams'' || {{null result}} || {{null result}} || {{null result}} |
||
|- |
|- |
||
| |
| 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 |
FROM employee RIGHT OUTER JOIN department |
||
ON employee.DepartmentID = department.DepartmentID |
|||
</syntaxhighlight> |
|||
</source> |
|||
{| class="wikitable" style="text-align:center" |
{| class="wikitable" style="text-align:center" |
||
第281行: | 第316行: | ||
| Robinson || 34 || Clerical || 34 |
| Robinson || 34 || Clerical || 34 |
||
|- |
|- |
||
| |
| Heisenberg || 33 || Engineering || 33 |
||
|- |
|- |
||
| Rafferty || 31 || Sales || 31 |
| Rafferty || 31 || Sales || 31 |
||
第288行: | 第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: |
|||
< |
<syntaxhighlight lang=sql> |
||
SELECT * |
SELECT * |
||
FROM |
FROM department LEFT OUTER JOIN employee |
||
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" |
||
第320行: | 第355行: | ||
| Robinson || 34 || Clerical || 34 |
| Robinson || 34 || Clerical || 34 |
||
|- |
|- |
||
| '' |
| ''Williams'' || {{null result}} || {{null result}} || {{null result}} |
||
|- |
|- |
||
| |
| Heisenberg || 33 || Engineering || 33 |
||
|- |
|- |
||
| Rafferty || 31 || Sales || 31 |
| Rafferty || 31 || Sales || 31 |
||
第329行: | 第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 |
||
第341行: | 第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: |
|||
< |
<syntaxhighlight lang=sql> |
||
SELECT employee.*, department.* |
SELECT employee.*, department.* |
||
FROM employee |
FROM employee |
||
第356行: | 第392行: | ||
ON employee.DepartmentID = department.DepartmentID |
ON employee.DepartmentID = department.DepartmentID |
||
WHERE employee.DepartmentID IS NULL |
WHERE employee.DepartmentID IS NULL |
||
</syntaxhighlight> |
|||
</source> |
|||
== 自连接 == |
== 自连接 == |
||
自连接就是和自身连接。<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–6}}</ref> |
|||
下面一个修改过的雇员表 <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 |
||
第376行: | 第412行: | ||
| 124 || Jones || Australia || 33 |
| 124 || Jones || Australia || 33 |
||
|- |
|- |
||
| 145 || |
| 145 || Heisenberg || Australia || 33 |
||
|- |
|- |
||
| 201 || Robinson || United States || 34 |
| 201 || Robinson || United States || 34 |
||
|- |
|- |
||
| 305 || Smith || |
| 305 || Smith || Germany || 34 |
||
|- |
|- |
||
| 306 || |
| 306 || Williams || Germany || {{null result}} |
||
|} |
|} |
||
{{clear}} |
|||
示例解决方案的查询可以写成如下: |
|||
An example solution query could be as follows: |
|||
< |
<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 |
||
第394行: | 第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 |
||
第404行: | 第440行: | ||
| 123 || Rafferty || 124 || Jones || Australia |
| 123 || Rafferty || 124 || Jones || Australia |
||
|- |
|- |
||
| 123 || Rafferty || 145 || |
| 123 || Rafferty || 145 || Heisenberg || Australia |
||
|- |
|- |
||
| 124 || Jones || 145 || |
| 124 || Jones || 145 || Heisenberg || Australia |
||
|- |
|- |
||
| 305 || Smith || 306 || |
| 305 || Smith || 306 || Williams || Germany |
||
|} |
|} |
||
{{clear}} |
|||
关于这个例子,请注意: |
|||
For this example, note that: |
|||
*<code>F</code> |
* <code>F</code> 和 <code>S</code> 是雇员表(employee)的第一个和第二个拷贝的别名 |
||
* |
* 条件 <code>F.Country = S.Country</code> 排除了在不同国家的雇员的组合。这个例子仅仅期望得到在相同国家的雇员的组合。 |
||
* |
* 条件 <code>F.EmployeeID < S.EmployeeID</code> 排除了雇员号(<code>EmployeeID</code>)相同的组合。 |
||
*<code>F.EmployeeID < S.EmployeeID</code> |
* <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 || |
| 305 || Smith || 305 || Smith || Germany |
||
|- |
|- |
||
| 305 || Smith || 306 || |
| 305 || Smith || 306 || Williams || Germany |
||
|- |
|- |
||
| 306 || |
| 306 || Williams || 305 || Smith || Germany |
||
|- |
|- |
||
| 306 || |
| 306 || Williams || 306 || Williams || Germany |
||
|} |
|} |
||
{{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 |
|||
例如 |
|||
< |
<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 |
|||
< |
<syntaxhighlight lang=sql> |
||
SELECT employee.LastName, employee.DepartmentID, |
SELECT employee.LastName, employee.DepartmentID, |
||
(SELECT department.DepartmentName |
(SELECT department.DepartmentName |
||
第448行: | 第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) ==== |
|||
适用于查询的中间结果,通常是无索引的临时表;以及中间结果的行数很大时。哈希连接选择行数较小的输入表作为生成输入,对其连接列值应用哈希函数,把其行(的存储位置)放入哈希桶中。 |
|||
== |
== 参见 == |
||
* |
* {{le|Composition of relations}} |
||
== |
== 参考文献 == |
||
=== 引用 === |
|||
{{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 |
||
第515行: | 第522行: | ||
|isbn= 9780619216740 |
|isbn= 9780619216740 |
||
}} |
}} |
||
*{{citation |
* {{citation |
||
|last=Shah |
|last=Shah |
||
|first= Nilesh |
|first= Nilesh |
||
第525行: | 第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 |
||
第537行: | 第544行: | ||
|isbn= 9781558604346 |
|isbn= 9781558604346 |
||
}} |
}} |
||
{{refend}} |
|||
== |
== 外部链接 == |
||
* [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]] |
|||
{{DEFAULTSORT:JOIN}} |
|||
[[ |
[[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
是外键。
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。
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”包的一部分。
通常用于检查服务器的性能。
内连接
[编辑]内连接(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 | 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.DepartmentID
或 department.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 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)。如果左表的一行在右表中存在多个匹配行,那么左表的行会复制和右表匹配行一样的数量,并进行组合生成连接结果。
如,这允许我们去找到雇员的部门时,显示所有雇员,即使这个雇员还没有关联的部门。(在上面的内连接部分由一个相反的例子,没有关联的部门号的雇员在结果中是不显示的)。
左外连接实例: (相对于内连接增添的行用斜体标出)
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)。
例如,这允许我们在找每一个雇员以及他的部门信息时,当这个部门里没有任何雇员时,也把部门显示出来。
右连接的实例: (相对于内连接增添的行用斜体标出)
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
全连接
[编辑]全连接是左右外连接的并集。连接表包含被连接的表的所有记录,如果缺少匹配的记录,即以 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
:
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;
它执行后将生成下面的表:
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 |
只有当中的两行满足最初问题的要求,第一项和最后一项对于本例来讲毫无用处。
替代方式
[编辑]外连接查询得到的结果也可以通过关联子查询得到。 例如
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)
[编辑]适用于查询的中间结果,通常是无索引的临时表;以及中间结果的行数很大时。哈希连接选择行数较小的输入表作为生成输入,对其连接列值应用哈希函数,把其行(的存储位置)放入哈希桶中。
参见
[编辑]参考文献
[编辑]引用
[编辑]- ^ SQL CROSS JOIN. [2019-03-30]. (原始内容存档于2019-03-30).
- ^ Simplifying Joins with the USING Keyword. [2019-03-30]. (原始内容存档于2019-08-26).
- ^ Shah 2005,第165頁
- ^ Adapted from Pratt 2005,第115–6頁
来源
[编辑]- Pratt, Phillip J., A Guide To SQL, Seventh Edition, Thomson Course Technology, 2005, ISBN 9780619216740
- Shah, Nilesh, Database Systems Using Oracle - A Simplified Guide to SQL and PL/SQL Second Edition International Edition, Pearson Education International, 2005 [2002], ISBN 0131911805
- Yu, Clement T.; Meng, Weiyi, Principles of Database Query Processing for Advanced Applications, Morgan Kaufmann, 1998 [2009-03-03], ISBN 9781558604346
外部链接
[编辑]- 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 (页面存档备份,存于互联网档案馆)