数据库中join用法

原链接

http://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5_(SQL)#.E7.A4.BA.E4.BE.8B.E7.94.A8.E8.A1.A8

 

一、join的用法

内连接、外连接

 

示例用表：

雇员表(Employee)

LastNameDepartmentID

Rafferty31

Jones33

Steinberg33

Robinson34

Smith34

JasperNULL

 

 

部门表(Department)

DepartmentID部门

31销售部

33工程部

34书记

35市场部

 

１、内连接:相等连接、自然连接、交叉连接

 

1)、显式的内连接与隐式连接(inner join == join )

 

显示连接：SELECT  * from   employee join  department on employee.DepartmentID = department.DepartmentID

等价于：

隐式连接：SELECT  * from   employee,department WHERE employee.DepartmentID = department.DepartmentID

 

注：当DepartmentID不匹配，就不会往结果表中生成任何数据。

 

2)、相等连接

提供了一种可选的简短符号去表达相等连接，它使用 USING 关键字。

SELECT  * from   employee join  department  using (DepartmentID)

 

注：与显式连接不同在于：DepartmentID只显示一列

 

3)、自然连接

　比相等连接的进一步特例化。两表做自然连接时，两表中的所有名称相同的列都将被比较，这是隐式的。

　自然连接得到的结果表中，两表中名称相同的列只出现一次.

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".

 

４)交叉连接（又称笛卡尔连接）

 如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B.  

显示连接：

select * from employee cross join department

等价于

隐式连接：

select * from employee,department

 

 

２、外连接

并不要求连接的两表的每一条记录在对方表中都一条匹配的记录。

1)左连接（left outer join == left join）

 若Ａ表与Ｂ表左连接，Ａ表对就的Ｂ表没有匹配，连接操作也会返回一条记录，对应值为NULL。

 

 如：

Jaspernull null null

Jones3333工程部

Rafferty3131销售部

Robinson3434书记

Smith3434书记

Steinberg3333工程部

 

 若Ａ表对应Ｂ表中有多行，则左表会复制和右表匹配行一样的数量，并组合生成连接结果。

 如：select * from department  left join employee  on employee.departmentId = department.departmentId

 

31销售部Rafferty31

33工程部Jones33

33工程部Steinberg33

34书记Robinson34

34书记Smith34

35市场部nullnull

 

2)、右连接（right outer join == right join）

与左连接同（略）

3)、全连接（full outer join ==full join）

是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 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 all

select * from employee right join department on employee.departmentId = department.departmentId  

 

注：SQLite 不支持右连接。