[SQL] 查詢語法基本介紹 Part 4 (關連線處理/合併查詢_Join)

-

基本概念：

將儲存在多個資料表的欄位值取出，並使用合併查詢合併成所需要的查詢結果(一張大資料表)。
通常是使用資料表之間的關聯欄位來進行查詢，但也可以不使用關聯性建立資料查詢。


*將因正規化分散的資料合併成一張表(JOIN)*

Select (要找什麼樣的資料欄位)
From (從哪些資料表)
Where (各表之間怎麼連/各表之間的關聯性)

備註：基本上JOIN的操作都會需要使用ER圖！

類型介紹：

1. INNER JOIN 內部合併查詢

查詢在兩張資料表中，彼此都存在且符合合併查詢條件的資料。
若各自只出現在自己的資料表中的資料則全部不要。

換個說法就是，兩邊資料表都有資料時才會合併，對方沒有的就刪掉(左右不相等的就刪掉/其中一邊有NULL值的也會刪掉。)


範例：

列出身分證號、姓名、地址、電話

SELECT
userinfo.uid, cname, address, tel

FROM userinfo, live, house, phone
WHERE
userinfo.uid = live.uid AND
live.hid = house.hid AND
house.hid = phone.hid

2. LEFT (OUTER) JOIN 左側外部合併查詢

在合併的兩個資料表中，取回左邊資料表內的所有紀錄資料，不管右邊資料是否有相對應的資料存在。

換一種說法：

左側外部合併查詢中，關聯線的左側資料比較多。
除了兩邊共有的資料外，也取出左側的資料表的其他紀錄。

重點提醒：

若有合併欄位無資料，資料庫會給該欄位NULL值。
如果要讓NULL值顯示自訂的訊息。可用isnull(欄位, “自訂的訊息”)。



備註：
如果資料型態不同，需轉換可使用 cast(欄位 as 資料型態)

用參考索引控制多的資料在同一邊(參考索引所在的那一邊)
資料多的在左邊->left join
反之 right join
left + right = outer join


範例：

列出身分證號、姓名、地址、電話

SELECT
userinfo.uid,
cname,
address,
tel

FROM
userinfo LEFT JOIN live
ON userinfo.uid = live.uid
LEFT JOIN house
ON live.hid = house.hid
LEFT JOIN phone
ON house.hid = phone.hid


查詢空屋數

// 這個會跑表出來

Select house.hid, house.address, uid /*選定要顯示結果的範圍欄位*/
From /*選定資料表範圍/交代資料表間如何結合?*/
House left join live on
House.hid = live.hid
Where uid is null /*給定條件*/


//算空屋數，就是UID為NULL的數量，利用count function。

Select count(*)
From
House left join live on /*也可以改用 right join，只是兩者要對調，因為資料大小*/
House.hid = live.hid
Where uid is null

3. RIGHT (OUTER) JOIN 右側外部合併查詢

右側外部合併查詢可以取回右邊資料表內的所有紀錄，而不論是否在左邊資料表裏有存在合併欄位值。

換一種說法：關連線右側的資料比較多。

基本上跟左側外部合併查詢一樣，只是反過來資料量多的在右側。

4. CROSS JOIN 交叉合併查詢

是關聯式代數的卡笛生乘積運算，其查詢結果的記錄數是兩個資料表記錄數的乘積。
未設定關聯而形成交叉對應。


--cross join (沒有設Join條件，強制合併)
select * from userinfo, live



重點提醒：

基本上如果查詢使用Cross Join，指令幾乎是錯的。

要注意JOIN時資料間的關係/關連是否正確，避免Cross Join發生！

若資料庫龐大，cross join 會使資料庫全速運轉JOIN，直到資料庫完成指令。若要停止該查詢指令，只能把資料庫完全關閉。

5. FULL OUTER JOIN完全外部合併查詢 [同場加映]

取回左、右邊資料表內的所有資料。

進階練習：請列出每個人有多少支電話。(人>房子>電話)

解法一

select userinfo.uid,count(tel)
from
userinfo left join live on
userinfo.uid = live.uid
left join phone on
live.hid = phone.hid
group by userinfo.uid

解法二

select uid, sum(n) from
(
select userinfo.uid, iif(tel is null,0,1) as n
from
userinfo left join live on
userinfo.uid = live.uid
left join phone on
live.hid = phone.hid
) as a
group by uid

補充：iif() function 邏輯函式

IIF ( boolean_expression, true_value, false_value )

根據布林運算式判斷欄位值，並回傳true/false的指定值。


文章內容除取自上課筆記外，亦有參考書籍 SQL Server 2017/2016 資料庫設計與開發實務。

留言

張貼留言

Popular Posts

[T-SQL] T-SQL 基本介紹_筆記

