原英文連結
準備
建立表A并添加資料
create table table_a
(
PK int primary key,
VALUE varchar(50) null
);
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnLxQzMzUjNxITM4ETMxkTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
建立表B并添加資料
create table table_b
(
PK int primary key,
VALUE varchar(50) null
);
下面是各種連接配接的韋恩圖:
詳細分析
1.inner join (内連接配接)
内連接配接是一種一一映射關系,就是兩張表都有的才能顯示出來
用韋恩圖表示是兩個集合的交集,如圖:
實作代碼
select A.PK as A_PK,A.Value as A_Value,B.PK as B_PK,B.Value as B_Value
from table_a A
inner join table_b B
on A.PK = B.PK;
查詢結果
2.left join (左連接配接)
左連接配接是左邊表的所有資料都有顯示出來,右邊的表資料隻顯示共同有的那部分,沒有對應的部分隻能補空顯示,所謂的左邊表其實就是指放在left join的左邊的表
用韋恩圖表示如下:
實作代碼
select A.PK as A_PK,A.Value as A_Value,B.PK as B_PK,B.Value as B_Value
from table_a A
left join table_b B
on A.PK = B.PK;
查詢結果
3.right join (右連接配接)
右連接配接正好是和左連接配接相反的,這裡的右邊也是相對right join來說的,在這個右邊的表就是右表
用韋恩圖表示如下:
實作代碼
select A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
from table_a A
right join table_b B
on A.PK = B.PK;
查詢結果
4.outer join (外連接配接、全連接配接)
查詢出左表和右表所有資料,但是去除兩表的重複資料
韋恩圖表示如下:
實作代碼
select A.PK as A_PK,A.Value as A_Value,B.PK as B_PK,B.Value as B_Value
from table_a A
full join table_b B
on A.PK = B.PK;
查詢結果
5.left join excluding inner join(左連接配接不包含内連接配接)
這個查詢是隻查詢左邊表有的資料,共同有的也不查出來
韋恩圖表示如下:
實作代碼
select A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
from table_a A
left join table_b B
on A.PK = B.PK
where B.PK is NULL
查詢結果
6.right join excluding inner join(右連接配接不包含内連接配接)
這個查詢是隻查詢右邊表有的資料,共同有的也不查出來
韋恩圖表示如下:
實作代碼
select A.PK as A_PK,A.Value as A_Value,B.PK as B_PK,B.Value as B_Value
from table_a A
right join table_b B
on A.PK = B.PK;
where A.PK is NULL
查詢結果
7.outer join excluding inner join(外連接配接不包括内連接配接)
意思就是查詢左右表各自擁有的那部分資料
韋恩圖表示如下:
實作代碼
select A.PK as A_PK,A.Value as A_Value,B.PK as B_PK,B.Value as B_Value
from table_a A
full join table_b B
on A.PK = B.PK;
where A.PK is NULL
or B.PK is NULL
查詢結果