（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)
