理解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一側的表中每一條記錄與另一側表中的所有記錄連接起來，得到的是兩側表中所有記錄的笛卡兒積。