? join 方式
（1) cross join
????? 參與select語句所有表的的所有行的笛卡爾乘積
????? select au_lname ,title
????? from authors cross join? titiles
???? outer? join 對參與join的兩個表有主從之分，處理方式以主表的每條數(shù)據(jù)去match 從屬表的列，合乎條件的數(shù)據(jù)是我們所要的答案，不合乎條件的也是我們要的答案，只不過哪些從屬表選取的列將被添上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 常用在同一表內(nèi)不同數(shù)據(jù)間對同一列的比較
??? 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
?? 這樣會重復數(shù)據(jù)，只要加上一句 and a.emp_name>b.emp_name
(5) full join
???? 不僅列出符合條件的數(shù)據(jù)，兩邊未符合join條件的數(shù)據(jù)也會一并列出。哪些未符合join條件的數(shù)據(jù)如果在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??
?? 要求結(jié)果為?
??? 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 聯(lián)接提示
此示例在 authors、titleauthors 和 titles 表之間建立三表聯(lián)接，以生成一個作者及其著作的列表。查詢優(yōu)化器使用 MERGE 聯(lián)接將 authors 和 titleauthors (A x TA) 聯(lián)接在一起。然后，將 authors 和 titleauthors MERGE 聯(lián)接 (A x TA) 的結(jié)果與 titles 表進行 HASH 聯(lián)結(jié)以生成 (A x TA) x T。
重要? 指定聯(lián)接提示后，要執(zhí)行 INNER JOIN 時 INNER 關鍵字不再為可選，而必須顯式說明。
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
下面是結(jié)果集：
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 邏輯運算符返回滿足第一個（頂端）輸入與第二個（底端）輸入聯(lián)接的每一行。
作者Blog：http://blog.csdn.net/softj/