SQL JOIN類型整理

之前工作常用MySQL資料庫，

不過因為大多只是撈資料，所以不外乎是用SELECT, INSERT, UPDATE, DELETE...等指令

JOIN相關的應用反而不熟，

與其想那麼多，不如就自己整理一份~

若沒有資料庫的朋友們，可以使用 SQL Fiddle 來進行下述範例的操作。

※註：以下範例皆以MySQL語法進行示範

首先，建立兩張Table並插入資料：

# 建立客戶資料表 - customers
CREATE TABLE `customers`(
`C_ID`INT AUTO_INCREMENT,
`NAME` VARCHAR(30) NOT NULL,
`CITY` VARCHAR(30) NOT NULL,
`ADDRESS`VARCHAR(100) NOT NULL,
`PHONE` VARCHAR(30) NOT NULL,
PRIMARY KEY (`C_ID`));

# 插入客戶資料至customers
INSERT INTO `customers` (`NAME`, `CITY`, `ADDRESS`, `PHONE`) VALUES 
('Jason', 'Tainan', 'XXX100巷1號', '0912345678'), 
('Tina', 'Kaohsiung', 'XXX200巷2號', '0923456789'), 
('Peter', 'Taichung', 'XXX300巷3號', '0934567890'), 
('Wendy', 'Taichung', 'XXX400巷4號', '0945678901'), 
('Debby', 'Hsinchu', 'XXX500巷5號', '0956789012');

# 建立訂單資料表 - orders 
# (資料表orders的欄位CUSTOMER_ID作為外鍵，並參照到資料表customers的主鍵欄位C_ID，
#  這代表訂單資料表的CUSTOMER_ID，其資料內容只允許客戶資料表內存在的C_ID。)
CREATE TABLE `orders`(
`O_ID`INT AUTO_INCREMENT,
`ORDER_NO` BIGINT NOT NULL,
`CUSTOMER_ID` INT NOT NULL,
PRIMARY KEY (`O_ID`), 
FOREIGN KEY (`CUSTOMER_ID`) REFERENCES `customers` (`C_ID`));

# 插入訂單資料至orders
INSERT INTO `orders` (`ORDER_NO`, `CUSTOMER_ID`) VALUES
(20210328173500, 4),
(20210328180310, 1),
(20210328180806, 2),
(20210328180923, 2),
(20210328210143, 1),
(20210328214407, 5);

Table 1: customers

C_ID	NAME	CITY	ADDRESS	PHONE
1	Jason	Tainan	XXX100巷1號	0912345678
2	Tina	Kaohsiung	XXX200巷2號	0923456789
3	Peter	Taichung	XXX300巷3號	0934567890
4	Wendy	Taichung	XXX400巷4號	0945678901
5	Debby	Hsinchu	XXX500巷5號	0956789012

Table 2: orders

O_ID	ORDER_NO	CUSTOMER_ID
1	20210328173500	4
2	20210328180310	1
3	20210328180806	2
4	20210328180923	2
5	20210328210143	1
6	20210328214407	5

■ INNER JOIN (內部連接)

INNER JOIN，簡單來講就是兩張資料表做「交集」(Intersection)，記作 A∩B。

假設我們想要知道資料表A客戶下訂的訂單資料，可以透過下述語法查詢：

# 使用"INNER JOIN"查詢訂單資料 - Ver.1
SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
INNER JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`;

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Wendy	20210328173500
Debby	20210328214407

由於部份客戶有2筆以上的訂單，可加入GROUP_CONCAT()函數、GROUP BY語法，

使得呈現出來的資料更容易閱讀，語法如下：

# 使用"INNER JOIN"查詢訂單資料 - Ver.2
SELECT `customers`.`NAME`, GROUP_CONCAT(`orders`.`ORDER_NO` SEPARATOR ', ') AS `ORDER_NO_GROUP`
FROM `customers`
INNER JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`
GROUP BY `customers`.`NAME`;

NAME	ORDER_NO_GROUP
Debby	20210328214407
Jason	20210328180310, 20210328210143
Tina	20210328180806, 20210328180923
Wendy	20210328173500

如此一來，客戶的訂單資料即可清楚呈現，

然而，Peter雖然是客戶名單之一(資料表customers)，

但訂單(資料表orders)並沒有Peter下訂的資料，

所以不滿足「交集」的條件，最後呈現的資料也就不會有Peter。

■ LEFT (OUTER) JOIN (左外部連接)

LEFT JOIN，我的理解是：

「呈現左資料表全部的資料，而右資料表有對應左資料表的則呈現，沒有對應的則以NULL表示。」

以集合表示可記作 A∪(A∩B)。

所以，假設我們想要知道全部資料表A客戶的訂單資料，可以透過下述語法查詢：

# 使用"LEFT JOIN"查詢訂單資料
SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
LEFT JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`;

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Peter	(null)
Wendy	20210328173500
Debby	20210328214407

很明顯地，因為我們要查「全部客戶的訂單資料」，

所以即使Peter沒有下訂，

仍會將Peter的名字(左資料表)呈現出來，而訂單編號則以NULL表示(右資料表)。

■ RIGHT (OUTER) JOIN (右外部連接)

RIGHT JOIN，我的理解是：

「呈現右資料表全部的資料，而左資料表有對應右資料表的則呈現，沒有對應的則以NULL表示。」

以集合表示可記作 (A∩B)∪B。

所以，假設我們想要知道全部的訂單資料，可以透過下述語法查詢：

# 使用"RIGHT JOIN"查詢訂單資料
SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
RIGHT JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`;

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Wendy	20210328173500
Debby	20210328214407

