理解T-SQL: JOIN语句
时间：2010-09-17 来源：英雄不问出处
1. 联接查询JOIN包含了以下几种类型：
Inner Join / Outer Join / Full Join / Cross Join
下面具体讨论这几种Join的用法
2. 关于数据表
本次讨论的前提是基于以下两张数据表
●Northwind.Employees
EmployeeID LastName FirstName City Country ReportsTo
----------- -------------------- ---------- --------------- --------------- -----------
1 Davolio Nancy Seattle USA 2
2 Fuller Andrew Tacoma USA NULL
3 Leverling Janet Kirkland USA 2
4 Peacock Margaret Redmond USA 2
5 Buchanan Steven London UK 2
6 Suyama Michael London UK 5
7 King Robert London UK 5
8 Callahan Laura Seattle USA 2
9 Dodsworth Anne London UK 5
以上雇员信息包括了id,名，姓，城市，国家，领导ID(ReportsTo)等信息
●Northwind.Products
ProductID ProductName CategoryID UnitPrice
----------- ---------------------------------------- ----------- ---------------------
1 Chai 1 18.00
2 Chang 1 19.00
3 Aniseed Syrup 2 10.00
4 Chef Anton's Cajun Seasoning 2 22.00
5 Chef Anton's Gumbo Mix 2 21.35
6 Grandma's Boysenberry Spread 2 25.00
7 Uncle Bob's Organic Dried Pears 7 30.00
8 Northwoods Cranberry Sauce 2 40.00
9 Mishi Kobe Niku 6 97.00
10 Ikura 8 31.00
11 Queso Cabrales 4 21.00
12 Queso Manchego La Pastora 4 38.00
13 Konbu 8 6.00
以上，物品表包括了ID,物品名，种类ID,单价等信息
●Northwind.Categories
CategoryID CategoryName Description
----------- --------------- ---------------------------------------
1 Beverages Soft drinks, coffees, teas, beers, and ales
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings
3 Confections Desserts, candies, and sweet breads
4 Dairy Products Cheeses
5 Grains/Cereals Breads, crackers, pasta, and cereal
6 Meat/Poultry Prepared meats
7 Produce Dried fruit and bean curd
8 Seafood Seaweed and fish
以上，种类表包括了种类ID,种类名，描述等信息。
3.Inner Join
Inner Join是最常用的Join类型，基于一个或多个公共字段把记录匹配到一起。Inner Join只返回进行联结字段上匹配的记录。
如：select * from Products inner join Categories on Products.categoryID=Categories.CategoryID
以上语句，只返回物品表中的种类ID与种类表中的ID相匹配的记录数。这样的语句就相当于：
select * from Products, Categories where Products.CategoryID=Categories.CategoryID [换成这样的形式，就比较熟悉了]
Inner Join是在做排除操作，任一行在两个表中不匹配，注定将从结果集中除掉。（我想，相当于两个集合中取其两者的交集，这个交集的条件就是on后面的限定）
还要注意的是，不仅能对两个表作联结，可以把一个表与其自身进行联结。拿Employee表来说：我想得出这样的一个表：
员工ID 员工名 员工姓 上级ID 上级名 上级姓
---------- --------- ---------- --------- -------- -----------
通过Inner Join，操作很简便:
select E.EmployeeID,E.LastName,E.FirstName,R.EmployeeID,R.LastName,R.FirstName
from Employees E Inner Join Employees R on E.EmployeeID=R.EmployeeID
更简化的写法可以这样写：
select E.EmployeeID,E.LastName,E.FirstName,R.EmployeeID,R.LastName,R.FirstName
from employees E,employees R where E.ReportsTo=R.EmployeeID
from employees E,employees R where E.ReportsTo=R.EmployeeID
4. Outer Join
Outer Join包含了Left Outer Join 与 Right Outer Join. 其实简写可以写成Left Join与Right Join
这两个与Inner Join的区别，及它们自身的区别在什么地方呢？
还是看Employees的例子：
select E.EmployeeID,E.LastName,E.FirstName,R.EmployeeID,R.LastName,R.FirstName
from employees E Inner Join employees R onE.ReportsTo=R.EmployeeID
from employees E Inner Join employees R onE.ReportsTo=R.EmployeeID
EmployeeID LastName FirstName EmployeeID LastName FirstName
----------- -------------------- ---------- ----------- -------------------- ----------
1 Davolio Nancy 2 Fuller Andrew
3 Leverling Janet 2 Fuller Andrew
4 Peacock Margaret 2 Fuller Andrew
5 Buchanan Steven 2 Fuller Andrew
6 Suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
8 Callahan Laura 2 Fuller Andrew
9 Dodsworth Anne 5 Buchanan Steven
select E.EmployeeID,E.LastName,E.FirstName,R.EmployeeID,R.LastName,R.FirstName
from employees E Left Outer Join employees R on E.ReportsTo=R.EmployeeID
from employees E Left Outer Join employees R on E.ReportsTo=R.EmployeeID
EmployeeID LastName FirstName EmployeeID LastName FirstName
----------- -------------------- ---------- ----------- -------------------- ----------
1 Davolio Nancy 2 Fuller Andrew
2 Fuller Andrew NULL NULL NULL
3 Leverling Janet 2 Fuller Andrew
4 Peacock Margaret 2 Fuller Andrew
5 Buchanan Steven 2 Fuller Andrew
6 Suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
8 Callahan Laura 2 Fuller Andrew
9 Dodsworth Anne 5 Buchanan Steven
select E.EmployeeID,E.LastName,E.FirstName,R.EmployeeID,R.LastName,R.FirstName
from employees E Right Outer Join employees R on E.ReportsTo=R.EmployeeID
from employees E Right Outer Join employees R on E.ReportsTo=R.EmployeeID
EmployeeID LastName FirstName EmployeeID LastName FirstName
----------- -------------------- ---------- ----------- -------------------- ----------
NULL NULL NULL 1 Davolio Nancy
1 Davolio Nancy 2 Fuller Andrew
3 Leverling Janet 2 Fuller Andrew
4 Peacock Margaret 2 Fuller Andrew
5 Buchanan Steven 2 Fuller Andrew
8 Callahan Laura 2 Fuller Andrew
NULL NULL NULL 3 Leverling Janet
NULL NULL NULL 4 Peacock Margaret
6 Suyama Michael 5 Buchanan Steven
7 King Robert 5 Buchanan Steven
9 Dodsworth Anne 5 Buchanan Steven
NULL NULL NULL 6 Suyama Michael
NULL NULL NULL 7 King Robert
NULL NULL NULL 8 Callahan Laura
NULL NULL NULL 9 Dodsworth Anne
看到以上区别了吗？
left join，right join要理解并区分左表和右表的概念，A可以看成左表,B可以看成右表。
left join是以左表为准的.,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.
right join和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
5. Full Join
Full Join 相当于把Left和Right联结到一起，告诉SQL Server要全部包含左右两侧所有的行，相当于做集合中的并集操作。
6. Cross Join
与其它的JOIN不同在于，它没有ON操作符，它将JOIN一侧的表中每一条记录与另一侧表中的所有记录联结起来，得到的是两侧表中所有记录的笛卡儿积。
相关阅读 更多 +
