[#0x003A] join

  来自wikipedia。仅作总结。

  假设有两张表:  

  Cross Join: 即笛卡尔乘积(Cartesian Product)。标准语法为:

SELECT *
FROM   employee CROSS JOIN department;

也可以隐式地写为:

SELECT *
FROM   employee, department;

得到的表结构为(LastName, DepartmentID, DepartmentID, DepartmentName)

 

  Inner Join: 不标准地说,Inner Join就是Cross Join加上了一个where子句,如:

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

当然,这也是隐式的写法。标准语法为:

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

 

  ★★Equi-Join: 是Inner Join的特殊形式,特指where子句中的条件为Equality Comparison,如上面的例子。不过它也有自己独特的语法:

SELECT *
FROM   employee INNER JOIN department 
USING  (DepartmentID)

注意using子句会消除掉一个DepartmentID列,即得到(LastName, DepartmentID, DepartmentName),而不是(LastName, DepartmentID, DepartmentID, DepartmentName)。可以把using子句看做是特殊的where子句。

 

  ★★★★Natural Join: 是使用using子句的Equi-Join的特殊形式。Natural Join不用指定Equality Comparison的Column,它会自动查找做Natural Join的表中同名的Column,隐式地在这个Column上使用using子句。它的标准语法为:

SELECT *
FROM   employee NATURAL JOIN department;

如果有多个同名Column(或者USING(Column1, ..., Column N)),则必须这多个Column都相等的列才被检入Join的结果中。

 

  Outer Join: Outer Join的定义不太好描述,我们先来看看Inner Join的示意图:

由于(John, NULL)和(35, Marketing)在对面表中没有匹配的记录,所以它们不会出现在Inner Join的结果中,而Outer Join就可以让这些没有匹配的记录出现在结果中。

 

  ★★Left Outer Join: 指“LEFT OUTER JOIN”关键字左方的表中的记录都会出现在结果中,如:

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

 得到的结果是:

示意图为:

 

  ★★Right Outer Join: 指“RIGHT OUTER JOIN”关键字右方的表中的记录都会出现在结果中,如:

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

得到的结果是:

示意图为:

 

  ★★Full Outer Join: 全外联结,即左外联结和右外联结的综合(并集)。如:

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

得到的结果为:

 

 

  Self Join: 指表与自身的联结。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值