有些问题需要两个步骤才能回答。 “哪些订单高于平均水平？”要求你先求平均值，然后将每个订单与它进行比较。 SQL 子查询 允许您将这两个步骤编写为一条语句。

本教程使用两个小表涵盖每种类型。我们在 SQLite 3.50 中运行这些查询，显示的结果来自该运行。标准子查询语法得到广泛支持，我们指出了数据库不同的地方。

什么是子查询？

子查询也称为内部查询或嵌套查询，是放置在另一个语句内的 SELECT 语句，位于圆括号中。从概念上讲，内部查询生成外部查询然后使用的结果。

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

样本表

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

请注意，Dev 没有订单。这个细节对于下面的几个例子很重要。

子查询类型一览

标量子查询

标量子查询恰好返回一个值，因此您可以在单个值有意义的任何地方使用它。这是之前的查询及其结果。所有订单的平均值为 980：

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

您不能编写 WHERE total > AVG(total)，因为不允许直接在 WHERE 中进行聚合。子查询通过单独计算值来解决这个问题。

如果“标量”子查询返回多行怎么办？大多数数据库，包括 PostgreSQL、MySQL 和 SQL Server，都会引发错误。 SQLite 没有。在我们的测试中，它仅默默地使用第一行，这可能隐藏真正的错误，因此不要依赖此行为。

在和不在

当内部查询返回列表时，使用IN。这会找到至少一个订单超过 1000 的客户：

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN 则相反，查找从未订购过的客户：

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

这在这里可行，但是 NOT IN 有一个危险的边缘情况，我们将在下面介绍。

FROM 中的子查询

FROM 中的子查询充当临时表，通常称为派生表。当您需要聚合两次时，它非常有用，例如在每个城市中每个客户的平均支出的查询中：

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

内部查询对每个客户进行总计，外部查询对这些总计进行平均。为派生表提供别名是一个好习惯，因为某些数据库需要它。如果 GROUP BY 对您来说是新的，请参阅 SQL GROUP BY 和 HAVING。

SELECT 中的子查询

标量子查询还可以添加计算列。这个计算每个客户的订单：

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev 显示为零。内部联接会完全删除 Dev，因为 Dev 没有匹配的行。使用 LEFT JOIN 和 GROUP BY 可以获得相同的结果，我们在 SQL 连接解释 中对此进行了解释。

相关子查询

前面的示例也是相关的，因为内部查询引用外部查询中的 c.id。非相关子查询可以单独运行。相关的行不能，因为它取决于当前外行的值。

这会发现订单高于同一客户的平均水平：

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

以下是阅读方法。对于订单 101，内部查询对客户 1 的订单（1200 和 800）进行平均，得到 1000。由于 1200 更大，因此保留该行。对于订单 102，适用相同的平均值，但 800 并不更大，因此被删除。客户2的单笔订单等于其自身的平均订单，因此也被丢弃。

“每行一次”描述了逻辑，查询优化器通常将它们重写为更有效的东西。尽管如此，缓慢的相关子查询还是值得研究的。内部列上的索引（此处为 orders.customer_id）可以产生重大影响。请参阅SQL 索引解释。

存在与不存在

EXISTS 问一个是或否的问题：内部查询是否至少返回一行？它可以在第一个匹配处停止，并且你在里面选择什么并不重要，这就是为什么人们经常写SELECT 1。

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

NOT IN NULL 陷阱

这是最常见的子查询错误之一。我们创建了一个包含一个真实 ID 和一个 NULL 的表，然后针对它运行两种形式：

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

为什么第一个不返回任何内容？ id NOT IN (1, NULL) 表示 id <> 1 AND id <> NULL。与 NULL 的任何比较都是未知的而不是真实的，因此整个条件永远不可能为真。没有错误消息，也没有警告，只是一个看起来合理的空结果。

子查询、JOIN、CTE

许多查询可以用多种方式编写。早些时候，我们使用IN找到了大订单的客户。这是使用连接得到的相同结果：

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

请注意DISTINCT。如果客户有多个匹配项，则联接可以产生重复项，而 IN 和 EXISTS 则不能。

性能因数据库和数据而异，现代优化器通常会生成类似的计划。因此，首先选择最清晰的形式，然后检查EXPLAIN是否发现它很慢。

常见错误

• 对多行结果使用 =。 使用 IN，或确保内部查询返回一行。
• 在可为空的列上使用NOT IN。您可能会默默地丢失每一行。
• 使用不明确的别名。 在相关查询中，限定每一列，否则它可能绑定到错误的表。
• 深度嵌套。 三个级别很难阅读，因此将它们转换为 CTE。
• 内部添加ORDER BY。通常对外部结果没有影响，有些数据库拒绝它。

最后的话

子查询是表达两步问题的自然方式。从标量子查询和 IN 开始，然后通过一次推理单个外部行来练习相关子查询。只要可能出现 NULL，请记住 NOT EXISTS。为了练习，用三种方式编写一个问题，即子查询、连接和 CTE，然后比较结果和可读性。

常见问题

继续学习