MySQL 列子查詢與行子查詢深度解析：IN/ANY/ALL 操作符、行構造符與效能實戰

前言

列子查詢回傳「單列多行」，行子查詢回傳「單行多列」。兩者配合 IN、ANY、ALL、行構造符 (a,b) 能表達豐富的集合與元組比較邏輯。本文系統講解語法、執行語義、特殊情況 (NULL/空集合) 與優化策略。

一、列子查詢

1.1 定義與基本語法

SELECT * FROM article
WHERE uid IN (SELECT uid FROM user WHERE status = 1);

子查詢回傳 uid 列的多行值集合。

1.2 IN / NOT IN

• IN 等價於 = ANY：滿足任一值即真。
• NOT IN 等價於 <> ALL：不等於所有值才真。
• **陷阱**：子查詢結果含 NULL → NOT IN 整體判斷為 UNKNOWN，**回傳 0 行**。
  修正：WHERE uid NOT IN (SELECT uid FROM user WHERE status=1 AND uid IS NOT NULL) 或改用 NOT EXISTS。

1.3 ANY / SOME

-- 找出薪資大於任一部門平均薪資的員工
SELECT * FROM employee e
WHERE salary > ANY (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• > ANY：大於**最小值**即真。
• = ANY：等同 IN。
• <> ANY：不等於任一值 (注意非 NOT IN)。

1.4 ALL

-- 找出薪資大於所有部門平均薪資的員工
SELECT * FROM employee e
WHERE salary > ALL (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• > ALL：大於**最大值**才真。
• < ALL：小於**最小值**才真。
• = ALL：等於所有值 (集合單一值時才可能真)。
• <&> ALL：等同 NOT IN。

1.5 特殊情況真值表

二、行子查詢

2.1 定義與行構造符

回傳 **單行多列**，外層使用行構造符 (col1, col2, ...) 或 ROW(col1, col2, ...) 進行元組比較。

2.2 語法範例

-- 找出 article 中 title、content、uid 完全等同於 blog 某行的文章
SELECT * FROM article
WHERE (title, content, uid) = (
    SELECT title, content, uid FROM blog WHERE bid = 2
);

2.3 比較運算子支援

• =、<>、<、>、<=、>= 皆支援行比較 (字典序)。
• IN 也支援行元組：WHERE (a,b) IN (SELECT c,d FROM t)。

2.4 限制

• 子查詢**必須保證回傳單行** (加 LIMIT 1 或唯一索引查找)，否則報錯 Subquery returns more than 1 row。
• 行構造符列數、順序、型別必須與子查詢選擇清單完全一致。

三、效能與優化

3.1 列子查詢優化

3.2 行子查詢優化

• 行比較可使用**複合索引**的最左前綴：INDEX (title, content, uid) 可支援 WHERE (title, content, uid) = ...。
• 若無複合索引，優化器退化為全表掃描逐行比較元組。

3.3 實戰：改寫 ANY/ALL 為聚合比較

-- 原始：薪資大於所有部門平均
WHERE salary > ALL (SELECT AVG(salary) FROM employee GROUP BY dept_id)

-- 優化：預先計算最大部門平均
WHERE salary > (SELECT MAX(avg_sal) FROM (SELECT AVG(salary) AS avg_sal FROM employee GROUP BY dept_id) t);
-- 或使用 CTE
WITH dept_avg AS (SELECT AVG(salary) AS avg_sal FROM employee GROUP BY dept_id)
SELECT * FROM employee WHERE salary > (SELECT MAX(avg_sal) FROM dept_avg);

優勢：避免 ALL 逐行比較，轉為單一標量比較，索引友善。

四、常見錯誤與修正

結語： 列子查詢與行子查詢是 SQL 表達集合與元組邏輯的標準工具。理解 IN/ANY/ALL 的真值語義 (特別是 NULL 與空集合)、行構造符的索引匹配規則，並善用 EXISTS、聚合預計算、CTE 改寫，即能寫出語意清晰且高效的查詢。