关于sql的left join,right join,inner join,outerjoin

这几天搞请清楚了SQL语句当中的左连接，右连接，内连接，还有外连接，以及自连接。

主要是参考wiki的内容。里面很详细的介绍了连接的一些例子。

如下介绍：

假如我们创建了如下的两个表格：

Employee Table

EmpName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
Robinson	34
Smith	34
Jasper	NULL

Department Table

DepartmentID	DeptName
31	Sales
33	Engineering
34	Clerical
35	Marketing

SQL语句如下：

create table Employee(
 EmpName Varchar(40),
 DepartmentID char(2)
)
create table Department (
DeptName Varchar(40),
DepartmentID  char(2)
)primary key(DeptID)

insert into Employee values('Rafferty','31');
insert into Employee values('Jones','33');
insert into Employee values('Steinberg','33');
insert into Employee values('Robinson','34');
insert into Employee values('Smith','34');
insert into Employee(EmpName) values('Jasper');
insert into Department values('Sales','31');
insert into Department values('Engineering','33');
insert into Department values('Clerical','34');
insert into Department values('Marketing','35');

如上table建立后，

1，内连接(inner join)

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

同等于下面的这个语句

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

2，等值连接(equi Join)

个人觉得其实就是内连接的一种，不过测试语句using的这个我在teradata测试下没通过，估计也是依赖于数据库。

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

和如下表示

SELECT *
FROM employee
       INNER JOIN department
          USING (DepartmentID)

3，自然连接(Natural Join)

其实自然连接就和内连接结果集然后过滤相应的一个条件，使形成的表不会有空字段或者冗余字段。

SELECT *
FROM employee NATURAL JOIN department

epartmentID	Employee.Empname	Department.DepartmentName
34	Smith	Clerical
33	Jones	Engineering
34	Robinson	Clerical
33	Steinberg	Engineering
31	Rafferty	Sales

4，交叉连接(Cross Join)

开发中很忌讳产生交叉连接，因为它将产生很大的数据量，而且一般都是冗余的数据，比如表格A有10条数据，表格B有30条数据，产生的结果集将是10*30=300条数据。

SELECT *
FROM employee CROSS JOIN department

隐含的交叉连接

SELECT *
FROM employee, department;

5，外连接(Outer Join)

包含左外连接(Left outer Join or Left Join)

右外连接(Right outer join or Right Join)

以及全外连接(Full outer Join)

 1.左连接如下(Left outer Join or Left Join)

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

Employee.EmpName	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

2.右连接(Right Outer Join)

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

Employee.EmpName	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

左右连接会根据主表的不同而产生不一样的数据集。一般from后接的就是主表。

3.全外连接(Full outer Join)

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

Employee.EmpName	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

其实相当于是合并了左右连接。

语句相等于下面的

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

6，自连接

其实就是表自己根据条件关联自己。

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;

大概就这些了，有其他的东西欢迎大家补充。谢谢