mySQL包含兩種聯接,分别是内連接配接(inner join)和外連接配接(out join),但我們又同時聽說過左連接配接,交叉連接配接等術語,本文旨在總結這些術語之間的關系。
1. 内連接配接
首先說明内連接配接的一個重要性質:内連接配接查詢結果與表的順序無關
(當然順序可能會發生變化,但是對應關系絕對不會錯亂!!!)
1.1 交叉連接配接(cross join)
當然,他還有其他的名字,比如:笛卡爾積,交叉積,還有最奇怪的名字“沒有連接配接”(no join)
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsICdzFWRoRXdvN1LclHdpZXYyd2LcBzNvwVZ2x2bzNXak9CX90TQNNkRrFlQKBTSvwFbslmZvwFMwQzLcVmepNHdu9mZvwFVywUNMZTY18CX052bm9CX90zdaVnVtpVMx0mYohmMZZXUYpVd1kmYr50MZV3YyI2cKJDT29GRjBjUIF2LcRHelR3LcJzLctmch1mclRXY39jM3MDMzETN0EDNxATM3EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
使用下列指令同時查詢玩具表的toy列和男孩表的boy列,得到的結果就是交叉連接配接
SELECT t.toy,b.boy
FROM toys AS t
CROSS JOIN
boys AS b;
其中,CROSS JOIN可以省略,簡寫為
SELECT t.toy,b.boy
FROM toys AS t, boys AS b;
交叉連接配接回把第一張表的每個值與第二張表的每個值進行比對,結果如下
交叉連接配接是内連接配接的一種,你又可以把内連接配接看作是通過查詢種的題哦呵件已除了某些結果資料行之後的交叉連接配接。
1.2 相等連接配接
我們假設每個男孩子都又一個玩具,表之間是一對一的關系,toy_id是外鍵,資料庫表如下圖
我們想找到每個男孩兒擁有什麼玩具,隻需要将boys表中的toy_id和toys中的主鍵進行比對,就會得到結果
SELECT boys.boy,toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id=toys.toy_id;
1.3 不等連接配接
我們繼續沿用1.2中的表結構,如果我們想找到每個男孩兒沒有的玩具,這時候我們可以使用不等連接配接(說白了就是=換成<>,其他沒有什麼差別)
SELECT boys.boy,toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id<>toys.toy_id
ORDER BY boys.boy;
1.4 自然連接配接
繼續沿用1.2的表結構。。。。。
注意:自然連接配接隻有在連接配接的列在兩張表中的名稱都相同時才會有用
其實,自然連接配接就是自動識别相同列的相等連接配接
SELECT boys.boy,toys.toy
FROM boys
NATURAL JOIN
toys
ORDER BY boys.boy;
得到的結果和1.2中的結果完全一樣(順序可能不同)
2. 外連接配接
首先說明外連接配接不同于内連接配接的一個性質:外連接配接查詢與表的順序有關
2.1 左外連接配接
LEFT OUTER JOIN(左外連接配接)接收左表的所有行,并用這些行與右表進行比對
當左表與右表具有一對多的關系時,左外連接配接特别有用。我們仍然使用之前的表結構
現在我們利用左外連接配接找出每個男孩擁有的玩具
SELECT b.boy,t.toy
FROM boys b
LEFT OUTER JOIN toys t
ON b.toy_id=t.toy_id;
LEFT OUTER JOIN左邊的表boys我們稱為左表,右邊的toys稱為右表,是以LEFT OUTER JOIN會取得左表boys的所有行和右表的toys的行進行比對,結果如下
我們的查詢結果和使用内連接配接時一樣,難道說内連接配接和外連接配接沒差別嗎?怎麼可能!!!接下來我們改變一下左表boys的表結構
我們向boys中新添加了一個Andy,把他的toy_id設定為6,注意,6在toys表中沒有對應的玩具,接下來再次運作上述程式
我們發現居然出現了一個NULL,NULL的出現是要告訴我們右表toys中沒有與左表boys中的Andy相比對的行,也就是說
外連接配接一定會提供資料行,無論還行能否在另一個表中找出相比對的行
接着做個實驗,我們調換左表和右表的順序
SELECT b.boy,t.toy
FROM toys t
LEFT OUTER JOIN boys b
ON b.toy_id=t.toy_id;
結論:出現NULL的列總是右表中的列
以下時左外連接配接的實際比對過程
toys中的玩具hula hoop與boys中的Davey的記錄比對,toys.toys_is=1,boys.toy_is=3
不比對
toys中的玩具hula hoop與boys中的Bobby的記錄比對,toys.toys_is=1,boys.toy_is=5
不比對
toys中的玩具hula hoop與boys中的Beaver的記錄比對,toys.toys_is=1,boys.toy_is=2
不比對
toys中的玩具hula hoop與boys中的Richie的記錄比對,toys.toys_is=1,boys.toy_is=1
成功比對
…………………省略toy_id=2,3的比對過程
toys中的玩具harmonica與boys中的Davey的記錄比對,toys.toys_is=4,boys.toy_is=3
不比對
toys中的玩具harmonica與boys中的Bobby的記錄比對,toys.toys_is=4,boys.toy_is=5
不比對
toys中的玩具harmonica與boys中的Beaver的記錄比對,toys.toys_is=4,boys.toy_is=2
不比對
toys中的玩具harmonica與boys中的Richie的記錄比對,toys.toys_is=4,boys.toy_is=1
不比對
右表查找完畢,沒有比對項,是以建立帶有NULL值的行來對應harmonica
……………….省略baseball cards的比對過程
2.2 右外連接配接
與左外連接配接完全相同,隻不過是用右表來評價左表
此外:RIGHT OUTER JOIN左側的表為右表!!!!!
這裡隻簡單舉一個例子,具體内容參考左外連接配接
SELECT b.boy,t.toy
FROM toys t
RIGHT OUTER JOIN boys b
ON b.toy_id=t.toy_id;
上述代碼等同于
SELECT b.boy,t.toy
FROM boys b
LEFT OUTER JOIN toys t
ON b.toy_id=t.toy_id;
這兩種寫法都是都把toys作為右表,把boys作為左表
實驗的表結構如下:
結果:
在實際運用過程中,我們一般傾向于隻使用一種,另一種簡單了解就好。