MySQL 嵌套子查詢完整指南：從標量子查詢到 EXISTS 相關子查詢

MySQL 4.1+ 完整支援 ANSI SQL 子查詢。本文系統講解 5 大子查詢類型：派生表、行子查詢、EXISTS/NOT EXISTS、相關子查詢、標量子查詢，附語法規則、性能特徵與改寫建議。

環境準備

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0), (2,'2',2.0);

1. 派生表

SELECT sb1, sb2, sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;
-- 結果：2, '2', 4.0

MySQL 5.7+ 支援派生表合併優化，8.0+ 可物化。別名 AS sb 必須指定。

2. 聚合嵌套：繞過「聚合函數不能嵌套」限制

-- 錯誤：SELECT AVG(SUM(col)) FROM t1 GROUP BY col;
-- 正確：
SELECT AVG(sum_col) FROM (SELECT SUM(col) AS sum_col FROM t1 GROUP BY col) AS t;

3. 行子查詢

SELECT * FROM t1
WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

構造元組比較，適用於多列主鍵關聯檢查。

4. EXISTS / NOT EXISTS 相關子查詢

-- 存在匹配店鋪類型的城市
SELECT DISTINCT store_type FROM Stores s
WHERE EXISTS (SELECT 1 FROM Cities_Stores cs WHERE cs.store_type = s.store_type);

-- 不存在匹配的類型
SELECT DISTINCT store_type FROM Stores s
WHERE NOT EXISTS (SELECT 1 FROM Cities_Stores cs WHERE cs.store_type = s.store_type);

-- 雙重否定：所有城市都有該類型店鋪（關係除法）
SELECT DISTINCT store_type FROM Stores s1
WHERE NOT EXISTS (
  SELECT 1 FROM Cities c
  WHERE NOT EXISTS (
    SELECT 1 FROM Cities_Stores cs
    WHERE cs.city = c.city AND cs.store_type = s1.store_type
  )
);

EXISTS 只關心是否有行返回，SELECT 1 比 SELECT * 略快。

5. 標量子查詢與多層嵌套

SELECT column1 FROM t1 AS x
WHERE x.column1 = (
  SELECT column1 FROM t2 AS y
  WHERE y.column1 = (
    SELECT column1 FROM t3 WHERE x.column2 = t3.column1
  )
);

每層返回單值，否則報錯「Subquery returns more than 1 row」。

支援子查詢的 DML 語句

SELECT, INSERT, UPDATE, DELETE, SET, DO 均可在適當位置使用子查詢。

子查詢可用子句/操作符

• 子句：DISTINCT, GROUP BY, LIMIT, ORDER BY, UNION, UNION ALL
• 比較：=, <>, >, <, >=, <=
• 集合：IN, NOT IN, ANY, SOME, ALL

性能與改寫建議

1. IN/EXISTS 子查詢：MySQL 5.6+ 半連接優化，但複雜場景仍建議改寫為 JOIN。
2. NOT IN 陷阱：子查詢結果含 NULL 導致整體為空，改用 NOT EXISTS。
3. 標量子查詢在 SELECT 列表：每行執行一次，極慢。改用 LEFT JOIN 或應用層組裝。
4. 派生表物化：大結果集派生表會建臨時表，考慮 CTE (8.0+) 或臨時表手工優化。