子查詢 (SQL Subquery)

子查詢 (Subquery) 是一個嵌套在另一個 SQL 語句中的 SELECT 查詢。子查詢被放在括號 () 中，可以出現在 SELECT、FROM、WHERE、HAVING 等子句中。子查詢又稱為「內部查詢 (Inner Query)」或「巢狀查詢 (Nested Query)」，而包含子查詢的查詢稱為「外部查詢 (Outer Query)」或「主查詢 (Main Query)」。

子查詢的基本語法 (Syntax)

在 WHERE 子句中使用：

SELECT column_list
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

在 FROM 子句中使用 (衍生表)：

SELECT column_list
FROM (SELECT column_list FROM table_name WHERE condition) AS derived_table;

在 SELECT 子句中使用 (純量子查詢)：

SELECT column1,
       (SELECT column_name FROM table_name WHERE condition) AS alias
FROM table_name;

子查詢的類型

1. 純量子查詢 (Scalar Subquery)

返回單一值 (一列一欄) 的子查詢。可以用在任何需要單一值的地方。

SELECT Name, Salary,
       (SELECT AVG(Salary) FROM employees) AS AvgSalary
FROM employees;

2. 行子查詢 (Row Subquery)

返回一列多欄的子查詢。

SELECT * FROM employees
WHERE (Dept, Salary) = (SELECT Dept, MAX(Salary) FROM employees GROUP BY Dept LIMIT 1);

3. 表子查詢 (Table Subquery)

返回多列多欄的子查詢，通常用在 FROM 子句中作為衍生表，或與 IN、EXISTS 一起使用。

SELECT * FROM (
    SELECT Dept, AVG(Salary) AS AvgSalary
    FROM employees
    GROUP BY Dept
) AS dept_avg
WHERE AvgSalary > 50000;

子查詢用法範例 (Example)

products_taiwan 資料表：

products_china 資料表：

範例： 使用子查詢找出在台灣有銷售但在中國沒有銷售的產品 (等同於 EXCEPT 或 MINUS)。

SELECT P_Name
FROM products_taiwan
WHERE P_Name NOT IN (SELECT P_Name FROM products_china);

結果：

子查詢搭配不同運算子

搭配 IN / NOT IN

-- 找出有訂單的客戶
SELECT * FROM customers
WHERE C_Id IN (SELECT DISTINCT C_Id FROM orders);

-- 找出沒有訂單的客戶
SELECT * FROM customers
WHERE C_Id NOT IN (SELECT C_Id FROM orders WHERE C_Id IS NOT NULL);

⚠️ 注意： 當子查詢結果可能包含 NULL 時，NOT IN 可能會返回空結果。建議使用 NOT EXISTS 或確保子查詢排除 NULL。

搭配比較運算子

-- 找出薪水高於平均的員工
SELECT * FROM employees
WHERE Salary > (SELECT AVG(Salary) FROM employees);

-- 找出薪水最高的員工
SELECT * FROM employees
WHERE Salary = (SELECT MAX(Salary) FROM employees);

搭配 EXISTS / NOT EXISTS

EXISTS 用於檢查子查詢是否有返回任何記錄：

-- 找出有訂單的客戶
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.C_Id = c.C_Id);

-- 找出沒有訂單的客戶
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.C_Id = c.C_Id);

搭配 ANY / SOME / ALL

-- 薪水大於任一業務部門員工的員工
SELECT * FROM employees
WHERE Salary > ANY (SELECT Salary FROM employees WHERE Dept = '業務');

-- 薪水大於所有業務部門員工的員工
SELECT * FROM employees
WHERE Salary > ALL (SELECT Salary FROM employees WHERE Dept = '業務');

相關子查詢 vs 非相關子查詢

非相關子查詢 (Non-Correlated Subquery)

子查詢可以獨立執行，不依賴外部查詢的值：

SELECT * FROM employees
WHERE Salary > (SELECT AVG(Salary) FROM employees);

相關子查詢 (Correlated Subquery)

子查詢引用外部查詢的欄位，每處理一筆外部記錄就執行一次：

-- 找出每個部門薪水最高的員工
SELECT * FROM employees e1
WHERE Salary = (
    SELECT MAX(Salary)
    FROM employees e2
    WHERE e2.Dept = e1.Dept  -- 引用外部查詢的 Dept
);

💡 效能提示： 相關子查詢通常效能較差，因為它需要為每一筆外部記錄重複執行。考慮使用 JOIN 或 CTE 來優化。

子查詢 vs JOIN vs CTE

常見使用情境

1. 替代 JOIN：當只需要比較值而不需要返回關聯表的欄位時。
2. 計算比較：與聚合結果比較 (如平均值、最大值)。
3. 存在性檢查：使用 EXISTS 檢查是否有相關記錄。
4. 衍生表：在 FROM 中建立臨時的資料集。