MySQL 子查詢入門完全指南：WHERE、FROM、SELECT 三大嵌套位置實戰

什麼是子查詢？

一條 SQL 語句中包含另一條 SELECT 語句，內層查詢結果供外層使用。按嵌套位置分為三類：

三大嵌套位置與示例

假設員工表 emp(empno, ename, job, sal, deptno)。

1. WHERE 子句：作為條件值

-- 查詢薪資最高的員工
SELECT * FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);

內層返回單值（標量子查詢），外層作等值比較。

2. FROM 子句：作為衍生表

-- 查詢 30 部門員工編號與姓名
SELECT e.empno, e.ename
FROM (SELECT * FROM emp WHERE deptno = 30) e;

內層結果集作為一張臨時表 e 參與外層查詢，**必須加別名**。

3. SELECT 列表：作為標量表達式（非標準，MySQL 支援）

  操作符語義示例
  
    > ALL (subq)大於子查詢**所有**值（等價 > MAX）WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
    > ANY (subq)大於子查詢**任一**值（等價 > MIN）WHERE sal > ANY (SELECT sal FROM emp WHERE job='經理')
    IN (subq)等值匹配任一值WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK')
    EXISTS (subq)子查詢返回至少一行WHERE EXISTS (SELECT 1 FROM emp e2 WHERE e2.mgr = e1.empno)
  


效能優化建議

  
優先用 JOIN 替代 IN/EXISTS，MySQL 對 JOIN 優化更成熟（Hash Join、BKA 等）。
  
衍生表（FROM 子查詢）在 5.6+ 支援物化，但大結果集仍建議落地臨時表或 CTE（8.0+）。
  
關聯子查詢（SELECT/WHERE 列表）慎用，易退化為 Nested Loop，改寫為 LEFT JOIN ... GROUP BY。
  
加上 LIMIT 1 或聚合函數 確保標量子查詢單行，避免「Subquery returns more than 1 row」錯誤。


MySQL 8.0 新特性：CTE (WITH 子句)
WITH dept_stats AS (
    SELECT deptno, COUNT(*) AS cnt, AVG(sal) AS avg_sal
    FROM emp GROUP BY deptno
)
SELECT e.ename, d.cnt, d.avg_sal
FROM emp e
JOIN dept_stats d ON e.deptno = d.deptno;
CTE 可遞歸、可多次引用，可讀性與效能雙贏，**強烈建議 8.0+ 專案全面替代 FROM 子查詢**。