MySQL 多表查詢 – CROSS JOIN 與 SELF JOIN

---

簡介

在日常開發中，我們常常需要同時從多個資料表取得資訊，這時 JOIN 就是不可或缺的工具。除了最常見的 INNER JOIN、LEFT/RIGHT JOIN，CROSS JOIN 與 SELF JOIN 也是相當實用的技巧，只是使用情境較為特殊，容易讓初學者感到困惑。

• CROSS JOIN 會產生兩個資料表的「笛卡兒積」，適合用來產生組合、列舉所有可能的配對，或是輔助產生測試資料。
• SELF JOIN 則是把同一張表「自我」連接，常用來比對同層級或不同層級的資料（如組織階層、父子關係），是解決階層式結構查詢的關鍵。

本篇將從概念說明、實作範例、常見陷阱與最佳實踐，逐步帶你掌握這兩種 JOIN 的使用方式，並提供實務上的應用情境，讓你在面對複雜查詢時不再手足無措。

---

核心概念

1. CROSS JOIN – 笛卡兒積

CROSS JOIN 會把左表的每一筆資料與右表的每一筆資料配對，結果筆數等於 左表筆數 × 右表筆數。它不需要 ON 條件，因為配對是「全」配對。

SELECT *
FROM   colors   CROSS JOIN sizes;

color	size
Red	S
Red	M
Red	L
Blue	S
…	…

注意：若兩張表都很大，結果集會呈指數級增長，務必留意效能與記憶體使用。

1.1 常見用途

用途	為什麼需要 CROSS JOIN
產生測試資料	快速產生所有組合 (如顏色 × 尺寸) 供測試。
時間維度表	把日期表與時間表結合，得到完整的時間戳記。
組合分析	計算所有產品與促銷方案的可能收益。

---

2. SELF JOIN – 表格自我連接

SELF JOIN 本質上是把同一張表當作兩個不同的別名來連接，透過 ON 條件指定兩筆資料之間的關係。最常見的情境是 父子層級、前後關聯、或 同類別的比較。

SELECT  e1.emp_id   AS employee,
        e1.name     AS employee_name,
        e2.emp_id   AS manager,
        e2.name     AS manager_name
FROM    employees e1
JOIN    employees e2
  ON    e1.manager_id = e2.emp_id;

employee	employee_name	manager	manager_name
101	小王	100	小李
102	小張	100	小李
…	…	…	…

2.1 常見用途

用途	為什麼使用 SELF JOIN
組織階層	找出員工與其直屬主管、或往上追溯多層主管。
相同類別比較	比較同一產品在不同月份的銷售額。
找出重複資料	檢查同一個欄位是否出現相同值的多筆紀錄。

---

程式碼範例

以下示範 5 個實務中常見的 CROSS JOIN 與 SELF JOIN 用法，並附上說明註解。

範例 1：產生顏色與尺寸的所有組合（CROSS JOIN）

-- 建立測試用的顏色表與尺寸表
CREATE TABLE colors (color VARCHAR(20));
INSERT INTO colors VALUES ('Red'),('Blue'),('Green');

CREATE TABLE sizes (size CHAR(1));
INSERT INTO sizes VALUES ('S'),('M'),('L');

-- 產生笛卡兒積
SELECT c.color, s.size
FROM   colors AS c
CROSS JOIN sizes AS s
ORDER BY c.color, s.size;

這個結果可直接匯入商品庫存表，快速建立「顏色 × 尺寸」的商品 SKU。

範例 2：產生完整的日期時間戳記（CROSS JOIN + 日期函式）

-- 日期表（僅示範 2024-01-01 ~ 2024-01-03）
CREATE TABLE dates (d DATE);
INSERT INTO dates VALUES ('2024-01-01'),('2024-01-02'),('2024-01-03');

-- 時間表（每小時）
CREATE TABLE hours (h TIME);
INSERT INTO hours VALUES ('00:00:00'),('01:00:00'),('02:00:00');

-- 結合產生完整的 datetime
SELECT CONCAT(d.d, ' ', h.h) AS datetime_point
FROM   dates d
CROSS JOIN hours h
ORDER BY datetime_point;

常用於 時間序列分析，如產生缺失時間點的補齊資料。

範例 3：找出同一部門內薪資最高的員工（SELF JOIN）

-- 員工表
CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(30),
    dept_id    INT,
    salary     DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1,'小王',10,55000),
(2,'小張',10,62000),
(3,'小林',20,47000),
(4,'小陳',20,53000);

-- 自我連接找出每個部門薪資最高者
SELECT e1.dept_id,
       e1.emp_id   AS top_emp,
       e1.name     AS top_name,
       e1.salary   AS top_salary
FROM   employees e1
LEFT JOIN employees e2
  ON   e1.dept_id = e2.dept_id
 AND   e1.salary < e2.salary
WHERE  e2.emp_id IS NULL;   -- 沒有比自己薪資更高的同部門同事

