SQL Server的Inner Join及Outer Join

轉自：http://www.williamlong.info/info/archives/50.html

 

在一個正規化的資料庫環境中, 我們常會碰到這款情形: 所需的資料並不是放在同一個資料表中, 在這個時候, 你就要用到 Join

　　當然 Join 如何將不同的資料庫的資料結合, 還要看你如何使用它, 一共有四種不同的 Join 的方式, 在這篇文章中我們將為你介紹 Inner Join 及 Outer Join 以及其應用

　　Inner Join

　　Inner Join 應該是最常用的 Join 方式, 它只會傳回符合 Join 規則的紀錄, 還是先來看看文法

　　Select <要選擇的欄位> From <主要資料表>

　　<Join 方式> <次要資料表> [On <Join 規則>]

　　現在我們利用 MS SQL 內建的北風資料庫來實際練習一下! 想多瞭解 MS SQL 的內建資料庫, 你可以看看 SQL Server 的內建資料庫 這篇文章

　　請開啟 QA (Query Analyzer), 為了使用北風資料庫, 請先執行 Use Northwind, 然後執行

　　Select ProductId, ProductName, SupplierId From Products

　　從 Products 產品資料表中取出三個欄位, 分別是產品代碼, 產品名稱, 供貨商代碼, 但查詢出來的結果保證讓你的老闆很不滿意, 因為供貨商代碼對於人類實在是無蝦米意義, 這個時候 Join 就可以幫上忙了, 藉由 Join Suppliers 這個資料表我們便可以查詢到供貨商名稱

Select ProductId, ProductName, Suppliers.SupplierId,Suppliers.CompanyName From Products Inner Join Suppliers on Products.SupplierID = Suppliers.SupplierID order by ProductName

　Outer Join

　　這款的 Join 方式是一般人比較少用到的, 甚至有些 SQL 的管理者也從未用過, 這真是一件悲哀的代志, 因為善用 Outer Join 是可以簡化一些查詢的工作的, 先來看看 Outer Join 的文法

　　Select <要查詢的欄位> From <Left 資料表>

　　<Left | Right> [Outer] Join <Right 資料表> On <Join 規則>

 

文法中的 Outer 是可以省略的, 例如你可以用 Left Join 或是 Right Join, 在本質上, Outer Join 是 inclusive, 叫它做包容性吧! 不同於 Inner Join 的排他性, 因此在 Left Outer Join 的查詢結果會包含所有 Left 資料表的資料, 顛倒過來講, Right Outer Join 的查詢就會包含所有 Right 資料表的資料, 接下來我們還是來做些實際操作, 仍然是使用北風資料庫, 但要先做一些小小的修改, 才能達到我們要的結果

　　首先要拿掉 Products 資料表的 Foreign Key, 否則沒有法度在 Products 資料表新增一筆 SupplierId 沒有對映到 Suppliers 資料表的紀錄, 要知影一個資料表的 Constraint 你可以執行 SQL 內建的 sp_helpconstraint , 在 QA 執行

　　sp_helpconstraint Products

　　接下來刪除 FK_Products_Suppliers 這個 Foreign Key

　　Alter Table Products

　　Drop Constraint FK_Products_Suppliers

　　再來新增一筆紀錄於 Products 資料表, SupplierId 使用 50 是因為它並沒有對映到 Suppliers 資料表中的記錄

　　Insert Into Products (ProductName,SupplierId,CategoryId)

　　values ('Test Product','50','1')

　　現在我們再執行頭前的查詢, 只是將 Inner Join 改為 Left Outer Join

 

Select ProductId, ProductName, Suppliers.SupplierId,Suppliers.CompanyName From Products Left Outer Join Suppliers

 

 on Products.SupplierID = Suppliers.SupplierID order by ProductId

 

 

比較一下兩種 Join 方式的查詢結果, 你應該就會知影其中的差別!

　　再來看看 Right Outer Join, 請新增下底這筆記錄

　　Insert Into Suppliers (CompanyName)

　　values ('LearnASP')

　　現在請使用 Right Out Join 來作查詢, 比較看看查詢的結果和 Inner Join 有什麼不同!

　　尋找不相符紀錄

　　這裡我們來看看如何使用 Out Join 來找不相符紀錄, 可能是有子紀錄卻沒有父紀錄或是顛倒過來

Select Suppliers.CompanyName From Products Right Join Suppliers On Products.SupplierId = Suppliers.SupplierId

 Where Products.SupplierId is Null

執行結果你會找到一筆資料為 LearnASP, 該筆供貨商資料存在, 但基本上已經沒有產品是來自這個供貨商, 想象一下如果不用 Outer Join 你要怎麼以一個 SQL 指令完成同一查詢結果! 知道 Outer Join 的好用了吧! 再執行 

Select Products.ProductName From Products Left Join Suppliers On Products.SupplierId = Suppliers.SupplierId

 Where Suppliers.SupplierId is Null 

這個查詢結果你會發現 Test Product 這項產品竟然找不到供貨商的資料!