关于JOIN（转载）
2009-09-15 08:27:33| 分类：
dbms
| 标签：
|举报
|字号大中小 订阅
在数据库的设计过程中常常按照第三范式来设计数据库，当然在有些场合为优化数据库的性能而增加相关冗余字段会使表的结构不符合3NF，在多数场合中，没有一张表能完整的包含客户所需要的所有数据。这样就需要通过联接JOIN多张在逻辑上存在依赖关系的表，检索所需要的数据。
理论上，把Join抽象成模型后可以归纳为Natural和Theta Join，大体上就是等值联接和非等值联接。
SQL89标准在 WHERE 子句中指定联接（内联接），SQL92标准也兼容这种方式。对于较简单的联接，使用这种方式可能较方便，但综合来说，不推荐使用该语法联接表。
SQL-92标准推荐在 FROM 子句中指定联接，这样可以很清楚的看出表之间的联接条件。就可读性以及后续的可修改性与 WHERE 子句相比有较大的优势。
SQL-92标准所定义的FROM子句的连接语法格式为：
FROM join_table join_type join_table [ON (join_condition)]
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。
连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。所以只有某些数据类型列可以进行直接连接。
join_type可分为如下三类
一. 内联接INNER JOIN
内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。
根据所使用的比较方式不同，内连接又分以下三种。
a.等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
b.不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
c.自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
SELF JOIN(自联接)实际上是一种特殊的INNER JOIN；自连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
二.外连接OUTER JOIN
外连接分为左外连接(LEFT OUTER JOIN/LEFT JOIN)、右外连接(RIGHT OUTER JOIN/RIGHT JOIN)和全外连接(FULL OUTER JOIN/FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
1.左外联接：左外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2.右外联接：右外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3.全外联接：全外联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合 WHERE 或 HAVING 搜索条件。
在特定场合下， FULL OUTER JOIN 相当于 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 的消除重复行的合集。
Note:
Outer Join和Inner Join的区别就在于未匹配行在结果集中的出现情况。
对于外关联，只有on子句里面才是关联条件；A left join B等同于B right join A。
在没有任何where子句的情况下，A left join B的结果集的记录数永远是A表中的记录行数。
外联结的操作性不象内联结那样可交换性， A left join B和B left join A 是完全不同的含义。
所以大多数情况下，数据库的优化器通常会严格按照SQL的代码书写顺序来依次进行Join操作。 在写多表外联结的时候就要小心，保证最终出来的结果符合你的语义需求。
On子句的位置往往决定了Join的先后次序。
对于外联结来说，Where只是提供Join操作结果集上的过滤或者说是搜索。
对于outer join来说，良好的书写习惯是:
用on子句说明联结条件；用Where子句来说明search条件。
由于A left join B和B right join A的等效性，在很多时候说左表和右表就比较容易混淆了。所以在一些资料中, 会使用Inner table(内表)和Outer Table(外表)来区分
Inner Table:内表就是只提供匹配记录信息的表；
Outer Table:外表是指对外联结同时提供未匹配记录信息的表。
对于inner join来说,参与join的表都是内表；
对于A left outer join B来说， A就是外表，B就是内表。
对于outer join来说，遵循如下规律：
<1>对于外联结，一般来说，结果集合的记录数是由Outer table来决定的；
<2>对于Outer table的过滤条件，必须放在where子句中才能生效。在where子句中对Outer table进行过滤后，再进行join操作。
<3>如果把Outer table的过滤条件放到On子句中，并不会影响到结果集中的记录数。它所能影响的只是在结果集中的记录中，哪些记录对应的inner table的信息会以null形式出现。
<4>在Where条件中如果对Inner Table进行过滤操作，那么虽然在SQL语句中出现了left/right/full outer join的关键词。但实际上的实现方式是内联结，优化器会以inner join方式实现。
原因：因为内表只提供匹配记录信息，对于这些信息进行过滤，最终的操作与inner join等效；
<5>把Inner table的过滤条件放到On子句中，相当于对Inner table先进行过滤Outer table和过滤后的inner table子集进行join操作。
综上，在outer join操作中，如果想进行过滤操作，
对于Outer table，放在where子句中,
对于Inner table，放在On子句中；
把Inner table的过滤操作放在Where子句中，转化称为内联结，
把Outer table的过滤操作放在On子句中，只是让不满足过滤条件的记录其相应的关联字段取值为null。
三. 交叉联接CROSS JOIN
没有 WHERE 子句的情况下交叉联接返回左右表中的所有行组合。交叉联接也称作笛卡尔积(Cartesian product)。
把inner关键词换成cross即可.这时候on关联条件不能出现. 下面两句等同
(a)select ...
from table_a cross join table_b ...
(b)select ...
from table_a, table_b ...
评论这张
转发至微博
转发至微博
关闭
玩LOFTER，免费冲印20张照片，人人有奖！
我要抢>
<#--最新日志，群博日志-->
<#--推荐日志-->
<#--引用记录-->
<#--博主推荐-->
<#--随机阅读-->
<#--首页推荐-->
<#--历史上的今天-->
<#--被推荐日志-->
<#--上一篇，下一篇-->
<#-- 热度 -->
<#-- 网易新闻广告 -->
<#--右边模块结构-->
<#--评论模块结构-->
<#--引用模块结构-->
<#--博主发起的投票-->
评论