數據庫左連接、右連接、內連接、全連接筆記

sunhao06 2022-01-08 00:17:47 阅读数:67

個人博客同步更新,歡迎瀏覽:鏈接
本文源網址:鏈接

1. 准備

建立兩張錶,錶A以及錶B,錶A以及錶B的字段以及數據如下所示:

錶A數據如下所示

image-20210415163946814

錶B數據如下所示

image-20210415164002140

下面是各種連接的韋恩圖

image-20210415163116517

2 INNER JOIN (內連接)

內連接是一種一一映射關系,就是兩張錶都有的才能顯示出來
用韋恩圖錶示是兩個集合的交集,如圖:

image-20210415164102160

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
INNER JOIN B B
ON A.PK = B.PK

查詢結果與原始錶的數據對比

image-20210415164541196

image-20210415164506379

3. LEFT JOIN (左連接)

左連接是左邊錶的所有數據都有顯示出來,右邊的錶數據只顯示共同有的那部分,沒有對應的部分只能補空顯示,所謂的左邊錶其實就是指放在left join的左邊的錶
用韋恩圖錶示如下:

image-20210415164707821

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
LEFT JOIN B B
ON A.PK = B.PK

查詢結果與原始錶的數據對比

image-20210415164832229

image-20210415164506379

4. RIGHT JOIN(右連接)

右連接正好是和左連接相反的,這裏的右邊也是相對right join來說的,在這個右邊的錶就是右錶
用韋恩圖錶示如下:

image-20210415164944818

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
RIGHT JOIN B B
ON A.PK = B.PK

查詢結果與原始錶的數據對比

image-20210415165123245

image-20210415164506379

5. OUTER JOIN(外連接、全連接)

查詢出左錶和右錶所有數據,但是去除兩錶的重複數據
韋恩圖錶示如下

image-20210415165231262

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
FULL OUTER JOIN B B
ON A.PK = B.PK

查詢結果與原始錶的數據對比

image-20210415165345129

image-20210415164506379
個人博客同步更新,歡迎瀏覽:鏈接
本文源網址:鏈接

6. LEFT JOIN EXCLUDING INNER JOIN(左連接不包含內連接)

這個查詢是只查詢左邊錶有的數據,共同有的也不查出來
韋恩圖錶示如下:

image-20210415165506139

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
LEFT JOIN B B
ON A.PK = B.PK
WHERE B.PK IS NULL

查詢結果與原始錶的數據對比

image-20210415165608981

image-20210415164506379

7. RIGHT JOIN EXCLUDING INNER JOIN(右連接不包含內連接)

這個查詢是只查詢右邊錶有的數據,共同有的也不查出來
韋恩圖錶示如下:

image-20210415165742844

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
RIGHT JOIN B B
ON A.PK = B.PK
WHERE A.PK IS NULL

查詢結果與原始錶的數據對比

image-20210415165913283

image-20210415164506379

8. OUTER JOIN EXCLUDING INNER JOIN(外連接不包含內連接)

意思就是查詢左右錶各自擁有的那部分數據
韋恩圖錶示如下:

image-20210415170009855

代碼實現

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM A A
FULL OUTER JOIN B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

*查詢結果與原始錶的數據對比*

image-20210415170120577

image-20210415164506379
個人博客同步更新,歡迎瀏覽:鏈接
本文源網址:鏈接

版权声明:本文为[sunhao06]所创,转载请带上原文链接,感谢。 https://gsmany.com/2022/01/202201080017470424.html