子查詢是 SQL 進階查詢核心。本文系統講解**子查詢分類、位置、運算子、常見錯誤、與 JOIN 轉換、實戰範例**，助你駕馭複雜業務邏輯。

子查詢基本原則

• 必須包在括號 (...) 內
• 置於比較運算子右側 (可讀性)
• 不能有 ORDER BY (除非配合 TOP/LIMIT)
• 單行子查詢用單行運算子 (=, <>, >, <, >=, <=)
• 多行子查詢用多行運算子 (IN, ANY, ALL, EXISTS)

分類與範例

單行子查詢 (回傳 1 行 1 欄)

-- 找出與員工 141 同職稱的人
SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

-- 多條件巢狀
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
  AND salary > (SELECT salary FROM employees WHERE employee_id = 143);

多行子查詢 (回傳多行)

-- IN: 部門最低薪資高於 50 號部門最低薪資的部門
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

-- 錯誤示範: 單行運算子用多行結果
SELECT employee_id, last_name
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
-- ORA-01427: single-row subquery returns more than one row

-- 修正: 改用 IN
SELECT employee_id, last_name
FROM employees
WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

相關子查詢 (Correlated Subquery)

內層參考外層欄位，**逐列執行** (如迴圈)。

-- 找出薪資高於所在部門平均薪資的員工
SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE a.salary > (
    SELECT AVG(salary) FROM employees b
    WHERE b.department_id = a.department_id
);

-- 等效 INLINE VIEW 寫法 (效能通常更好)
SELECT a.last_name, a.salary, a.department_id, b.avg_sal
FROM employees a
JOIN (
    SELECT department_id, AVG(salary) avg_sal
    FROM employees GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary > b.avg_sal;

EXISTS / NOT EXISTS (存在性檢查)

-- 找出非主管的員工 (employee_id 不在 manager_id 中)
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

-- 更安全: NOT EXISTS (不受 NULL 影響)
SELECT employee_id, last_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id
);

ANY / ALL (限定比較)

-- 薪資大於 任一 10/20 號部門員工薪資
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id IN (10,20));

-- 薪資大於 所有 10/20 號部門員工薪資
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id IN (10,20));

多欄位子查詢 (成對 vs 非成對)

-- 成對比較: (manager_id, department_id) 必須同時匹配同一組
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (
    SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)
) AND employee_id NOT IN (178,174);

-- 非成對: 獨立匹配
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141))
  AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141))
  AND employee_id NOT IN (174,141);

子查詢 vs JOIN 效能選擇

場景	推薦	理由
小結果集、單次查詢	子查詢	可讀性好
大表關聯、需索引	JOIN	優化器更易產生 Hash/Merge Join
EXISTS/NOT EXISTS	子查詢	半連接優化，停止掃描
IN 子查詢大結果集	JOIN	避免重複掃描

實戰：跨多表串聯查詢

-- 找銷售過產品編號以 FW123 開頭的銷售人員姓名
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c
JOIN HumanResources.Employee e ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN (
    SELECT SalesPersonID FROM Sales.SalesOrderHeader
    WHERE SalesOrderID IN (
        SELECT SalesOrderID FROM Sales.SalesOrderDetail
        WHERE ProductID IN (
            SELECT ProductID FROM Production.Product WHERE ProductNumber LIKE 'FW123%'
        )
    )
);

掌握子查詢分類、運算子選擇、相關/非相關特性、與 JOIN 互轉，是寫出高效、可維護 SQL 的關鍵。