? 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 聯接提示
此示例在 authors、titleauthors 和 titles 表之間建立三表聯接，以生成一個作者及其著作的列表。查詢優化器使用 MERGE 聯接將 authors 和 titleauthors (A x TA) 聯接在一起。然后，將 authors 和 titleauthors MERGE 聯接 (A x TA) 的結果與 titles 表進行 HASH 聯結以生成 (A x TA) x T。
重要? 指定聯接提示后，要執行 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
下面是結果集：
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 邏輯運算符返回滿足第一個（頂端）輸入與第二個（底端）輸入聯接的每一行。
作者Blog：http://blog.csdn.net/softj/