SQL 子查詢與巢狀子查詢實戰教學：單行、多行、多欄、相關子查詢

子查詢 (Subquery) 是 SQL 進階查詢核心技巧。本文從原則、分類到實戰範例，完整解析單行、多行、多欄、相關子查詢與內聯視圖用法。

子查詢三大原則

1. 必須置於括號 () 內。
2. 置於比較運算子右側，提升可讀性。
3. 子查詢 不可含 ORDER BY (除非配合 TOP-N/ LIMIT)。

分類與運算子

實戰範例 (以 employees 表為例)

單行子查詢

-- 職務同 141 號員工
SELECT last_name, job_id FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

-- 薪資高於 143 號且職務同 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);

-- 全公司最低薪資員工
SELECT last_name, job_id, salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

多行子查詢 (IN / ANY / ALL)

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

-- 薪資等於任一部門最低薪資的員工 (需用 IN)
SELECT employee_id, last_name FROM employees
WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

多欄子查詢 (成對 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);

-- 非成對：分別符合 manager_id 清單與 department_id 清單
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);

相關子查詢 (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
);

內聯視圖 替代相關子查詢 (效能較佳)

SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a
JOIN (
    SELECT department_id, AVG(salary) AS salavg
    FROM employees GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary > b.salavg;

多層巢狀子查詢 (跨表關聯)

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 + 衍生表 (內聯視圖)。
• NOT IN 子查詢若包含 NULL 會導致全不匹配，改用 NOT EXISTS。
• 適當建立子查詢參考欄位的索引。