最近开发公司项目后台 通常查询数据都要关联很多表 有的时候表之间的关联关系没有写清楚就会出现重复数据 本来想自己写几个SQL说明SQL的链接关系 搜索的时候发现维基百科上面将的非常的清晰 直接粘贴拿来学习。
SQL 的连接(JOIN)语句将数据库中的两个或多个表组合起来.[1] 由"连接"生成的集合, 可以被保存为表, 或者当成表来使用. JOIN 语句的含义是把两张表的属性通过它们的值组合在一起. 基于 ANSI 标准的 SQL 列出了五种 JOIN 方式: 内连接(INNER), 全外连接(FULL OUTER), 左外连接(LEFT OUTER), 右外连接(RIGHT OUTER)和交叉连接(CROSS). 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join).
程序员用 JOIN 谓词表示要得到"连接"后的集合. 如果evaluated predicate为真, 组合后的记录就会按照预期的方式生成, 如一个记录集, 或者一张临时表.
示例用表
下文中解释"连接"都将用到这里的两张表. 表中的记录(行)用于演示不同类型的"连接"和"连接谓词"的作用. 在下面两张表中, Department.DepartmentID 是主键, Employee.DepartmentID 是外键.
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Steinberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| Jasper | NULL |
| DepartmentID | DepartmentName |
|---|---|
| 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 和DepartmentName 等列，把它们放到结果表的一行(一条记录)里。当 DepartmentID 不匹配，就不会往结果表中生成任何数据.
显式的内连接实例:
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
等价于:
SELECT *
FROM employee，department
WHERE employee.DepartmentID = department.DepartmentID
显式的内连接的输出结果:
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Robinson | 34 | 秘书 | 34 |
| Jones | 33 | 工程部 | 33 |
| Smith | 34 | 秘书 | 34 |
| Steinberg | 33 | 工程部 | 33 |
| Rafferty | 31 | 销售部 | 31 |
注 雇员 "Jasper" 和部门 "市场部" 都未出现。它们在预期得到的表中没有任何匹配的记录: "Jasper" 没有关联的部门，而号码为35的部门中没有任何雇员。这样，在"连接"后的表中，就没有关于 Jasper 或 市场部 的信息了。相对于预期的结果，这个行为可能是一个微妙的Bug。外连接可能可以避免这种情况.
相等链接
相等连接 (equi-join，或 equijoin)，是比较连接(θ连接)的一种特例，它的连接谓词只用了相等比较。使用其他比较操作符(如 <)的不是相等连接。前面的查询已经展示了一个相等连接的实例:
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
SQL 提供了一种可选的简短符号去表达相等连接，它使用 USING 关键字 (Feature ID F402):
SELECT *
FROM employee
INNER JOIN department
USING (DepartmentID)
USING 结构并不仅仅是语法糖，上面查询的结果和使用显式谓词得到的查询得到的结果是不同的。特别地，在 USING 部分列出的列(column)将以只出现一次，且名称无表名修饰.在上面的例子中，将产生单独的名为 DepartmentID 的列，而不是 employee.DepartmentID 或 department.DepartmentID.
USING 语句现已被 MySQL，Oracle，PostgreSQL，SQLite，和 DB2/400 等产品支持.
自然连接
自然连接比相等连接的进一步特例化。两表做自然连接时，两表中的所有名称相同的列都将被比较，这是隐式的。自然连接得到的结果表中，两表中名称相同的列只出现一次.
上面用于内连接的查询实例可以用自然连接的方式表示如下:
SELECT *
FROM employee NATURAL JOIN department
用了 USING 语句后，在连接表中，DepartmentID 列只出现一次，且没有表名作前缀:
| DepartmentID | Employee.LastName | Department.DepartmentName |
|---|---|---|
| 34 | Smith | 秘书 |
| 33 | Jones | 工程部 |
| 34 | Robinson | 秘书 |
| 33 | Steinberg | 工程部 |
| 31 | Rafferty | 销售部 |
在 Oracle 里用 JOIN USING 或 NATURAL JOIN 时，如果两表共有的列的名称前加上某表名作为前缀，则会报编译错误: "ORA-25154: column part of USING clause cannot have qualifier" 或 "ORA-25155: column used in NATURAL join cannot have qualifier".
交叉连接
交叉连接(cross join)，又称笛卡尔连接(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<左> 和 right<右> 表示 JOIN 关键字的两边.)
在标准的 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 |
| Jasper | NULL | NULL | NULL |
| Steinberg | 33 | Engineering | 33 |
右外连接
右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已. 如果 A 表右连接 B 表, 那么"右表" B 中的每一行在连接表中至少会出现一次. 如果 B 表的记录在"左表" A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL.
右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).
例如, 这允许我们在找每一个雇员以及他的部门信息时, 当这个部门里没有任何雇员时, 也把部门显示出来.
右连接的实例: (相对于内连接增添的行用斜体标出)
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
实际上显式的右连接很少使用, 因为它总是可以被替换成左连接--换换表的位置就可以了, 另外, 右连接相对于左连接并没有什么额外的功能. 上表同样可以使用左连接得到:
SELECT *
FROM department LEFT OUTER JOIN employee
ON employee.DepartmentID = department.DepartmentID
全连接
全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.
如, 这允许我们查看每一个在部门里的员工和每一个拥有雇员的部门, 同时, 还能看到不在任何部门的员工以及没有任何员工的部门.
全连接实例:
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| Jasper | NULL | NULL | NULL |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
一些数据库系统(如 MySQL)并不直接支持全连接, 但它们可以通过左右外连接的并集(参: union)来模拟实现. 和上面等价的实例:
SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
SQLite 不支持右连接, 全外连接可以按照下面的方式模拟:
SELECT employee.*, department.*
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT employee.*, department.*
FROM department
LEFT JOIN employee
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
自连接
自连接就是和自身连接.[2] 下面的例子是一个很好的说明.
示例
构建一个查询, 它试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家. 如果你有两张雇员表(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
AND F.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
ON employee.DepartmentID = department.DepartmentID
也可以写成如下样子:
SELECT employee.LastName, employee.DepartmentID,
(SELECT department.DepartmentName
FROM department
WHERE employee.DepartmentID = department.DepartmentID )
FROM employee