四种联系(join)的区别及用法

• 链接：

作为动词，它表示将两个或多个表的内容结合在一起并产生一个结果集，该结果集对每个表的列和行进行合并。表的联接一般都使用它们共有的数据。例如，您可以对有一个共同 pub_id 列的 titles 表和 publishers 表联接，产生一个包含书名信息和出版商信息的结果集。

作为名词，表示对表进行联接的过程或结果，如在术语"内部联接"中表示对表联接的一种特殊的方法。

• 联接条件（join condition）

一个比较子句，它指定了表是如何通过它们的联接字段相联系的。最普通的联接条件是相等（一个等联接），在等联接中联接字段的值必须相同。例如，您可以通过在 titles 表和 publishers 表的 pub_id 列中查找相匹配的值联接这两个表。然而，任何比较运算符都可以是比较条件的一部分。

• 内部联接（inner join）

一个联接，在该联接中只有当联接字段的值满足某些特定的准则时才将两个表的记录进行结合并添加到一个查询结果中。例如，在查询设计器视图中，表之间的缺省联接是一个内部联接，它只有当联接字段的值相等时才从两个表中选择记录。

• 外部联接（outer join）

一个联接，该联接还包括那些和联接表中记录不相关的记录。您可以创建一个外部联接的三种变形来指定所包括的不匹配行：左外部联接、右外部联接和完全外部联接。

• 左外部联接（left outer join）

一种外部联接类型，在该联接中包括第一个命名表（左边的表，它出现在 join 子句的最左边）的所有行。右边表中没有匹配的行不出现。例如，您可以在 titles 表和 publishers 表之间创建一个左外部联接，以包括所有的书名，不论书名有无出版商的信息。

• 右外部联接（right outer join）

一种外部联接，在该联接中包括第二个命名表（右边的表，出现在 join 子句中的最右边）的所有行。不包括左边表中没有匹配的行。例如，titles 表和 publishers 表之间的一个右外部联接将包括所有的出版商，甚至包括那些在 titles 表中没有书名的出版商。

以上是msdn中对链接的定义。现在我们就从这四种链接所使用的不同方法来看他们的结果有什么不同。

titles 表sh(书号)  ph(出版商编号)232342  0010432  00382478123 005

publishers 表
ph(出版商编号) mc(出版商名称)
001 红虎
002 rmh
003 hazl

现要把这两个表的内容合成如下的表结构：

sh(书号)    ph(出版商编号)        mc(出版商名称)

现在看看采用四种链接方法的结果会有什么不同。先说说他们的命令：

内联接：
sele titles.sh,publishers.ph,publishers.mc ;
  from titles inner join publishers ;    &&内联接中的inner是可以省略的
    on titles.ph=publishers.ph

外联接：
sele titles.sh,publishers.ph,publishers.mc ;
  from titles outer join publishers ;
    on titles.ph=publishers.ph

左联接：
sele titles.sh,publishers.ph,publishers.mc ;
  from titles left join publishers ;
    on titles.ph=publishers.ph

右联接：
sele titles.sh,publishers.ph,publishers.mc ;
  from titles right join publishers ;
    on titles.ph=publishers.ph

大家可能看到，除了在join之前的那个关键字不同之外，其他地方是一模一样的，链接条件(即on那一部分)也是一样的。结果：

内链接：

232342  001 红虎0432  003 hazl

全链接：

232342  001 001 红虎null  null 002 rmh0432  003 003 hazl82478123 005 null null

左链接：

232342  001 001 红虎0432  003 003 hazl82478123 005 null null

右链接：

232342  001 001 红虎null  null 002 rmh0432  003 003 hazl

所以我们很容易记住：

1、左链接：就是以join的左边那个表为"主"，以titles.ph=publishers.ph为判断标准，不管右边的表有没有对应的记录，都要把左边表的记录放在结果中去，但右边表没有相应的记录那应该放个什么数值进去？答案是就放个null，表示没有。在左链接中，某记录在右边表，却不在左边表，那是不放进去结果去的，原因是左边表才是"主"，要不要放由它决定：它有的，就一定放进去，它没有的，就不要了。

2、右链接：和左链接一样，只不过为"主"的一方调过来了，换成是由右边做"主"。

3、内链接：和左、右链接不同，它一定要左、右两边都有的记录才会放进结果，如果有某个记录不存在于任何一边，那这个记录是不会出现在结果中去的。

4、外链接：跟内联接相，反，相当于左、右链接的合并：不管什么情况，只要某个记录出现在这两个表，就一定会出现在结果中去，然后象左、右链接的处理方法一样，用null来填充没有对应值的字段。

注：以上说的"有"、"没有"，意思是以titles.ph=publishers.ph为判断标准来下决定的。比如当前titles表的ph是"002"，而在publishers中，没有一个记录的ph的值是"002"的，所以就说"002"这个值在titles有，在publisher中没有，这样titles.ph为"002"的记录就会被选中，最后放在结果中去。