-
變數形式 變數的形式主要分為兩大類 區域變數：使用者自訂的變數。 區域變數以@開頭。 如：@n 全域變數：屬於系統變數，以@@開頭。 例如：@@ERROR 變數宣告 變數的宣告方式，是在變數前方加上Declare單字，後方加上要指定給該變數的資料型態。 DECLARE @n int Declare @cname varchar(10) 註：T-SQL語言中，語法的大小寫並沒有差別。 變數的設定與顯示 設定變數有以下兩種方法： select @n = 100 set @a = 120 若要顯示變數的內容，則是如下方： select @n 將select的結果放入變數 /*將A01的姓名放入變數中*/ declare @cname varchar(20) select @cname = cname from userinfo where uid = 'A01' IF_判斷 IF @i > 10 BEGIN // 判斷式成立 END ELSE BEGIN // 判斷式不成立 END 註：如果 Begin End 中間只有單行程式碼，Begin End可省略不打。 IF @i > 10 // 判斷式成立 ELSE // 判斷式不成立 CASE_多個條件判斷 CASE語法主要是針對多個條件判斷需求所使用，但原則上多個條件判斷也可使用if...else。 declare @cname varchar(50) select @cname = cname from userinfo where uid = 'A03' print CASE @cname when '王力宏' then '姓王' when '周杰倫' then '姓周' else '不知姓什麼' End WHILE_迴圈 Declare @i int --迴圈要執行幾次的變數 Set @i = 0 --設初始值為零 while @i < 10 --當變數i小於10 BEGIN if @i = 5 --當變數i等於5 begin set@i=7  --設變數為...
閱讀完整內容

[SQL] 查詢語法基本介紹 Part 5 (視觀表 View)

-
基本介紹： 視觀表又稱檢視表，是一個虛擬的資料表。 視觀表本身並無儲存資料，只有定義資料，定義從那些資料表或檢視表挑出那些欄位或記錄。 雖然視觀表不是一個真正的資料表，是由SQL Command所產生。但是完全可以當成一般的資料表使用，可以新增、更新、刪除資料，但這些操作實際上是作用在其定義的來源資料表。 另一個角度來看，可以是將特定查詢的SQL Command以View的方式，儲存在資料庫裡。 在建立視觀表時，建議在名稱前方加上特定字串以供區別，如：VW_userinfo_taipei。 優點：隱藏/過濾原始資料，並簡化資料查詢。 達成邏輯資料獨立。 增加資料安全性。 簡化資料查詢。 簡化使用者觀點。 缺點：多一道建立過程，且沒有真正資料，所以有更多的操作限制。 執行效率差。 更多的操作限制。 增加管理的複雜度。 語法範例： 建立一個住在台北市民眾資料的視觀表 CREATE VIEW v_userinfo_taipei AS SELECT userinfo.uid, cname, address FROM userinfo, live, house WHERE userinfo.uid = live.uid AND live.hid = house.hid AND house.address LIKE = '台北市%' 注意！AS後面只能接一個SQL Command (巢狀/UNION ALL) 使用視觀表：與一般查詢資料表相同 --查詢住在台北市民眾資料的所有欄位 select * from v_userinfo_taipei --查詢住在台北市民眾資料共有幾筆 Select count(*) from v_userinfo_taipei 進階練習：查詢平均電話費大於300的費用 --先建立含有平均電話費的視觀表 create view vw_avg_fee as select tel, round(avg(fee),0) as avg_fee from bill group by tel --使用視觀表並篩選資料 select * from vw_avg_fee where avg_fee > 300 其他解法分享 (不使用VIEW的方...
閱讀完整內容

[SQL] MS-SQL資料庫卸離與備份還原

-
備份 1. 進入SSMS操作介面 2. 在要備份的資料庫上點選右鍵_工作_備份 3. 選擇備份類型 (Express版僅有完整與差異備份，enterprise版才有多交易紀錄備份。) 4. 做差異備份前一定要做完整備份，因為差異備份是備份對於上次完整備份後，所做的改變部分備份。 5. 選擇媒體選項 (覆寫媒體選擇_附加至現有/覆寫所有現有備份) 6. 確認備份位置 7. 按確定讓系統執行備份 通常 -> 每周備份一次完整。每天備份一次差異。每小時做一次交易紀錄備份。 還原 1. 進入SSMS操作介面 2. 資料庫_右鍵 3. 選取表單上的還原資料庫 4. 選取來源<檔案位置/裝置> 5. 按下確定_開始還原 卸離資料庫 卸離資料庫後，可在SQL安裝資料夾中將資料庫檔案直接複製，並移動到另外一台電腦上。 1. 進入SSMS操作介面 2. 資料庫_右鍵_卸離 3. 點選確定即卸離完成 備註： 卸離後拷貝檔案需要卸離資料庫後才可備份檔案，備份速度為被複製檔案的大小差別。 附加資料庫 1. 進入SSMS操作介面 2. 資料庫_右鍵_附加 3. 選擇要附加的資料庫 4. 點選確定即完成附加
閱讀完整內容