介紹

一個 子查詢 是巢狀在另一個查詢中的 SQL 查詢。就像函式可以呼叫另一個函式一樣，一個 SELECT 可以包含另一個 SELECT — 而內部查詢的結果會被外部查詢用來過濾、計算或組成最終結果。

子查詢可能出現在三個位置：在 SELECT、在 FROM，或在 WHERE/HAVING。其行為會依據子查詢回傳的結果類型而改變——因此將其分類為 Scalar、Column、Row 與 Table 才具有意義。

以下範例將使用下列資料表：

clientes: id, nome, cidade
pedidos:  id, cliente_id, valor, criado_em
produtos: id, nome, preco, categoria_id
categorias: id, nome

Enter fullscreen mode Exit fullscreen mode

依結果分類

回傳恰好 一個值：一行、一欄。可用在任何需要單一值的地方——在 SELECT、在 WHERE，或在 HAVING。

Scalar 子查詢 

-- 哪位客戶下了最高金額的訂單？
SELECT nome
FROM clientes
WHERE id = (
  SELECT cliente_id
  FROM pedidos
  ORDER BY valor DESC
  LIMIT 1
);

Enter fullscreen mode Exit fullscreen mode

內部的子查詢會回傳單一的 cliente_id —— 也就是最高金額訂單所屬的客戶 ID。外部查詢則使用這個值來取得客戶姓名。

Scalar 子查詢也可以直接放在 SELECT 中，作為計算欄位：

SELECT
  nome,
  (SELECT COUNT(*)FROM pedidosWHERE cliente_id= c.id)AS total_pedidos
FROM clientes c;

Enter fullscreen mode Exit fullscreen mode

如果 scalar 子查詢回傳超過一行，資料庫會丟出錯誤。因此常見的做法是搭配 LIMIT 1 或使用聚合函式來確保只回傳單一值。

Column 子查詢

回傳 單一欄位的多行資料 —— 本質上就是一份值清單。主要搭配 IN、 NOT IN、 ANY 與 ALL 等運算子使用。

-- 至少下過一筆金額超過 1000 的訂單的客戶
SELECT nome
FROM clientes
WHERE id IN (
  SELECT DISTINCT cliente_id
  FROM pedidos
  WHERE valor > 1000
);

Enter fullscreen mode Exit fullscreen mode

子查詢會回傳符合條件的客戶 ID 清單。外部查詢的 IN 運算子會檢查每個 id 是否出現在該清單中。

搭配 NOT IN 的反向查詢同樣實用——但需特別注意：若子查詢中包含任何 NULL， NOT IN 的行為會與直覺相反，可能導致查無任何資料。此時建議改用 NOT EXISTS。

ANY 與 ALL 在某些情境下是更具表達力的替代方案：

-- 價格高於「任一」類別 2 產品的商品
SELECT nome, preco
FROM produtos
WHERE preco > ANY (
  SELECT preco FROM produtos WHERE categoria_id= 2
);

-- 價格高於「所有」類別 2 產品的商品
SELECT nome, preco
FROM produtos
WHERE preco > ALL (
  SELECT preco FROM produtos WHERE categoria_id= 2
);

Enter fullscreen mode Exit fullscreen mode

Row 子查詢

回傳 包含多欄的單一行。較不常見，但適合一次比對多個值的集合。

-- 找出名稱與價格完全符合的產品
SELECT id
FROM produtos
WHERE (nome, preco)= (
  SELECT nome, preco FROM produtos WHERE id = 10
);

Enter fullscreen mode Exit fullscreen mode

整行比對可避免撰寫多個 AND 條件。各資料庫對此支援程度不同——PostgreSQL 與 MySQL 支援良好；SQL Server 則支援較有限。

Table 子查詢

回傳 多行多欄的完整資料集 —— 相當於一份暫存資料表。亦稱為 derived table 或 inline view。此類子查詢會出現在 FROM 子句中，且必須指定別名。

-- 依客戶計算營收，但只篩選營收超過 500 的客戶
SELECT cliente_nome, receita_total
FROM (
  SELECT
    c.nomeAS cliente_nome,
    SUM(p.valor) AS receita_total
  FROM clientes c
  JOIN pedidos p ON p.cliente_id= c.id
  GROUP BY c.nome
) AS resumo
WHERE receita_total> 500;

Enter fullscreen mode Exit fullscreen mode

內部子查詢會先計算每位客戶的營收。外部查詢則將此結果視為資料表，再套用篩選條件。這是因為 WHERE 會在 GROUP BY 之前評估，而 table 子查詢可以繞過此限制。

依行為分類

巢狀子查詢（獨立）

當子查詢可以完全獨立於外部查詢執行時，稱為 巢狀子查詢。它不會參考外部資料表的任何欄位——只會被執行一次，結果再傳回給外部查詢使用。

SELECT nome
FROM clientes
WHERE cidade= (
  SELECT cidade FROM clientes WHERE id= 1
);

Enter fullscreen mode Exit fullscreen mode

內部子查詢會查詢客戶 1 的城市。由於這與外部查詢的資料無關，因此只執行一次，回傳 'São Paulo'，再由外部查詢使用此值來篩選所有客戶。

因為是獨立的，巢狀子查詢通常效率較高——資料庫只執行一次，並重複使用結果。

關聯子查詢（相依）

當子查詢參考外部查詢的欄位時，稱為 關聯子查詢。這表示它會針對外部查詢處理的每一筆資料 重新評估一次——雖然在大表上可能較耗資源，但能表達其他方式無法完成的查詢。

-- 找出總訂單金額高於整體平均值的客戶
SELECT nome
FROM clientes c
WHERE (
  SELECT SUM(valor)
  FROM pedidos p
  WHERE p.cliente_id=  c.id-- 參考外部查詢
)> (
  SELECT AVG(valor) FROM pedidos
);

Enter fullscreen mode Exit fullscreen mode

子查詢中的 WHERE p.cliente_id = c.id 參考了來自外部查詢的 c.id。對外部查詢檢視的每位客戶，子查詢都會使用該客戶的 id 重新執行一次。

關聯子查詢最經典的用法是搭配 EXISTS 與 NOT EXISTS：

-- 曾下過至少一筆訂單的客戶
SELECT nome
FROM clientes c
WHERE EXISTS (
  SELECT 1
  FROM pedidos p
  WHERE p.cliente_id= c.id
);

-- 從未下過任何訂單的客戶
SELECT nome
FROM clientes c
WHERE NOT EXISTS (
  SELECT 1
  FROM pedidos p
  WHERE p.cliente_id = c.id
);

Enter fullscreen mode Exit fullscreen mode

EXISTS 只要找到第一筆符合的資料就會回傳 true——不需要計數或取出資料，只需確認至少存在一行。因此在許多情況下，特別是子查詢可能包含 NULL 時，EXISTS 比 IN 更有效率。

子查詢 vs JOIN

許多子查詢可以改寫成 JOIN，反之亦然。兩者的選擇取決於可讀性，有時也與效能有關——不過現代的查詢最佳化器通常會為兩者產生相同的執行計畫。

-- 使用子查詢
SELECT nome FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE valor > 1000);

-- 使用 JOIN
SELECT DISTINCT c.nome
FROM clientes c
INNER JOIN pedidos p ON p.cliente_id = c.id
WHERE p.valor > 1000;

Enter fullscreen mode Exit fullscreen mode

兩者結果相同。子查詢較接近自然語言的思考方式；JOIN 則明確表達資料表之間的關聯。請選擇能更清楚表達意圖的寫法——當子查詢可能包含 NULL 或回傳大量資料時，建議優先使用 EXISTS 而非 IN。