当前位置：首页 > news >正文

内连接和外连接

news 来源：原创 2024/4/28 14:59:38

2019独角兽企业重金招聘Python工程师标准>>>

内连接

内连接分为: 相等连接，自然连接，和交叉连接

显式的内连接实例:

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

等价于:

SELECT * FROM employee，department WHERE employee.DepartmentID = department.DepartmentID

相等链接

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

SELECT * FROM employee INNER JOIN department USING (DepartmentID)

自然连接

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

SELECT * FROM employee NATURAL JOIN department

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

显式的交叉连接实例:

SELECT * FROM employee CROSS JOIN department

隐式的交叉连接实例:

SELECT * FROM employee ,department;

外连接

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

左外连接

左外连接 会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL).

如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.

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

这允许我们去找到雇员的部门时, 显示所有雇员, 即使这个雇员还没有关联的部门.(在上面的内连接部分由一个相反的例子, 没有关联的部门号的雇员在结果中是不显示的).

右外连接

右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).

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

这允许我们在找每一个雇员以及他的部门信息时, 当这个部门里没有任何雇员时, 也把部分显示出来.

实际上显式的右连接很少使用, 因为它总是可以被替换成左连接--换换表的位置就可以了。

全连接

全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.

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

这允许我们查看每一个在部门里的员工和每一个拥有雇员的部门, 同时, 还能看到不在任何部门的员工以及没有任何员工的部门.

一些数据库系统(如 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

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

自连接

自连接就是和自身连接

试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家.

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;

F 和 S 是雇员表(employee)的第一个和第二个拷贝的别名
条件 F.Country = S.Country 排除了在不同国家的雇员的组合. 这个例子仅仅期望得到在相同国家的雇员的组合.
条件 F.EmployeeID < S.EmployeeID 排除了雇员号(EmployeeID)相同的组合.
F.EmployeeID < S.EmployeeID 排除了重复的组合. 没有这个条件的话, 将生成类似下面表中的无用数据(仅以 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