多表操作之列關聯（二）

2020 年 1 月 2 日
筆記

本文標識 : MQ0014

本文編輯 : 長安月下賞美人兒

編程工具 : MySQL、DBeaver

閱讀時長 : 8分鐘

前言

上周和小夥伴兒們一起分享了《多表操作之列關聯（一）》，主要涉及到交叉連接和內連接，此篇則詳細介紹外連接的相關資訊。也許會有困惑，為啥單獨介紹外連接，是因為其骨骼驚奇么？！小編在此鄭重聲明，之所以單獨介紹外連接，主要是因為其特別有趣，特別精彩，尤其是裡面的左連接更是在日常工作中被頻繁翻牌子！

外連接分類

外連接根據連接方式的不同，分為兩種類。其中，左連接和右連接，原理一致，但也分兩種不同的情況，全連接亦然如此。具體類別如下圖所示。

左連接、右連接
全連接

圖示所示：

左連接

左連接與右連接原理一致，故搞懂左連接兩種不同場景，也將順利解鎖右連接的奧秘。

LEFT JOIN：以左表為主表，通過相同意義的欄位與右表連接，返回左表的全部數據行，以及右表中匹配的記錄，如果右表中沒有匹配，則用欄位 NULL 來填充。聽起來很簡單有沒有？！但是，事情的經過並不是這個樣子的，實踐是檢驗真理的唯一標準，實際操作過程中的坑坑窪窪在所難免。

場景一：LEFT JOIN 包含兩表交集

# LEFT JOIN 場景一基礎語法：  SELECT <select_list>  FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key；

在左連接場景一中，根據兩表連接關鍵字的不同，又細分為3種情況。

1、一對一

通俗的講，A 表與 B 表連接的關鍵字內的資訊值，兩者一一對應。即 A.Key 中的資訊，在 B.Key 中有且僅有唯一對應值。譬如，前者裡面有 1 ，後者裡面有且僅有一個 1 。

數據背景：數據表 t1 ，數據表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

數據結果：

2、一對多

通俗的講，A 表與 B 表連接的關鍵字內的資訊值，前者關鍵字內資訊值各自獨立，後者關鍵字內資訊值出現多個重複值。即 A.Key 中的資訊，在 B.Key 中有多個對應值。譬如，前者裡面有 1 ，後者裡面有 2 個 1 。

數據背景：數據表 t1 ，數據表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

數據結果：

邏輯解析：

（1）數據表 t1 中，有 4 條數據，且 id 各自獨立；

（2）數據表 t2 中，有 4 條數據，但 id=2 的數據有 2 條；

（3）當 id=2 的時候，在表 t2 中有 2 條滿足和表 t1 中 id=2 進行匹配；

（4）故，返回的結果是 5 條，其中 2 條數據 id=2。

3、多對多

通俗的講，A 表與 B 表連接的關鍵字內的資訊值，前者與後者關鍵字內資訊值出現多個重複值。即 A.Key 中有重複資訊，在 B.Key 中有多個對應值。譬如，前者裡面有 2 個 1 ，後者裡面有 2 個 1 。

數據背景：數據表 t1 ，數據表 t2

SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id;

數據結果：

邏輯解析：

（1）數據表 t1 中，有 6 條數據，且 id=1、id=2 各有 2 條數據；

（2）數據表 t2 中，有 7 條數據，且 id=1、id=2 各有 2 條數據；

（3）當 id=1 ，name=『大毛』的時候，在表 t2 中有 2 條滿足和表 t1 中 id=2 進行匹配，以此類推；

（4）故，返回的結果是 10 條，其中 id=1、id=2 各有 4 條數據。

場景二：LEFT JOIN 不包含兩表交集

# LEFT JOIN 場景二基礎語法：  SELECT <select_list>  FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key  WHERE  B.Key IS NULL；

數據背景：數據表 t1 ，數據表 t2

#例1  SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.id=B.id  WHERE B.id IS NULL;

數據結果：

#例2  SELECT * FROM t1 A  LEFT JOIN t2 B  ON A.name=B.name  WHERE B.name IS NULL;

數據結果：

左連接小結：

在進行多表連接之前，查看清楚連接的關鍵字是否唯一，如果不唯一，則要考慮清楚選擇哪個欄位作為關鍵字
在留存交集的情況下，考慮清楚三種不同的連接情況，確定想要的數據模式
如果想對右表進行限制，則一定要在 on 條件中進行
如果想對連接之後的數據進行過濾，則一定要在 where 條件中進行
如果沒有 where 條件，無論 on 條件對兩表怎麼限制，左表的每一行至少會有一行的合成結果

全連接

全連接，就是將要連接的各表的所有資訊進行融合。簡單粗暴的講，就是取並集，用數學符號表示就是集合A ∪ 集合 B

場景一：FULL OUTER JOIN 包含兩表交集

# FULL  OUTER JOIN 場景一基礎語法：  SELECT <select_list> FROM TableA A  LEFT JOIN TableB B  ON A.Key = B.Key  WHERE <限制條件>  UNION  SELECT <select_list> FROM TableA A  RIGHT JOIN TableB B  ON A.Key = B.Key  WHERE <限制條件>；

數據背景：數據表 t1 ，數據表 t2

SELECT * FROM t1  LEFT JOIN t2  ON t1.name=t2.name  UNION  SELECT * FROM t1  RIGHT JOIN t2  ON t1.name=t2.name;

數據結果：

注意：如果直接關聯兩表會如何？

（還記得之前學習的行關聯么？！）

SELECT*FROM t1  UNION  SELECT * FROM t2;

數據結果：

場景二：FULL OUTER JOIN 不包含兩表交集

# FULL OUTER JOIN 場景二基礎語法：  SELECT <select_list> FROM TableA A  LEFT JOIN TableB B  ON B.Key IS NULL  UNION  SELECT <select_list> FROM TableA A  RIGHT JOIN TableB B  ON A.Key IS NULL；

數據背景：數據表 t1 ，數據表 t2

SELECT * FROM t1  LEFT JOIN t2  ON t1.name=t2.name  WHERE t2.name IS NULL  UNION  SELECT * FROM t1  RIGHT JOIN t2  ON t1.name=t2.name  WHERE t1.name IS NULL;

數據結果：

邏輯解析：

（1）數據表 t1 與 t2 中，以 name 為連接關鍵詞，重複的數據有『二毛』、『小明』；

（2）兩數據表左連接，且不包含交集

（3）兩數據表右連接，且不包含交集

（4）對查詢結果關聯即可得到結果

全連接小結：

進行數據表全連接的同時，要注意是否包含交集

注意：關於全連接，有的資料顯示，可以直接用 FULL OUTER JOIN 句式，但小編在 DBeaver 中嘗試失敗，感興趣的小夥伴兒可以去嘗試一下。

# FULL OUTER JOIN 場景一基礎語法：  SELECT <select_list> FROM TableA A  FULL OUTER JOIN TableB B  ON A.Key=B.Key;    # FULL OUTER JOIN 場景二基礎語法：  SELECT <select_list> FROM TableA A  FULL OUTER JOIN TableB B  WHERE A.Key IS NULL  OR B.Key IS NULL

Tags: