SQL 達人的工作現場攻略筆記 - CH-3 自我連結使用方式

前言

SQL 提供連結會依照特徵命名，分別為內部連結、外部連結、交叉連結這些，且這些連結通常會以不同的資料表為對象，但 SQL 並未禁止同一張表用連結，此時在同一張資料表套用連結稱為自我連結 self join。

一、重複排序、排列、結合

假設有張表商品與價錢資料表、其中共有蘋果、橘子、香蕉三筆記錄不得不製作業績表時候，必須取得商品組合。

name (產品名稱)	price (價錢)
蘋果	100
橘子	50
香蕉	80

說是組合其實組合的種類有兩種，一種是重視的排列順序有序數對 (ordered pair)，另一種是不重視順序的無序數對(unordered pair)。要建立有序數對非常簡單，例如:

-- 建立重複排序SQL
SELECT P1.name AS name_1, P2.name AS name_2　
FROM Products P1 CROSS JOIN Products P2;

結果:

name_1	name_2
蘋果	蘋果
蘋果	橘子
蘋果	香蕉
橘子	蘋果
橘子	橘子
橘子	香蕉
香蕉	蘋果
香蕉	橘子
香蕉	香蕉

交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在結合時，不指定任何條件，即將兩個資料表中所有的可能排列組合出來。可以看到這種連結方式出現很多多餘的組合，這都是因為重視順序的集合，此外也可以將交叉連結寫成以下方式‧

-- 交叉連結另一種寫法
SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2;

此時作者卻說這種語法不要使用，原因是因為希望執行設有連結條件的內部連結，卻有可能忘記撰寫連結條件而變成交叉連結，產生預料之外的結果，交叉連結會消耗少不資源。接著將程式碼改寫成排除多餘的集合內容。首先排除(蘋果，蘋果)這種元素組合。

-- 取得排列 SQL
SELECT P1.name AS name_1, P2.name AS name_2　
FROM Products P1 INNER JOIN Products P2 ON P1.name <> P2.name;

結果:

name_1	name_2
蘋果	橘子
蘋果	香蕉
橘子	蘋果
橘子	香蕉
香蕉	蘋果
香蕉	橘子

ಠ_ಠ CROSS JOIN MYSQL 是舊版可能不支援此寫法，就會有使用 FROM table1, table2 方式。

早期語法可能使用下列，但只要錯誤一步就有可能會寫成剛剛的交叉連結，所以盡可能不要使用此寫法。原因是因為忘記加上 WHERE 條件就會執行交叉連結，但若是使用 INNER JOIN 的語法就算忘記大部的 DBMS 都會視為語法錯誤。

-- 取得排列 SQL
SELECT P1.name AS name_1, P2.name AS name_2　
FROM Products P1, Products P2
WHERE P1.name <> P2.name;

這裡說明如果上面忘記轉寫條件又會變成交叉連結，但使用 INNER JOIN 方式忘記寫條件系統就會噴錯，算是一種安全的寫法。接下來作者叫我們嘗試排除(蘋果、橘子)與(橘子、蘋果)這種只是調換順序組合。

-- 取得組合 SQL
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
INNER JOIN Products P2 ON P1.name > P2.name

結果:

name_1	name_2
蘋果	橘子
香蕉	橘子
香蕉	蘋果

若想取得三種以上得組合，只需將程式碼改成以下內容。

-- 取得組合 SQL 擴充三欄位版本
SELECT P1.name AS name_1,
       P2.name AS name_2,　
       P3.name AS name_3,
FROM Products P1
INNER JOIN Products P2 ON P1.name > P2.name
INNER JOIN Products P3 ON P2.name > P3.name;

結果:

name_1	name_2	name_3
蘋果	橘子	香蕉

除了例題使用的 = 之外，使用 >、<、<> 這種比較運算子連結稱為非等值連結。這種連結與自我連結一起使用也稱為自我非等值連結。這種一般來說不太會在商業邏輯使用，但會建立在欄位組合使用。

二、刪除重複列

在關聯式資料世界裡，重複列與 NULL 差不多是令人討厭，所以也有許多排列重複列的方法因此問世。用剛剛產品資料表例子，試著排除重複出現橘子，而這張表沒有設定Key。

-- 刪除重複列SQL
DELETE FROM Products P1
WHERE rowid < (SELECT MAX(P2.rowid)
FROM Products P2 WHERE P1.name = P2.name AND P1.price = P2.price);

這裡使用方式是虛擬欄位去取得列的編號因為這張表沒有設定 Key 關係，而在 MYSQL 中是名稱是使用_rowid，
在 SQL 裡擁有不同名稱的集合被當成不同的集合，即使物理層級相同的存在，在邏輯層級仍屬於不同的存在。

三、部分不一致的 Key 搜尋

name (姓名)	family_id (家庭 ID)	address (地址)
許郁文	100	台北市萬華區長泰街 3-2-29
許美美	100	台北市萬華區長泰街 3-2-92
張瑋礽	200	台北市中正區南海路 2-8-1
張銘仁	200	台北市中正區南海路 2-8-1
Holmes	300	貝克街 221B
Watson	400	貝克街 221B

接下來將下列聯絡人資料表為例。主 Key 是人名，同一家族的人使用相同家族 ID，基本上同一家人會有相同的地址，但也有像是 Holmes 跟 Watson 這種還不是家人卻同居的情侶，書中是說將目光放在許家夫妻上，這兩個人並不是分居而是地址有誤，以這種情況如何篩選出是同一家人但地址卻不一致的紀錄呢?

-- 搜尋同一家人，地址不同的 SQL
SELECT DISTINCT A1.name,A1.address
FROM Address A1 INNER JOIN Address A2 ON A1.family_id = A2.family_id
AND A1.address <> A2.address;

方法有很多種但如果是自我非等值連結撰寫，就能將程式碼寫得很簡潔。

從下面商品資料表找出相同價格商品

name (商品名稱)	price (價格)
蘋果	50
橘子	100
葡萄	50
西瓜	80
檸檬	30
草莓	100
香蕉	100

-- 搜尋價格相同，商品名稱卻不同 SQL
SELECT DISTINCT A1.name,A1.price
FROM Product A1 INNER JOIN Product A2 ON A1.price = A2.price
AND A1.name <> A2.name
ORDER BY P1.price;

結果:

name (商品名稱)	price (價格)
蘋果	50
葡萄	50
草莓	100
橘子	100
香蕉	100

四、總結

自我連結就是利用自身表去找出想要的資料。

1. 自我連結通常會與非等值連結搭配使用。

2. 與 GROUP BY 組合可建立遞迴性集合。

3. 想成是讓不同資料表連結。

4. 從邏輯層級思考，而不是物理層級。

參考資料:

1. SQL 達人的工作現場攻略筆記。