數據庫關系運算之連接

在數據庫理論中，關系連接（Join） 是將兩個或多個關系（表）中的元組（行）根據一定條件組合成新關系的操作，是關系型數據庫中核心且高頻使用的操作。其本質是通過共享的屬性（列）建立表之間的關聯，從而獲取更完整的信息。

一、連接的核心要素

參與連接的表：至少兩個表（如表A和表B）。
連接條件：指定表之間的關聯規則，通常基于兩表中相同或可比較的列（如A.id = B.a_id）。
連接結果：新表，包含滿足條件的元組組合，列通常是兩表列的并集（可通過選擇指定需要的列）。

二、常見連接類型及示例

為便于理解，先定義兩個示例表：

表1：學生表（Student）

學號（S_id）	姓名（S_name）	班級（Class）
101	張三	一班
102	李四	二班
103	王五	一班
104	趙六	三班

表2：成績表（Score）

成績ID（Sc_id）	學號（S_id）	科目（Subject）	分數（Score）
1	101	數學	90
2	101	語文	85
3	102	數學	88
4	103	英語	92
5	105	數學	75

1. 內連接（Inner Join）

定義：只保留兩個表中同時滿足連接條件的元組。
邏輯：結果 = 表A中滿足條件的元組 + 表B中對應滿足條件的元組（交集）。
示例需求：查詢“有成績記錄的學生姓名及對應成績”（即學生表和成績表中都存在的學號）。

-- SQL語句
SELECT s.S_name, s.S_id, sc.Subject, sc.Score
FROM Student s
INNER JOIN Score sc ON s.S_id = sc.S_id;

結果：
| S_name | S_id | Subject | Score |
|--------|------|---------|-------|
| 張三 | 101 | 數學 | 90 |
| 張三 | 101 | 語文 | 85 |
| 李四 | 102 | 數學 | 88 |
| 王五 | 103 | 英語 | 92 |
說明：
- 趙六（104）在成績表中無記錄，故未出現；
- 成績表中105號學生不在學生表中，故未出現；
- 張三（101）有兩條成績記錄，因此結果中會對應兩條行（一行數學、一行語文）。

2. 左連接（Left Join / Left Outer Join）

定義：以左表（左側的表）為基準，保留左表所有元組，右表中滿足條件的元組與之匹配；若右表無匹配，右表列顯示為NULL。
邏輯：結果 = 左表所有元組 + 右表中對應滿足條件的元組（左表全集 + 交集）。
示例需求：查詢“所有學生的姓名及成績（無成績的學生顯示‘無成績’）”。

-- SQL語句
SELECT s.S_name, s.S_id, IFNULL(sc.Subject, '無科目') AS Subject,  -- 用IFNULL處理NULLIFNULL(sc.Score, '無成績') AS Score
FROM Student s
LEFT JOIN Score sc ON s.S_id = sc.S_id;

結果：
| S_name | S_id | Subject | Score |
|--------|------|----------|--------|
| 張三 | 101 | 數學 | 90 |
| 張三 | 101 | 語文 | 85 |
| 李四 | 102 | 數學 | 88 |
| 王五 | 103 | 英語 | 92 |
| 趙六 | 104 | 無科目 | 無成績 | （趙六在成績表中無記錄，右表列用NULL填充后替換）
說明：左表（Student）的所有4名學生均保留，趙六因無成績記錄，成績表相關列顯示為“無科目”和“無成績”。

3. 右連接（Right Join / Right Outer Join）

定義：以右表（右側的表）為基準，保留右表所有元組，左表中滿足條件的元組與之匹配；若左表無匹配，左表列顯示為NULL。
邏輯：結果 = 右表所有元組 + 左表中對應滿足條件的元組（右表全集 + 交集）。
示例需求：查詢“所有成績記錄對應的學生姓名（包括無對應學生的成績）”。

-- SQL語句
SELECT IFNULL(s.S_name, '未知學生') AS S_name, sc.S_id, sc.Subject, sc.Score
FROM Student s
RIGHT JOIN Score sc ON s.S_id = sc.S_id;

