iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 18
1

SQL 關聯語句 - join(Left, inner…)

今天筆者要討論的東西是 join 這個詞句,
join 這個詞的應用情境,
是在我們要針對具有『關聯』的 table 做查詢會用到的。

何謂關聯

『關聯』這是 SQL 資料庫最主要的特性之一,
現實世界中,許多類別的資料會互相關聯,
例如這段描述:

一筆訂單,會有好幾筆的產品,而一筆訂單,又屬於一筆會員。

這段描述總共有三個 table,
『訂單』、『產品』和『會員』
而因此我有可能會有這種查詢需求:
『 找出所有訂單金額大於 1000 的每個會員和訂單的資料 』,
那這個時候,就會有機會使用 join ,
來幫助我們在這個關聯基礎下,做出篩選查詢。

再一個例子,我們除了使用之前的 Table - users 外,
我們也新增兩個 Table ,
“classes 課”, “ departments 系別”
他們的關係為:

一個課只能給一個使用者上,但一個學生可以有很多門課。而一個系可以開很多門課。

這邊也會是所謂的 『關聯』

關聯的類型

而這些關聯可以粗估分成三種關係:
『一對一』,『一對多』,『多對多』
我們可以先用充滿粉紅泡泡的愛情,舉幾個簡單例子,

一對一,就像是下圖,
https://ithelp.ithome.com.tw/upload/images/20181101/20107758sIG8OXk9Pk.png
這是一個充滿承諾與信用的兩人關係,
他只屬於她,她也只屬於他,
兩人之前沒有和其他人有別的關聯,
這就是『一對一』的關係。

一對多,這段感情開始有了變化,
https://ithelp.ithome.com.tw/upload/images/20181101/20107758cFP0EIGQUz.png
這個男的開始對回教文化產生興趣,
但是產生興趣的點,是一夫多妻這回事,
所以呢,他開始和不同的女性有了關聯,
因此這些女性只分別和他有關聯,這個男的則和多位女性有關聯,
這就是所謂『 一對多 』的關係。

多對多,是一個超時代概念的誕生,
https://ithelp.ithome.com.tw/upload/images/20181101/20107758AiqYgLoUaq.png
當作我們處於大哆拉A夢時代,西元 2200 年,
這是一個每個人都可以擁有許多伴侶的年代,
因此一個女性可以和多位男性擁有關聯,
而一個男性,也能和多位女性擁有關聯,
彼此間都擁有錯綜複雜的關聯,
這就是所謂『多對多』的關係。

我們套到正常一點,晚點會用到的表格,
users 和 classes 會是一對多的關聯,
classes 和 departments 也會是一對多的關聯,
users 和 departments 會『透過 classes』 ,具有多對多的關聯。

因此我們總共有以下這三個 Table

users

id name gender age hobby
1 John male 23 basketball
2 May female 32 baseball
3 Tom both 25 sleep
4 Gary male 50 coding

classes

id name grades user_id department_id
1 日本經典成人文學 2 1 1
2 程式與海 3 1 2
3 一個開發者的誕生 3 2 2
4 維尼的包子 3 null 1

departments

id name years
1 文學系 26
2 資工系 10

join

講了那麼多奇怪的幹話,終於開始提正題,
假設我們現在有個需求是說,
『 撈取所有修課學生是 male 的 class 』,
我們的篩選 where 條件是 users,
最後要撈出來的表的是 class 和 users ,
那我就可以來使用 join !!

join 的語法統一如同下方:

Select * From classed Inner join users on  users.id  = classed.user_id Where users.gender = male

開始來玩拆解結構小遊戲囉。

第一個 classed : 指的是主要的 table
Inner join : join 的模式
第一個 users : 被抓來做篩選的 table
on + users.id = classed.user_id : 將兩個 table 合在一起的連接點,也就是 users 的 id 等於 classed 的 user_id 的資料合併為一筆
users.gender : 因為我們合併了兩個 table ,所有做欄位篩選時,要指定是哪個 table 的哪個欄位

而常用的 join 模式有三種,『 Inner 』、『 Left 』、『 Right 』
可以看看這張附圖:
https://ithelp.ithome.com.tw/upload/images/20181101/20107758Xy54B2jO4c.png

剩下的,我們就一一解說看看有什麼差別吧!

Inner

也就是我們剛見到的 Inner join 本人大大:

Select * From classed Inner join users on  users.id  = classed.user_id Where users.gender = male

會輸出下面這個結果

id name grades user_id department_id id name gender age hobby
1 日本經典成人文學 2 1 1 1 John male 23 basketball
2 程式與海 3 1 2 1 John male 23 basketball
3 一個開發者的誕生 3 2 2 2 May female 32 baseball

不知道讀者有沒有注意到,他只有抓取『有配對到結果的資料』
因此 classes 裡面的第四筆資料,就沒有顯示上去,
很適合去只取有『交集』的資料,
也就是上圖內,兩圓中間的部分,
像是我們的 SQL 程式碼目的是找有配到『有學生修的課』即可

Left

要把 Inner join 改成 Left join 模式非常簡單,
只要這樣做:

Select * From classed Left join users on  users.id  = classed.user_id Where users.gender = male

難得不是程式碼,而是結果,
我們來看看結果為:

id name grades user_id department_id id name gender age hobby
1 日本經典成人文學 2 1 1 1 John male 23 basketball
2 程式與海 3 1 2 1 John male 23 basketball
3 一個開發者的誕生 3 2 2 2 May female 32 baseball
4 維尼的包子 3 null 1 null null null null null

你會看到,結果會先取得所有的 classes,
那如果沒有相對應的 users ,就直接全部塞 null ,
他的邏輯就是抓取,『所有的課加上修課學生的資料』。

Right

最後是 Right join 模式,
想必這裡大家就更知道要怎麼做了吧:

Select * From classed Right join users on  users.id  = classed.user_id Where users.gender = male

結果為:

id name grades user_id department_id id name gender age hobby
1 日本經典成人文學 2 1 1 1 John male 23 basketball
2 程式與海 3 1 2 1 John male 23 basketball
3 一個開發者的誕生 3 2 2 2 May female 32 baseball
null null null null null 3 Tom both 25 sleep
null null null null null 4 Gary male 50 coding

因為是取右邊圓的部分,
因此沒有配對到 users 的 classes 資料,就全部加上 null,
也就是取得『有學生修的課的資料,但學生沒修的照撈出學生』,
這樣能不能理解這三個模式的差異了呢?

如果有任何問題,或是指證文中的錯誤,歡迎寄信給我或留言在下面喔~


上一篇
[Day17] 資料庫語言( SQL )進階語法
下一篇
[Day19] MVC - 主流系統架構
系列文
菜鳥後端工程師的第一門課30

1 則留言

0
Nissen
iT邦新手 5 級 ‧ 2019-10-03 13:26:39

想請問如果是多個hobby要怎麼處理呢?

請問是指如果有多個 hobby 作為搜尋條件時嗎?

我要留言

立即登入留言