介绍

一个 子查询 是嵌套在另一个查询中的 SQL 查询。就像一个函数可以调用另一个函数，一个 SELECT 可以包含另一个 SELECT —— 内部查询的结果由外部查询用于过滤、计算或组成最终结果。

子查询可以出现在三个可能的位置：在 SELECT、FROM 或 WHERE/HAVING 中。行为会根据子查询返回的结果类型而变化 —— 这正是按标量、列、行和表进行分类的意义所在。

对于示例，我们将使用：

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 中。

标量子查询 

-- 哪个客户下了最大金额的订单？
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。外部查询使用该值来查找姓名。

标量子查询也可以直接在 SELECT 中使用，作为计算列：

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

Enter fullscreen mode Exit fullscreen mode

如果标量子查询返回多于一行的结果，数据库会抛出错误。因此，通常使用 LIMIT 1 或聚合函数来确保这一点。

列子查询

返回单列的多行 —— 本质上是一个值列表。主要与 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

行子查询

返回包含多列的一行。不太常见，但对于一次性比较一组值很有用。

-- 查找名称和价格完全匹配的产品
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 的支持较为有限。

表子查询

返回多行多列 —— 一个完整的数据集，被当作临时表处理。也称为 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 之前评估 —— 表子查询绕过了这一点。

按行为分类

嵌套子查询（独立）

当子查询可以完全独立于外部查询执行时，它是 嵌套 的。它不引用外部表中的任何列 —— 它被评估一次，结果传递给外部查询使用。

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 —— 不需要计数或返回数据，只需确认至少存在一行即可。因此在许多情况下比 IN 更高效，特别是当子查询可能返回 NULL 时。

子查询 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。