結果：
| S_name | S_id | Subject | Score |
|----------|------|---------|-------|
| 張三 | 101 | 數學 | 90 |
| 張三 | 101 | 語文 | 85 |
| 李四 | 102 | 數學 | 88 |
| 王五 | 103 | 英語 | 92 |
| 未知學生 | 105 | 數學 | 75 | （105號學生不在Student表中，左表列用NULL填充后替換）
說明：右表（Score）的所有5條成績記錄均保留，105號學生因不在學生表中，姓名顯示為“未知學生”。

4. 全連接（Full Join / Full Outer Join）

定義：保留兩個表中所有元組，滿足條件的元組正常匹配；不滿足條件的元組，對應另一表的列顯示為NULL。
邏輯：結果 = 左表全集 + 右表全集（并集）。
注意：MySQL不直接支持Full Join，可通過“Left Join + Union + Right Join”模擬。

示例結果（模擬）：

S_name	S_id	Subject	Score
張三	101	數學	90
張三	101	語文	85
李四	102	數學	88
王五	103	英語	92
趙六	104	無科目	無成績
未知學生	105	數學	75

5. 交叉連接（Cross Join）

定義：不指定連接條件時的連接，返回兩個表的笛卡爾積（左表每一行與右表每一行都組合）。
特點：結果行數 = 左表行數 × 右表行數（通常需配合條件過濾，否則結果冗余）。
示例：Student（4行）× Score（5行）= 20行結果（此處省略冗余內容）。

三、連接的使用場景總結

連接類型	核心用途	典型場景示例
內連接	獲取兩表關聯的“有效數據”	學生成績查詢（必須有學生和成績記錄）
左連接	以左表為基準，補充右表信息（允許右表為空）	員工及所屬部門查詢（含暫無部門的員工）
右連接	以右表為基準，補充左表信息（允許左表為空）	訂單及客戶信息查詢（含暫無客戶的訂單）
全連接	獲取兩表所有數據及關聯數據	合并兩個來源的用戶數據（含獨有和共有）
交叉連接	生成所有可能組合（需配合條件）	生成“學生-課程”所有可選組合

通過以上示例可以看出，連接的核心是通過“共享列”建立表之間的關聯，不同連接類型決定了結果中保留的數據范圍。實際使用時，需根據業務需求選擇合適的連接類型，并注意連接條件的準確性（避免笛卡爾積或遺漏數據）。

在數據庫關系連接中，不同的連接類型適用于不同的業務場景。以下通過**“電商訂單系統”**的典型場景，舉例說明常用連接類型的實際應用（假設存在兩張核心表：訂單表(orders)和用戶表(users)）。

基礎表結構與數據

為了更直觀理解，先定義兩張表的結構和示例數據：

1. 用戶表（users）

存儲用戶的基礎信息，主鍵為user_id。

user_id（主鍵）	username	age
101	張三	25
102	李四	30
103	王五	28
104	趙六	35

2. 訂單表（orders）

存儲用戶的訂單信息，user_id為外鍵（關聯users表的user_id），主鍵為order_id。

order_id（主鍵）	user_id（外鍵）	order_time	total_amount
1001	101	2025-07-01 10:00	299
1002	101	2025-07-05 14:30	599
1003	102	2025-07-03 09:15	199
1004	105	2025-07-06 16:40	899

典型場景與連接類型示例

場景1：查詢“有訂單的用戶及其訂單信息”（內連接 Inner Join）

業務需求：運營需要統計“已下單用戶”的訂單明細（不含未下單用戶，也不含無對應用戶的異常訂單）。

原理：內連接只保留兩張表中“連接條件匹配”的記錄（即users.user_id = orders.user_id且兩邊都存在的記錄）。

SQL語句：

SELECT u.user_id, u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o 
ON u.user_id = o.user_id;

查詢結果（只保留匹配的記錄）：

user_id	username	order_id	total_amount
101	張三	1001	299
101	張三	1002	599
102	李四	1003	199

說明：

