SQL 巢狀子查詢深度解析：單行/多行、成對/非成對比較、Inline View 與實戰陷阱

SQL 巢狀子查詢：從原則到進階模式完全指南

巢狀子查詢 (子查詢中再含子查詢) 是處理多層邏輯的強大工具，但也易寫出低效或錯誤的 SQL。本文系統梳理類型、運算子、常見陷阱與優化重寫。

一、核心原則

1. 子查詢必須用括號包裹。
2. 置於比較運算子右側，提升可讀性。
3. 子查詢不可含 ORDER BY (除非配合 TOP/LIMIT 做 Top-N)。
4. 單行運算子 (=, <>, >, <, >=, <=) 要求子查詢**恰好回傳 1 行 1 列**；多行運算子 (IN, ANY, ALL, EXISTS) 接受多行單列。

二、單行子查詢 (Scalar Subquery)

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

-- 找出薪資等於全公司最低薪的員工
SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

陷阱：若子查詢回傳多行 → ORA-01427 / ERROR 1242。
修正：改用 IN 或 = ANY。

三、多行子查詢與運算子

四、多欄子查詢：成對 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);
-- 結果：僅 176 符合 (149, 80) 這組組合

-- 非成對比較：分別獨立匹配
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);
-- 結果：多筆，只要 manager_id 在集合中且 department_id 也在集合中即可

關鍵差異：成對比較要求**同一行**滿足所有欄位組合；非成對允許來自不同行。

五、相關子查詢：引用外部查詢欄位

-- 找出薪資高於所在部門平均薪資的員工
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 (派生表) / CTE 優化相關子查詢

-- Inline View (FROM 子查詢)
SELECT a.last_name, a.salary, a.department_id, b.avg_sal
FROM employees a
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary > b.avg_sal;

-- CTE 寫法 (更清晰、可重用)
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY department_id
)
SELECT a.last_name, a.salary, a.department_id
FROM employees a JOIN dept_avg d ON a.department_id = d.department_id
WHERE a.salary > d.avg_sal;

優勢：聚合只掃描一次，轉為 JOIN，優化器易產生 Hash/Merge Join，效能提升數量級。

七、多層巢狀實戰：跨表關聯鏈

-- 找出銷售過產品編號以 FW123 開頭的產品的銷售人員
SELECT DISTINCT c.last_name, c.first_name
FROM person.contact c
JOIN humanresources.employee e ON e.contact_id = c.contact_id
WHERE e.employee_id IN (
  SELECT sales_person_id FROM sales.salesorderheader
  WHERE salesorder_id IN (
    SELECT salesorder_id FROM sales.salesorderdetail
    WHERE product_id IN (
      SELECT product_id FROM production.product WHERE product_number LIKE 'FW123%'
    )
  )
);

此類多層 IN 子查詢建議改寫為連續 JOIN 或使用 EXISTS，避免重複掃描大表。

八、NULL 值陷阱總結

• NOT IN (subquery) 若子查詢結果含 NULL，整體回傳空集 → **嚴禁在 NOT IN 子查詢中出現 NULL**，改用 NOT EXISTS。
• 子查詢回傳 0 行：標量比較變 NULL，IN 為 FALSE，EXISTS 為 FALSE。