理解T-SQL: JOIN语句

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


 

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

   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

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

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一侧的表中每一条记录与另一侧表中的所有记录联结起来,得到的是两侧表中所有记录的笛卡儿积。

posted @ 2009-10-13 00:44  shipfi  阅读(2134)  评论(0编辑  收藏