MySQL 子查詢全類型詳解：標量、列、行、表子查詢與 EXISTS 實戰模式

MySQL 子查詢全類型詳解：標量、列、行、表子查詢與 EXISTS 實戰模式

子查詢是構建複雜邏輯的核心工具。本文按出現位置（FROM/WHERE/EXISTS）與返回結果形態（標量/列/行/表）雙維度分類，配合「先寫外層目標，再填內層條件」的通用思維模型，逐一拆解 5 大類型實戰用法。

一、 通用思維模型：由外而內，層層遞進

1. 先寫最終想要的結果集（外層 SELECT ... FROM ...）。
2. 確定過濾/關聯條件需要從哪裡獲取。
3. 在對應位置嵌入子查詢，子查詢只負責「產出條件值」。
4. 驗證子查詢單獨運行結果是否符合預期（標量單值、列多行、行多列、表多行多列）。

二、 按返回結果形態分類

1. 標量子查詢 —— 返回單值（1 行 1 列）

適用位置：WHERE 比較運算符（=, >, <, >=, <=, <>）、SELECT 列表、ORDER BY 等。

案例：查詢 zhangsan 所在教室

-- 目標：教室名
SELECT room FROM class 
WHERE id = (SELECT c_id FROM stu WHERE s_name = 'zhangsan');

關鍵：子查詢必須只返回一行一列，否則報錯 Subquery returns more than 1 row。

2. 列子查詢 —— 返回一列多行

適用操作符：IN、NOT IN、ANY、ALL、EXISTS（轉換）。

案例：查詢 PHP 與 iOS 班級的所有學生

SELECT * FROM stu 
WHERE c_id IN (SELECT id FROM class WHERE c_name IN ('php', 'ios'));

3. 行子查詢 —— 返回一行多列

適用場景：多列聯合比較，使用元組構造器 (col1, col2) = (val1, val2)。

案例：查詢性別為 1 且年齡最大的學生

SELECT * FROM stu 
WHERE (age, sex) = (SELECT MAX(age), sex FROM stu WHERE sex = 1);

4. 表子查詢 —— 返回多行多列（衍生表）

位置：FROM 子句後，作為臨時數據源參與 Join/聚合。

案例：查詢每個班級年齡最大的學生

SELECT s.* 
FROM stu s
JOIN (
    SELECT c_id, MAX(age) AS max_age 
    FROM stu 
    GROUP BY c_id
) t ON s.c_id = t.c_id AND s.age = t.max_age;

MySQL 8.0+ 推薦：使用 CTE (WITH t AS (...) SELECT ...) 替代 FROM 子查詢，可讀性更強、可復用。

三、 EXISTS 子查詢 —— 語義化的存在性判斷

特點：不關心返回具體值，只關心是否存在至少一行。內層常用 SELECT 1 或 SELECT *，優化器可短路優化。

案例：查詢 PHP 班級中有學生的班級信息

SELECT * FROM class c
WHERE c.c_name = 'php'
  AND EXISTS (SELECT 1 FROM stu s WHERE s.c_id = c.id);

EXISTS vs IN 性能口訣

• 外大內小：外層表大、內層表小 → IN（或 JOIN）更優。
• 外小內大：外層表小、內層表大 → EXISTS 更優（外層驅動，內層索引查找）。
• 現代優化器對 IN 做了半連接優化，大多數場景性能接近，優先寫語義清晰的。

四、 子查詢優化建議

1. 能用 JOIN 替代的優先用 JOIN（MySQL 歷史版本子查詢優化較弱，8.0 大幅改善）。
2. 衍生表 必加別名 AS t，否則報錯 Every derived table must have its own alias。
3. 避免在 WHERE 使用 NOT IN + 含 NULL 的子查詢（結果永為空），改用 NOT EXISTS 或 LEFT JOIN ... IS NULL。
4. 標量子查詢放 SELECT 列表 會導致每行執行一次（N+1 問題），改用 JOIN 或應用層組裝。
5. 大表關聯子查詢確保關聯列有索引。

掌握 5 大類型的適用邊界與思維模型，子查詢將成為你處理複雜業務邏輯的得力工具，而非性能殺手。