由於一開始建表時有建立FOREIGN KEY，

這代表資料表orders無法插入資料表customers不存在的CUSTOMER_ID，

所以此查詢結果會與INNER JOIN一樣。

※備註：

假設建表時無FOREIGN KEY之設定，

且資料表orders插入了一筆ORDER_NO = 20210328220005、CUSTOMER_ID = 6的訂單，

使用RIGHT JOIN查詢後你會發現：

CUSTOMER_ID = 6那筆訂單的NAME欄位資料會以NULL表示，如下圖所示：

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Wendy	20210328173500
Debby	20210328214407
(null)	20210328220005

■ FULL (OUTER) JOIN (全外部連接)

FULL JOIN，簡單來講就是兩張資料表做「聯集」(Union)，記作 A∪B。

假設我們想知道全部客戶與全部訂單的資料，可透過下述語法查詢：

# 使用"LEFT JOIN" & "RIGHT JOIN" & "UNION" 等語法，實作FULL JOIN語法查詢訂單資料
# Note: MySQL無"FULL JOIN"語法
(SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
LEFT JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`)
UNION
(SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
RIGHT JOIN `orders`
ON `customers`.`C_ID` = `orders`.`CUSTOMER_ID`);

Case 1 - 依照原資料表的查詢結果：(查詢結果同LEFT JOIN)

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Peter	(null)
Wendy	20210328173500
Debby	20210328214407

Case 2 - 假設建表時無FOREIGN KEY之設定，且資料表orders插入了一筆

ORDER_NO = 20210328220005、CUSTOMER_ID = 6的訂單，查詢結果：

NAME	ORDER_NO
Wendy	20210328173500
Jason	20210328180310
Tina	20210328180806
Tina	20210328180923
Jason	20210328210143
Debby	20210328214407
Peter	(null)
(null)	20210328220005

■ SELF JOIN (自連接)

SELF JOIN其實就是將同一張資料表，拆成兩張表來進行JOIN的動作，

例如：

我想要從資料表customers，列出欄位 - 客戶X、客戶Y、城市，

其中客戶X與客戶Y必須住在同一城市。

此時可以透過下述語法查詢：

# 以SELF JOIN方式，列出住在同一城市的客戶X與客戶Y(兩兩一組)
SELECT A.`NAME` AS CustomerNameX, B.`NAME` AS CustomerNameY, A.`CITY`
FROM `customers` AS A, `customers` AS B
WHERE A.`NAME` <> B.`NAME` AND A.`CITY` = B.`CITY`;

CustomerNameX	CustomerNameY	CITY
Wendy	Peter	Taichung
Peter	Wendy	Taichung

■ CROSS JOIN (交叉連接 or 笛卡爾連接)

CROSS JOIN即針對兩個欄位中的資料，進行Cartesian Product(笛卡兒積)。

可記作 A × B、數量共 | A × B | = | A | ．| B | 

例：若欄位A有5筆不同資料、欄位B有6筆不同資料，則查詢出來的組合將會有5 x 6 = 30種。

將資料表customers - 欄位NAME、資料表orders - 欄位ORDER_NO，作CROSS JOIN：

# CROSS JOIN查詢
SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
CROSS JOIN `orders`;

5位客戶與6筆訂單的笛卡爾積結果會有5x6=30筆資料， 由於資料量多佔篇幅故不貼上，

有興趣的人再到SQL Fiddle進行測試~

■ NATURAL JOIN (自然連接)

NATURAL JOIN會自動把兩張資料表相同名字的欄位合在一起，

合併的邏輯與INNER JOIN相同，

另外，還有NATURAL LEFT JOIN與NATURAL RIGHT JOIN等用法。

試著在一開始建立資料表時，

將資料表orders - 欄位CUSTOMER_ID  → 更改為C_ID，

接著進行NATURAL JOIN查詢：

# NATURAL JOIN查詢
SELECT `customers`.`NAME`, `orders`.`ORDER_NO`
FROM `customers`
NATURAL JOIN `orders`;

NAME	ORDER_NO
Jason	20210328180310
Jason	20210328210143
Tina	20210328180806
Tina	20210328180923
Wendy	20210328173500
Debby	20210328214407

你會發現，其實查詢出來的結果與INNER JOIN相同，

可以試試NATURAL LEFT JOIN與NATURAL RIGHT JOIN，

查詢結果也會與上面提到的LEFT JOIN與RIGHT JOIN相同。

● 參考資料：

• SQL Fiddle - 線上練習SQL語法的測試網站，支援MySQL、PostgreSQL、SQLite
• Fooish 程式技術 - JOIN 連接 (SQL JOIN)
• W3Schools - SQL Joins
• TWCODE01.COM - SQL 連接(JOIN)
• Stack Overflow - How to do a FULL OUTER JOIN in MySQL?
• Wikipedia - Cartesian Product