# blog.Toby

BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
 130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks

join 方式
（1) cross join
参与select语句所有表的的所有行的笛卡尔乘积
select au_lname ,title
from authors cross join  titiles

outer  join 对参与join的两个表有主从之分，处理方式以主表的每条数据去match 从属表的列，合乎条件的数据是我们所要的答案，不合乎条件的也是我们要的答案，只不过哪些从属表选取的列将被添上null。
(2) left join
左边的为主表，右边为从属表
select a.cust_id ,b.order_date,b.tot_ant
from customer a left join sales b
on (a.cust_id =b.cust_id and b.order_date>''1996/10/15'')
可以写为
select a.cust_id,b.order_date,b.tot_ant
from custom a
left join (select * from sales where order_date>''1996/10/15'') b
on a.cust_id =b.cust_id
(3) right join
左边的表为从属表，右边的表为主表
(4) self join
self join 常用在同一表内不同数据间对同一列的比较
select a.emp_no,a.emp_name,b.emp_no,b.emp_name,a.date_hired
from employee a
join employee b
on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
order by a.date_hired
这样会重复数据，只要加上一句 and a.emp_name>b.emp_name
(5) full join
不仅列出符合条件的数据，两边未符合join条件的数据也会一并列出。哪些未符合join条件的数据如果在select列中无法得到对应的值则填上null
select a.cust_id,b.tot_amt
from customer a full join sales b
on a.cust_id=b.cust_id

id   ic  name  amount
I    *        *
c
i
c
i
i
要求结果为
ic  name  amount  ic   name  amount
i                         c
i                         c
i
i
select aaa.*,bbb.*
from ( select (select count(id) from  aa as  b where (b.id<a.id) and (ic=''i'')) as     newid, * from aa a where ic=''i'') aaa
full join
(select (select count(id) from aa as b where b.id<a.id and ic=''c'') as newid,* from
aa a where ic=''c'') bbb
on aaa.newid=bbb.newid
order by aaa.name
6.使用 HASH 和 MERGE 联接提示

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + '' '' + LTRIM(a.au_lname)), 1, 25)
AS Name, SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta
ON a.au_id = ta.au_id INNER HASH JOIN titles t
ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC

Warning: The join order has been enforced because a local join hint is used.
Name                      Title
------------------------- --------------------
Abraham Bennet            The Busy Executive''s
Reginald Blotchet-Halls   Fifty Years in Bucki
Cheryl Carson             But Is It User Frien
Michel DeFrance           The Gourmet Microwav
Innes del Castillo        Silicon Valley Gastr
...                    ...
Johnson White             Prolonged Data Depri
Akiko Yokomoto            Sushi, Anyone?

(25 row(s) affected)

### (7)Inner Join

Inner Join 逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。

posted on 2006-04-03 13:54 渠上月 阅读(268) 评论(0)  编辑  收藏 所属分类: sql (sqlServer)

 只有注册用户登录后才能发表评论。 网站导航: 相关文章: