SQL語法 - SUBQUERY

奧莉薇

更新於 2024/08/04發佈於 2024/08/04閱讀時間約 7 分鐘

※ 為什麼需要 Subquery？

當⼀個任務需要多個 Query 完成任務，可以使⽤ Subquery 把多個 Query 合併成⼀個 Query。

當我們在進行SQL查詢時，每次查詢都需要在Web Server和資料庫之間來回傳遞資料。這個過程會產生網路延遲，特別是當兩者之間的物理距離較遠時，這種延遲會更加明顯。多次查詢會增加這種延遲，從而影響效能。

使用子查詢（Subquery）可以將多個查詢合併成一個查詢，這樣只需要一次來回傳遞資料，就能獲得所需的結果。這不僅減少了網路延遲，還能提高Web Server生成正確HTML的速度，從而提升整體效能。

簡單來說，子查詢可以幫助我們減少資料傳輸的次數，降低網路延遲，並提高查詢效能。這對於需要頻繁查詢資料的應用程式來說，尤為重要。

※ 語法

使⽤括號括住 Query。

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
AND salary > 50000;

從 employees 表中選擇所有符合以下條件的記錄：

部門是 ‘Sales’ 或 ‘Marketing’：這部分 (department = 'Sales' OR department = 'Marketing') 表示選擇部門是銷售或市場的員工。
薪水大於 50000：這部分 AND salary > 50000 表示選擇那些薪水超過 50000 的員工。

這兩個條件必須同時滿足，才能選擇相應的記錄。換句話說，這個查詢會返回所有在銷售或市場部門工作且薪水超過 50000 的員工。

※ 解析順序

最深層的 Subquery(INNER query)開始：首先執行最內層的Subquery，這通常是用來計算或篩選數據的。
⼀層⼀層往外：接著執行外層的子查詢，依次向外層執行，直到所有子查詢都完成。
執行主查詢：最後，使用子查詢的結果來執行主查詢。

簡單來說，SQL 會先處理最內層的子查詢，然後逐層向外執行，最終完成主查詢。

※ 使用場景

COMPARISON OPERATOR（比較運算子）例子：

查詢所有薪水高於平均薪水的員工。

//查詢所有薪水高於公司平均薪水的員工的 employee_id 和 employee_name。
SELECT employee_id, employee_name
FROM employees
//選擇那些薪水高於平均薪水的員工。
WHERE salary > (
//計算 employees 表中所有員工的平均薪水
    SELECT AVG(salary)
    FROM employees
);

FROM(DERIVED TABLE– 衍生資料表)例子：

Query 的結果其實也是一個 Table：子查詢的結果可以視為一個臨時表。
接在 FROM 後面：除了可以接已經存在於資料庫中的表之外，也可以接由子查詢生成的表。這個表我們稱之為衍生資料表（Derived Table），因為它是由子查詢生成的。

※ FROM語法：

// 從衍生資料表 derived_table_name 中選擇 column_list
SELECT column_list
// 產生一個臨時的衍生資料表
FROM (
// 從 table_1 中選擇 column_list
    SELECT column_list
    FROM table_1
) derived_table_name // 命名為 derived_table_name
// 使用 WHERE 子句來篩選 c1 列的值大於 0 的記錄
WHERE derived_table_name.c1 > 0;

WHERE IN/NOT IN例子：

在WHERE這行指令去一個欄位的值是否在一個集合裡面。

WHERE IN：

// 從 employees 表中選擇 employee_id 和 employee_name 列
SELECT employee_id, employee_name
FROM employees
// 篩選 department_id 並使用IN 運算子用來檢查 department_id 是否在指定的集合（1, 2, 3）中
WHERE department_id IN (1, 2, 3);WHERE NOT IN：

WHERE NOT IN ：

// 從 employees 表中選擇 employee_id 和 employee_name 列
SELECT employee_id, employee_name
FROM employees
// 篩選 department_id 並使用IN 運算子用來檢查 department_id 是否不在指定的集合（1, 2, 3）中
WHERE department_id NOT IN (1, 2, 3);

Correlated Subquery（相關子查詢）例子：

依賴外部查詢：相關子查詢會參考外部查詢中的數據，因此與外部查詢有關。
逐行解析：相關子查詢會對外部查詢中的每一行數據進行解析，並且每次都會重新計算子查詢。
每行執行一次：每當外部查詢處理一行數據時，相關子查詢都會執行一次，並使用該行數據來計算結果。

簡單來說，相關子查詢會依賴外部查詢中的數據，並且在外部查詢處理每一行數據時都會執行一次子查詢。

※ 選擇所有薪水高於其所在部門平均薪水的員工。

// 從 employees 表中選擇 employee_id 和 employee_name 列
SELECT employee_id, employee_name
FROM employees e
WHERE salary > (
// 子查詢中的 e.department_id 參考了外部查詢中的 department_id
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

※ EXIST / NOT EXIST

當我們在 WHERE 子句中使用 EXISTS 時，這個語法會檢查後面的子查詢 (Subquery) 是否返回非空集合。如果子查詢返回的結果不是空集合，那麼該記錄 (RECORD) 就會被保留下來。

由於這個子查詢會使用到外部的表 (TABLE)，因此它是一個相關子查詢 (Correlated Subquery)。相關子查詢的執行方式是遍歷外部表的每一個記錄，並且每遍歷一個記錄，子查詢都會重新執行一次。這樣可以根據當前記錄的值來動態填入子查詢中的條件。

簡單來說，WHERE EXISTS 會根據子查詢的結果來篩選記錄，而相關子查詢會根據外部表的每個記錄動態執行。

EXIST / NOT EXIST例子：

※ 返回所有有訂單記錄的員工。

//選擇了 employees 表中的 employee_id 和 employee_name 列。
SELECT employee_id, employee_name
FROM employees e
//使用了 EXISTS 子句來檢查某些條件是否成立。
//EXISTS 子句會返回布爾值（TRUE 或 FALSE），用來決定是否保留主查詢中的記錄。
WHERE EXISTS (
//子查詢會檢查 orders 表中是否存在與 employees 表中的 employee_id 匹配的記錄。
    SELECT 1
    FROM orders o
    WHERE o.employee_id = e.employee_id
);