王五（103）、趙六（104）因無訂單，未出現在結果中；
訂單1004（user_id=105）因無對應用戶，也未出現在結果中。

場景2：查詢“所有用戶的訂單情況（含未下單用戶）”（左連接 Left Join）

業務需求：客服需要整理“所有用戶的訂單記錄”，包括“未下單用戶”（需標記為“無訂單”）。

原理：左連接以“左表（users）”為基準，保留左表所有記錄；右表（orders）中匹配的記錄正常顯示，不匹配的記錄用NULL填充。

SQL語句：

SELECT u.user_id, u.username, o.order_id, IFNULL(o.total_amount, '無訂單') AS total_amount
FROM users u
LEFT JOIN orders o 
ON u.user_id = o.user_id;

查詢結果（保留所有用戶，無訂單的用戶訂單字段為NULL或“無訂單”）：

user_id	username	order_id	total_amount
101	張三	1001	299
101	張三	1002	599
102	李四	1003	199
103	王五	NULL	無訂單
104	趙六	NULL	無訂單

說明：

左表（users）的所有用戶（101-104）均被保留；
王五（103）、趙六（104）無訂單，訂單相關字段顯示為NULL（通過IFNULL轉換為“無訂單”）。

場景3：查詢“所有訂單及對應用戶（含異常訂單）”（右連接 Right Join）

業務需求：技術人員需要排查“異常訂單”（即訂單對應的用戶不存在的情況）。

原理：右連接以“右表（orders）”為基準，保留右表所有記錄；左表（users）中匹配的記錄正常顯示，不匹配的記錄用NULL填充。

SQL語句：

SELECT o.order_id, o.user_id, u.username, IF(u.user_id IS NULL, '異常訂單', '正常訂單') AS order_status
FROM users u
RIGHT JOIN orders o 
ON u.user_id = o.user_id;

查詢結果（保留所有訂單，無對應用戶的訂單標記為異常）：

order_id	user_id	username	order_status
1001	101	張三	正常訂單
1002	101	張三	正常訂單
1003	102	李四	正常訂單
1004	105	NULL	異常訂單

說明：

右表（orders）的所有訂單（1001-1004）均被保留；
訂單1004（user_id=105）無對應用戶，用戶相關字段（username）顯示為NULL，被標記為“異常訂單”。

場景4：查詢“所有用戶和所有訂單（含未匹配記錄）”（全連接 Full Join）

業務需求：數據分析師需要一次性獲取“所有用戶+所有訂單”的完整數據（含未下單用戶和異常訂單），用于全局統計。

原理：全連接保留左表和右表的所有記錄，雙方不匹配的部分用NULL填充（注：MySQL不直接支持FULL JOIN，可通過LEFT JOIN + UNION + RIGHT JOIN模擬）。

模擬SQL語句：

-- 左連接結果（所有用戶+匹配訂單）
SELECT u.user_id, u.username, o.order_id, o.total_amount
FROM users u LEFT JOIN orders o ON u.user_id = o.user_id
UNION  -- 合并結果并去重
-- 右連接中“訂單無對應用戶”的部分（避免重復）
SELECT u.user_id, u.username, o.order_id, o.total_amount
FROM users u RIGHT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id IS NULL;

查詢結果（所有用戶+所有訂單，不匹配部分為NULL）：

user_id	username	order_id	total_amount
101	張三	1001	299
101	張三	1002	599
102	李四	1003	199
103	王五	NULL	NULL
104	趙六	NULL	NULL
NULL	NULL	1004	899

總結：連接類型與場景對應關系

連接類型	核心作用	典型場景
內連接（Inner）	只保留匹配的記錄	正常業務數據查詢（如已下單用戶的訂單明細）
左連接（Left）	保留左表所有記錄，匹配右表數據	需包含“主表全部數據”的查詢（如所有用戶的訂單情況）
右連接（Right）	保留右表所有記錄，匹配左表數據	需包含“從表全部數據”的查詢（如所有訂單及用戶匹配情況）
全連接（Full）	保留左右表所有記錄	全局數據統計（如所有用戶+所有訂單的完整視圖）