LEFT JOIN 結合 WHERE e2.emp_id IS NULL 的技巧，常被稱為「找出最大/最小」的 反向 SELF JOIN。

範例 4：比對同一商品不同月份的銷售額（SELF JOIN）

-- 銷售表
CREATE TABLE sales (
    product_id INT,
    month      CHAR(7),   -- YYYY-MM
    amount     INT
);

INSERT INTO sales VALUES
(101,'2024-01',120),
(101,'2024-02',150),
(102,'2024-01',80),
(102,'2024-02',95);

-- 自我連接找出相鄰月份的增減變化
SELECT s1.product_id,
       s1.month   AS month_a,
       s1.amount  AS amount_a,
       s2.month   AS month_b,
       s2.amount  AS amount_b,
       (s2.amount - s1.amount) AS diff
FROM   sales s1
JOIN   sales s2
  ON   s1.product_id = s2.product_id
 AND   DATE_ADD(s1.month, INTERVAL 1 MONTH) = s2.month;

透過 DATE_ADD 把月份往前推一個月，直接找出 相鄰月份 的比較結果。

範例 5：找出重複的客戶 Email（SELF JOIN）

-- 客戶表
CREATE TABLE customers (
    cust_id INT PRIMARY KEY,
    name    VARCHAR(30),
    email   VARCHAR(50)
);

INSERT INTO customers VALUES
(1,'阿明','amy@example.com'),
(2,'小華','bob@example.com'),
(3,'小美','amy@example.com');

-- 自我連接找出重複的 email
SELECT c1.cust_id AS id1,
       c2.cust_id AS id2,
       c1.email
FROM   customers c1
JOIN   customers c2
  ON   c1.email = c2.email
 AND   c1.cust_id < c2.cust_id;   -- 防止同筆資料配對自己

這種寫法可快速定位 資料重複 的問題，常用於清理作業。

---

常見陷阱與最佳實踐

陷阱	說明	解決/最佳實踐
結果集過大	CROSS JOIN 會產生指數級資料量，若不加限制會導致查詢卡死或 OOM。	在測試或正式環境前 加上 WHERE 條件 或先 限制左/右表筆數（如 LIMIT、WHERE 篩選）。
忘記使用別名	SELF JOIN 中同一張表出現多次，若未使用別名會產生「欄位名稱衝突」錯誤。	必須為每個實例取別名（如 e1、e2），並在 SELECT 時明確指明欄位來源。
錯誤的連接條件	SELF JOIN 常因條件寫錯（例如 = 而非 <）導致無限循環或錯誤結果。	仔細檢查 ON 條件，必要時先寫出 子查詢 或 CTE 進行驗證。
忽視索引	連接欄位若未建立索引，查詢效能會大幅下降。	為 JOIN 欄位（如 manager_id、dept_id）建立 B‑Tree 索引，或使用 覆寫索引。
使用 CROSS JOIN 取代 INNER JOIN	有時開發者誤以為 CROSS JOIN 可以代替 INNER JOIN，結果會得到不必要的組合。	根據需求選擇正確的 JOIN 類型；若只想過濾匹配資料，使用 INNER JOIN。

---

實際應用場景

1. 商品 SKU 產生

   • 電商平台需要把「顏色」與「尺寸」的所有組合寫入商品表。利用 CROSS JOIN 產生完整組合後，再一次性插入，省去手動逐筆輸入的成本。

2. 組織圖與權限繼承

   • 企業內部系統常以 employees 表記錄員工與其直屬主管。透過 SELF JOIN 可以快速查出 直屬主管、上層主管（多層自我連接或遞迴 CTE），進而決定資料存取權限。

3. 時間序列資料補齊

   • 在 IoT 或金融分析中，若某段時間沒有資料點，需要補上「零」或「null」以保持連續性。先用 CROSS JOIN 產生完整的日期‑時間表，再左連接實際資料，即可完成補齊。

4. 相鄰期間比較

   • 銷售、流量或 KPI 報表常需要「本月 vs 前月」的差異。利用 SELF JOIN 把同一商品的不同月份配對，直接算出差額，避免在程式端額外迴圈計算。

5. 資料清理與重複偵測

   • 客戶資料庫中經常會出現重複的 Email、電話等欄位。透過 SELF JOIN 找出 email 相同且 cust_id 不同的紀錄，搭配 DELETE 或 UPDATE 完成去重作業。

---

總結

• CROSS JOIN 產生笛卡兒積，適合用於「全組合」的情境，如測試資料、時間維度或所有可能的配對。使用時務必留意結果筆數，避免因資料量過大而造成效能瓶頸。
• SELF JOIN 把同一張表當作兩個實體連接，能解決 階層結構、相鄰比較、重複偵測 等問題。關鍵在於給予別名、正確撰寫 ON 條件，以及為連接欄位加上適當的索引。

掌握這兩種特殊的 JOIN，能讓你在面對多表查詢時更靈活、效能更佳，也為資料分析、報表產出與系統設計打下堅實基礎。祝你在 MySQL 的世界裡，寫出更高效、更易維護的查詢語句！