SQL SERVER 关于外联接(Outer Join)及其他

http://kitesky.itpub.net/post/909/23464

SQL SERVER 关于外联接(Outer Join)及其他

一 使用外联接

　　以前在Oracle中用=(+)和(+)=来进行左外联接和右外联接；后来用SQL Server时用*=和=*进行外连接左外联接和右外联接；
现在决定用SQL-92的标准方法：[OUTER] JOIN，OUTER是可以省略的。

　　LEFT OUTER JOIN 或 LEFT JOIN        表示左外联接 

　　RIGHT OUTER JOIN 或 RIGHT JOIN   表示左外联接 

　　FULL OUTER JOIN 或 FULL JOIN        表示左外联接

　　外联接的意思不用多说，我们都懂，但是JOIN到底怎么用呢？没有找到很好的资料，只能从例子中学习了：

　　1、这个例子也许没有实际意义，只是为了说明问题：

CREATE TABLE orders(order_id  int , firm_id  int , p_id  int )

CREATE TABLE firms (firm_id  int , f_name  int )

CREATE TABLE products(p_id  int , p_name  int )

select a.order_id, b.f_name, c.p_name

from orders a left join firms b on a.firm_id  =  b.firm_id

left join products c on a.p_id  =  c.p_id

　　说明：orders表是主表，先和从表firms进行左联接，再和从表products进行左联接。

　　判断是外联接中的主表还是从表主要看from从句中各个表在LEFT JOIN或RIGHT JOIN两边的位置：LEFT JOIN左边的表是主表，RIGHT JOIN右边的表是主表；

　　ON表达了两个表连接的条件，一般外联接是等值联接，不等值联接意义不大；

　　在多个表的连接中，一个表既可以做主表又同时可以做从表，为了说明这个问题，我们修改以上SQL为：

select a.order_id, b.f_name, c.p_name

from orders a left join firms b on a.firm_id  =  b.firm_id

right join products c on a.order_id  =  c.p_id

　　这个SQL没有什么意义，但从中可以看出a表既是b的主表又是c的从表；到底怎么用，还是要根据实际情况来决定是左联接还是右联接；

　　那天，看到了这样一个例子：

create table tab1 (c1  int , c2  int , c3  int ) create table tab2 (c1  int

, c2  int , c3  int ) create table tab3 (c1  int , c2  int , c3  int )

create table tab4 (c1  int , c2  int , c3  int ) SELECT  *

FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3  =  tab2.c3

left OUTER JOIN tab3 right OUTER JOIN tab4 ON tab3.c1  =  tab4.c1

ON tab2.c3  =  tab4.c3

　　这种用法还真少见，具体怎么个意思，还在理解中...我把它改写成：

SELECT  * FROM tab1 left JOIN tab2 ON tab1.c3  =  tab2.c3

LEFT OUTER JOIN tab4 ON tab2.c3  =  tab4.c3

RIGHT OUTER JOIN tab3  ON tab3.c1  =  tab4.c1

　　也许它们是一个意思。我发现加个括号，看的更清楚一些（它是个嵌套）

SELECT  * FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3  =  

tab2.c3 left OUTER JOIN (tab3 right OUTER JOIN tab4 ON tab3.c1 

=  tab4.c1) ON tab2.c3  =  tab4.c3

　　二 外联接中 "ON + AND" 与 "ON + WHERE" 的区别

　　1、on条件是外联接时在生成临时表时使用的联结条件，不论从表是确定值还是NULL，主表所有的值都会出现；

　　如果再加上and条件； 如果and条件引用的是主表的列，则对结果毫无影响，主表的所有纪录依然会全部出现；如果and条件引用的是从表的列，则不符合条件的从表纪录显示NULL；

　　2、where条件是在临时表生成后，再对临时表进行过滤的条件。临时表中的所有纪录都受影响，不符合条件的纪录被过滤出结果集；

　　3、示例：

select a.module_id, a.name, b.module_name

from fb_autocoding a left join fb_app_module b on a.module_id  =  

b.module_id and b.module_internal_label  <>   ' LO ' ;

select a.module_id, a.name, b.module_name

from fb_autocoding a left join fb_app_module b on a.module_id  =  

b.module_id where b.module_internal_label  <>   ' LO ' ;

　　三 其他Join运算

　　merge join：在处理其他联结之前，先把相关两个表联结在一起；

　　hash join：把一个表join到已经被执行过join的结果上；

　　用括号改变join的顺序：

select catalog.item, catalog.item_color, product.item, color.color_name

from catalog full outer join (product cross join color) on catalog.item  =  

product.item and catalog.item_color  =  color.color_name;

posted on 2007-07-12 15:44 junky 阅读(848) 评论(1)  编辑  收藏 所属分类: SQL Server 、database