MySQL 多表查詢全攻略：從基本 JOIN 到複雜子查詢與集合運算

MySQL 多表查詢全攻略：從基本 JOIN 到複雜子查詢與集合運算

多表查詢是 SQL 核心技能。本文系統梳理 18 大類多表查詢模式：基本多表 SELECT、別名應用、UNION 集合運算、簡單/複雜子查詢、ANY/SOME/ALL 量詞、派生表、子查詢作表達式、關聯子查詢、多表 UNION、排序優化、條件 UNION、內連接、外連接、IN/NOT IN 範圍限定、關聯子查詢、HAVING 分組過濾，並附帶實戰避坑指南。

一、基本多表查詢與別名

-- 隱式內連接（舊語法，不推薦）
SELECT a.id, a.name, b.math 
FROM tb_demo a, tb_demo_tel b 
WHERE a.id = b.id;

-- 顯式內連接（標準語法）
SELECT a.id, a.name, b.math 
FROM tb_demo a 
INNER JOIN tb_demo_tel b ON a.id = b.id;

別名規則：定義別名後不可再使用原表名；多表有同名欄位時必須加前綴（表名/別名）。

二、集合運算：UNION vs UNION ALL

SELECT id, name FROM table1
UNION        -- 去重
SELECT id, name FROM table2;

SELECT id, name FROM table1
UNION ALL    -- 保留重複
SELECT id, name FROM table2;

前提：欄位數、順序、資料型別相容。

三、子查詢分層解析

1. 簡單子查詢：WHERE id IN (SELECT id FROM t WHERE ...)
2. 複雜子查詢：多層嵌套，表間必有關聯欄位（WHERE 建立關聯）。
3. 量詞謂詞：

> ANY   -- 大於子查詢任一值
> ALL   -- 大於子查詢所有值
= ANY   -- 等於任一值 (等價 IN)
!= ALL  -- 不等於所有值 (等價 NOT IN)

四、進階子查詢模式

• 派生表：SELECT * FROM (SELECT ... FROM t) AS alias，必須加別名。
• 子查詢作標量表達式：SELECT (SELECT AVG(score) FROM t) AS avg_score FROM t; 建議加別名便於應用層取值。
• 關聯子查詢：外層引用內層，內層引用外層欄位，逐行求值。

五、多表 UNION 進階

• 排序：只能在最後一個 SELECT 後加 ORDER BY。
• 條件 UNION：配合 GROUP BY ... HAVING 實現優先順序輸出（如指定出版社置頂）。

六、外連接：LEFT vs RIGHT JOIN

-- IN 範圍
SELECT * FROM t WHERE code IN (SELECT code FROM t WHERE code BETWEEN 'A' AND 'Z');

-- HAVING 分組過濾（聚合後過濾）
SELECT name, math FROM t GROUP BY id HAVING math > 95;

八、實戰避坑清單

1. ❌ 隱式連接（逗號分隔表） → ✅ 顯式 JOIN ... ON。
2. ❌ 子查詢返回多行用 = → ✅ 用 IN / ANY / ALL。
3. ❌ 派生表缺少別名 → ✅ 必須 AS alias。
4. ❌ UNION 欄位數/型別不一致 → ✅ 統一欄位列表，必要時 CAST。
5. ❌ 外連接條件寫在 WHERE → ✅ 條件放在 ON 子句，否則退化為內連接。

熟練掌握這 18 種模式，應對 99% 多表查詢場景綽綽有餘。