笛卡尔积对两个关系R和S进行操作,产生的关系中元组个数为两个关系中元组个数之积。等值联接则是在笛卡尔积的结果上再进行选择操作,挑选关系第i个分量与第(r+j)个分量值相等的元组;自然连接则是在等值联接(以公共属性值相等为条件)的基础上再行投影操作,去掉S中的公共属性列,当两个关系没有公共属性时,自然连接就转化成笛卡尔积
内连接分:等值连接 不等值连接 自然连接
外连接分:左外连接 右外连接
还可参看维基百科上一篇介绍更全的文章:http://zh.wikipedia.org/wiki/连接_(SQL)
连接 (SQL)
SQL JOIN
)语句将数据库中的两个或多个表组合起来.[1] JOIN
JOIN
INNER
), 外连接(OUTER
), 左外连接(LEFT
), 和右外连接(RIGHT
). 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join).
程序员用 JOIN
下文中解释"连接"都将用到这里的两张表. 表中的记录(行)用于演示不同类型的"连接"和"连接谓词"的作用. 在下面两张表中, Department.DepartmentID
是主键,Employee.DepartmentID
是外键.[编辑]示例用表
LastName | DepartmentID |
---|---|
Rafferty | 31 |
Jones | 33 |
Steinberg | 33 |
Robinson | 34 |
Smith | 34 |
Jasper | NULL |
DepartmentID | 部门 |
---|---|
31 | 销售部 |
33 | 工程部 |
34 | 书记 |
35 | 市场部 |
注: "市场部" 目前没有员工列出. 同样, 雇员 "Jasper" 不在 部门表中的任何一个部门.
[编辑]内连接
内连接(inner join)是应用程序中用的普遍的"连接"操作,它一般都是默认的连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起,产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较,并找出满足连接谓词的组合。当连接谓词被满足,A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集,可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合,然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接,笛卡尔积运算是非常没效率的.
SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号",它显式地使用关键字 JOIN
,其次是"隐式连接符号",它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT
FROM
WHERE
内连接"可以进一步被分为: 相等连接,自然连接,和交叉连接(见下).
程序要应该特别注意连接依据的列可能包含 NULL 值,NULL 值不与任何值匹配(甚至和它本身) -- 除非连接条件中显式地使用 IS NULL
IS NOT NULL
例如,下面的查询通过 Employee 表和 Department 表共有的属性 DepartmentID 连接两表。在两表 DepartmentID 匹配之处(如连接谓词被满足),查询将组合两表的LastName,DepartmentID
显式的内连接实例:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID
等价于:
SELECT * FROM employee,department WHERE employee.DepartmentID = department.DepartmentID
显式的内连接的输出结果:
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Robinson | 34 | 书记 | 34 |
Jones | 33 | 工程部 | 33 |
Smith | 34 | 书记 | 34 |
Steinberg | 33 | 工程部 | 33 |
Rafferty | 31 | 销售部 | 31 |
注
[编辑]相等链接
相等连接 (equi-join,或 <
)的不是相等连接。前面的查询已经展示了一个相等连接的实例:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID
SQL 提供了一种可选的简短符号去表达相等连接,它使用 USING
SELECT * FROM employee INNER JOIN department USING (DepartmentID)
USING
USING
DepartmentID
employee.DepartmentID
department.DepartmentID
.
USING
[编辑]自然连接
自然连接比相等连接的进一步特例化。两表做自然连接时,两表中的所有名称相同的列都将被比较,这是隐式的。自然连接得到的结果表中,两表中名称相同的列只出现一次.
上面用于内连接的查询实例可以用自然连接的方式表示如下:
SELECT * FROM employee NATURAL JOIN department
用了 USING
DepartmentID | Employee.LastName | Department.DepartmentName |
---|---|---|
34 | Smith | Clerical |
33 | Jones | Engineering |
34 | Robinson | Clerical |
33 | Steinberg | Engineering |
31 | Rafferty | Sales |
在 JOIN USING
NATURAL JOIN
[编辑]交叉连接
交叉连接(cross join),又称笛卡尔连接(cartesian join)或叉乘(Product),它是所有类型的内连接的基础。把表视为行记录的集合,交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为"永真",或连接条件不存在.
如果 A 和 B 是两个集合,它们的交叉连接就记为: A × B.
用于交叉连接的 SQL 代码在 FROM
显式的交叉连接实例:
SELECT * FROM employee CROSS JOIN department
隐式的交叉连接实例:
SELECT * FROM employee,department;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Rafferty | 31 | Sales | 31 |
Jones | 33 | Sales | 31 |
Steinberg | 33 | Sales | 31 |
Smith | 34 | Sales | 31 |
Robinson | 34 | Sales | 31 |
Jasper | NULL | Sales | 31 |
Rafferty | 31 | Engineering | 33 |
Jones | 33 | Engineering | 33 |
Steinberg | 33 | Engineering | 33 |
Smith | 34 | Engineering | 33 |
Robinson | 34 | Engineering | 33 |
Jasper | NULL | Engineering | 33 |
Rafferty | 31 | Clerical | 34 |
Jones | 33 | Clerical | 34 |
Steinberg | 33 | Clerical | 34 |
Smith | 34 | Clerical | 34 |
Robinson | 34 | Clerical | 34 |
Jasper | NULL | Clerical | 34 |
Rafferty | 31 | Marketing | 35 |
Jones | 33 | Marketing | 35 |
Steinberg | 33 | Marketing | 35 |
Smith | 34 | Marketing | 35 |
Robinson | 34 | Marketing | 35 |
Jasper | NULL | Marketing | 35 |
交叉连接不会应用任何谓词去过滤结果表中的记录。程序员可以用 WHERE
[编辑]外连接
外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 -- 甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.
(在这种情况下left<左> 和 JOIN
在标准的 SQL 语言中, 外连接没有隐式的连接符号.
[编辑]左外连接
左外连接(left outer join), 亦简称为左连接(left join), 若 A 和 B 两表进行左外连接, 那么结果表中将包含"左表"(即表 A)的所有记录, 即使那些记录在"右表" B 没有符合连接条件的匹配. 这意味着即使 ON
如, 这允许我们去找到雇员的部门时, 显示所有雇员, 即使这个雇员还没有关联的部门. (在上面的内连接部分由一个相反的例子, 没有关联的部门号的雇员在结果中是不显示的).
左外连接实例: (相对于内连接增添的行用斜体标出)
SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Jones | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
Robinson | 34 | Clerical | 34 |
Smith | 34 | Clerical | 34 |
Jasper | NULL | NULL | NULL |
Steinberg | 33 | Engineering | 33 |
[编辑]右外连接
右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已. 如果 A 表右连接 B 表, 那么"右表" B 中的每一行在连接表中至少会出现一次. 如果 B 表的记录在"左表" A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL.
右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).
例如, 这允许我们在找每一个雇员以及他的部门信息时, 当这个部门里没有任何雇员时, 也把部分显示出来.
右连接的实例: (相对于内连接增添的行用斜体标出)
SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Smith | 34 | Clerical | 34 |
Jones | 33 | Engineering | 33 |
Robinson | 34 | Clerical | 34 |
Steinberg | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
NULL | NULL | Marketing | 35 |
实际上显式的右连接很少使用, 因为它总是可以被替换成左连接--换换表的位置就可以了, 另外, 右连接相对于左连接并没有什么额外的功能. 上表同样可以使用左连接得到:
SELECT * FROM department LEFT OUTER JOIN employee ON employee.DepartmentID = department.DepartmentID
[编辑]全连接
全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.
如, 这允许我们查看每一个在部门里的员工和每一个拥有雇员的部门, 同时, 还能看到不在任何部门的员工以及没有任何员工的部门.
全连接实例:
SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Smith | 34 | Clerical | 34 |
Jones | 33 | Engineering | 33 |
Robinson | 34 | Clerical | 34 |
Jasper | NULL | NULL | NULL |
Steinberg | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
NULL | NULL | Marketing | 35 |
一些数据库系统(如 MySQL)并不直接支持全连接, 但它们可以通过左右外连接的并集(参:
SELECT * FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION SELECT * FROM employee RIGHTJOIN 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 UNIONSELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHEREemployee.DepartmentID IS NULL
[编辑]自连接
自连接就是和自身连接.[2]
[编辑]示例
构建一个查询, 它试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家. 如果你有两张雇员表(Employee
), 那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了, 你可以用一个通常的连接(相等连接)操作去得到这个表. 不过, 这里所有雇员信息都在一张但对大表里.[3]
下面一个修改过的雇员表 Employee
:
EmployeeID | LastName | Country | DepartmentID |
---|---|---|---|
123 | Rafferty | Australia | 31 |
124 | Jones | Australia | 33 |
145 | Steinberg | Australia | 33 |
201 | Robinson | United States | 34 |
305 | Smith | United Kingdom | 34 |
306 | Jasper | United Kingdom | NULL |
示例解决方案的查询可以写成如下:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country ANDF.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;
它执行后将生成下面的表:
EmployeeID | LastName | EmployeeID | LastName | Country |
---|---|---|---|---|
123 | Rafferty | 124 | Jones | Australia |
123 | Rafferty | 145 | Steinberg | Australia |
124 | Jones | 145 | Steinberg | Australia |
305 | Smith | 306 | Jasper | United Kingdom |
关于这个例子, 请注意:
F
和 S
是雇员表(employee)的第一个和第二个拷贝的别名 - 条件
F.Country = S.Country
排除了在不同国家的雇员的组合. 这个例子仅仅期望得到在相同国家的雇员的组合. - 条件
F.EmployeeID < S.EmployeeID
排除了雇员号( EmployeeID
)相同的组合. F.EmployeeID < S.EmployeeID
排除了重复的组合. 没有这个条件的话, 将生成类似下面表中的无用数据(仅以 United Kingdom 为例)
EmployeeID | LastName | EmployeeID | LastName | Country |
---|---|---|---|---|
305 | Smith | 305 | Smith | United Kingdom |
305 | Smith | 306 | Jasper | United Kingdom |
306 | Jasper | 305 | Smith | United Kingdom |
306 | Jasper | 306 | Jasper | United Kingdom |
只有当中的两行满足最初问题的要求, 第一项和最后一项对于本例来讲毫无用处.
[编辑]替代方式
外连接查询得到的结果也可以通过关联子查询得到. 例如
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee LEFT OUTER JOIN department ONemployee.DepartmentID = department.DepartmentID
也可以写成如下样子:
SELECT employee.LastName, employee.DepartmentID, (SELECT department.DepartmentName FROM department WHERE employee.DepartmentID =department.DepartmentID ) FROM employee
[编辑]实现
[编辑]连接算法
执行一个连接操作, 存在三种基本的算法.