Love, and to be Loved.

我愛你,你是自由的。

SQL的JOIN語法解析(轉)

總的來說,四種JOIN的使用/區別可以描述為:

left join 會從左表(shop)那裡返回所有的記錄,即使在右表(sale_detail)中没有匹配的行。

right outer join 右連結,返回右表中的所有記錄,即使在左表中没有記錄與它匹配。

full outer join 全連結,返回左右表中的所有記錄。

在表中存在至少一個匹配時,inner join 返回行。 關鍵字inner可省略。

具體可以看stackoverflow上,Difference between Inner Join & Full join這個問題,說得蛮清楚的,我就搬運一下這個問題的答案好了。

Join

一共有三種OUTER JOIN:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

關鍵字OUTER是可選擇的,取決於具體語言,在實現上它們都是遵循標準的,因此FULL JOINFULL OUTER JOIN是一樣的。

接著将以簡化的數據集來說明這些JOIN語句。考慮有如下两個數據集,注意到有些元素在A中有,在B中没有,反過來也是。

1
2
3
4
5
6
7
Set "A"    Set "B"  
AA BB
-------- --------
Item 1 Item 3
Item 2 Item 4
Item 3 Item 5
Item 4 Item 6

LEFT OUTER JOIN

现在執行如下SQL語句(左連結,LEFT OUTER JOIN):

1
SELECT * FROM A LEFT OUTER JOIN B ON AA = BB

将會得到如下的結果(空白的元素表示NULL):

1
2
3
4
5
6
AA         BB
-------- --------

Item 1
Item 2
Item 3 Item 3
Item 4 Item 4

左連結(LEFT OUTER JOIN)會輸出左邊的表中的所有結果,如果右邊的表中有相應項,则會輸出,否則為NULL

因此,如果要找出在AA(左邊的表)中有,而在BB(右邊的表)中没有的數據項,可以使用如下的SQL語句:

1
2
SELECT * FROM A LEFT OUTER JOIN B ON AA = BB
WHERE BB is NULL

RIGHT OUTER JOIN

如果使用右連結,結果将會輸出BB中所有的數據項和AA中相應的匹配項(注意你現在是獲取了右邊的表中的所有數據項):

1
2
3
4
5
6
7
SELECT * FROM A RIGHT OUTER JOIN B ON AA = BB  
AA BB
-------- --------

Item 3 Item 3
Item 4 Item 4
Item 5
Item 6

FULL OUTER JOIN

如果想要取得所有的元素項,则可以使用FULL JOIN:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM A FULL JOIN B ON AA = BB  
AA BB
-------- --------
Item 1 <-----+
Item 2 |
Item 3 Item 3 |
Item 4 Item 4 |
Item 5 +--- empty holes are NULL's
Item 6 |
^ |
| |
+---------------------+

再次注意,缺失的數據項的值是NULL。

INNER JOIN

INNER JOINJOIN是一樣的,一般INNER關鍵字可以省略。INNER JOIN将只會返回相匹配的元素項,即不會返回結果為NULL的數據項。

1
2
3
4
5
6
SELECT * FROM A INNER JOIN B ON AA = BB 

AA BB
-------- --------

Item 3 Item 3
Item 4 Item 4

CROSS JOIN

最後還有一個CROSS JOIN,笛卡爾積,将會返回A中每個元素分别匹配B中所有元素的結果,即N*M組合。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT * FROM A CROSS JOIN B  

AA BB
-------- --------
Item 1 Item 3 ^
Item 1 Item 4 +--- A中第一個元素, 匹配B中所有元素
Item 1 Item 5 |
Item 1 Item 6 v
Item 2 Item 3 ^
Item 2 Item 4 +--- A中第二個元素, 匹配B中所有元素
Item 2 Item 5 |
Item 2 Item 6 v
Item 3 Item 3 ... and so on
Item 3 Item 4
Item 3 Item 5
Item 3 Item 6
Item 4 Item 3
Item 4 Item 4
Item 4 Item 5
Item 4 Item 6

圖解SQL的JOIN操作

這邊也有一張圖清楚的說明了每個JOIN操作。建議把上面的内容瀏覽一遍後,再好好看下這張圖片,相信對JOIN的操作應該就完全明白了。

图解SQL的JOIN操作圖解SQL的JOIN操作

參考資料

Origin