大家如果想一下，这个on的作用跟where、having似乎有点类似，都是起到过滤的作用：根据条件选取所取的记录，而根据命令的工作流程，这个on是比where、having都要早执行的，而它里面的条件表达式又不一定是titles.ph=publishers.ph的形式，还可以继续扩充，变成一个很复杂的条件表达式，从而完成一个很有效的、where和having都不能实现的过滤功能。具体的比较请看 on、where、having的区别 一节。

刚才举的例子，表中的ph都是没有重复的。现在以内联接为例，举个判断字段中内容有重复的例子：

temp1  temp2aa  aa1  11  22  23  2

sele temp1.aa,temp2.aa ;  from temp1 join temp2 ;    on temp1.aa=temp2.aa

运行结果是：

1 11 12 22 22 2

很明显，有些记录重复了几遍。temp1.aa中的虽然只有1个2，但temp2.aa有3个2，所以结果就会有1＊3＝3个2了。如果temp1.aa而2个2的话，那结果就会有2＊3＝6个2了。

知道了这一点，在做多表链接查询的时候很有用。你要考虑第一、二个链接后的结果跟第三个表链接时，会不会出现这种情况？如果有，那是不是你想要的？如果有，那怎么处理？有些朋友说做这个命令的结果中有些记录会比正确的结果大几倍，就要看看是不是出现了这种重复算的情况。

学会了链接，在开始做之前，先要说一个很重要的问题：在视图设计器来看多个表的联接关系，它们之间的链接是用一条线连接起来的，看起来就象一串糖葫芦。如果一个表同时和三个表联接，那看起来就象一支分叉的树枝了，那这种情况结果就不对了。大家可能不明白我在说什么，我举个例子大家就会明白了。

有一个产品表、一个进货明细表、一个出货明细表，现在的要求是要求产品表中所有的产品的进、出情况，也就是把三个表象join命令那样合成一个表，如果没有相应的进、出记录，也照样列出来但不计较null值。刚开始学的朋友很可就会这样做：

1、 在设计器里添加这三个表；

2、 然后用产品表中的产品编号分别与其它二个表左链接，这样产品表中就有二个链接(也就是二条线了)；

3、 然后把三个表的字段都做为输出字段。

但结果呢？不对。只有一个表的记录出现在结果中，即使把四种链接类型都试一下，结果都是不对的。

为什么呢？我估计是以下原因：如果产品表只与进货表链接的话，系统根据产品表和进货表的联接关系，以产品表为左表，和进货这个右表组成一个临时结果，然后又以临时表为左表，再去找进货表的右边表。而进货表的右边没有表，这时系统就停止链接，交给where去过滤了。但现在产品表同时跟二个表左联接，系统会自动选其中一个先进行链接，链接结果出来后，这个临时结果的右边就没有表了，系统就停止链接动作了。剩下的出货表、退货表都还没链接，所以那个表等于没用。

解决的方法是：进货表用进货表的产品编号全链接产品表，然后产品表又用产品表的产品编号全链接出货表，进、货表的顺序可以调过来，但产品表一定要在中间，且两个链接类型都是全链接，否则结果都不对。这样的链接情况，在设计器里按链接中的各个表的左右顺序排起来，很直观的：就是一串！没有分叉。这个方法的实现过程就是：

进货表全链接产品表，即使某种产品没有进货，但得出来的结果也一样有这个记录，只是它的进货内容是null值。然后这个临时结果又跟出货表全链接，这次的结果就前一步差不多，有出货内容的记录就有出货数量，否则就是null值。因为没有分叉，所以全部表都链接进去了，结果也就对了(当然如果链接类型错了，结果也是不对的)。

看了刚才那个问题之后，还有一个问题也要说一下。在刚才那个例子中，如果产品表中某个产品编号出现了重复，有n个记录的编号相同，而在进货表里这个编号的记录也出现m个，这样一来，结果就有点不同了。首先在进货表跟产品表的全链接结果里，这个编号就会出现n＊m次，就不是一次了。然后这个临时表再去跟出货表全链接时，即使这个编号在出货表里出现一次，但在最后的链接结果中，这个编号还是会出现n＊m次，那它的出货记录也重复了n＊m次了。如果现在要sum()出货记录的话，那出货数量就会放大了n＊m倍了，进货记录也不准了。所以如果产品表中的编号有重复的话，那结果就很可能会不对了。

但产品表的编号没有重复，那结果就一定会正确呢？也未必。大家试一下，假设进货表和产品表的编号"001"都是只出现一次，但出货表中就出现了二次。那最后的结果中"001"还是出现了二次，二次的产品名称、进货数量都是相同的，只是出货数量不同而已。如果这时sum()，结果还是不对。

所以如果想在多表链接后进行sum()之类的汇总操作，使用以上的方法是不行的。解决方法是使用union，用它来将进货的汇总情况跟出货的汇总情况合起来，从而避免互相干扰。

另注：3个表之间的连接

eg：      select a.* b.field1,c.field2 from table a left outer jion table b on a,field1=b.field1 left outer join table c on a.field2=c.field2