SQLite 子查询：WHERE、FROM 和 SELECT 中的嵌套查询

子查询就是 SELECT 里的 SELECT

子查询就像它的名字一样：一条 SELECT 语句藏在另一条语句里，用括号括起来。SQLite 先执行内层查询，拿到结果，再交给外层查询使用。

先准备一个可以反复使用的小例子：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

五个订单，四个客户，其中两个客户什么都没买过。下面会一直用这组数据。

WHERE 中的子查询：按列表过滤

最常见的形式：在内层查询中取出一组 id，再用它过滤外层查询。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

内层查询返回 orders 中出现过的每个 customer_id。外层查询只保留 id 在这个列表里的客户。Cleo、Boris 和 Ada 会出现在结果中，没有订单的 Dmitri 不会。

IN (SELECT ...) 是处理“A 中在 B 里有匹配的行”这类问题的主力写法。可以在心里把它读成“这一列的值是内层查询返回的值之一”。

NOT IN：小心 NULL

反过来的问题“哪些客户没有下过单？”只差一个词：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

在这里它能正常工作。但 NOT IN 有个坑：只要子查询返回了一个 NULL，整个 NOT IN 就会变成 NULL（不等于 TRUE），结果一行都不返回。既出人意料，又不报任何错。

对可能包含 NULL 的列使用 NOT IN 时，稳妥的习惯是：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

或者改用 NOT EXISTS，它完全没有这个问题。后面会讲到。

标量子查询：一行一列

标量子查询只返回一个值，也就是一行一列，凡是需要一个值的地方都可以用它。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

内层的 SELECT MAX(total) FROM orders 返回 200，外层查询再筛选出等于这个值的订单。需要和聚合结果做比较时，这种写法随时用得上。

标量子查询也可以放在 SELECT 列表里，给每一行附加一个计算出的值：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

customers 的每一行都会代入 customers.id，执行一次内层查询。这就是相关子查询，下面会详细介绍。对于这种“每行一个数字”的场景，LEFT JOIN 加 GROUP BY 通常性能更好，但标量写法读起来非常直观。

EXISTS：只检查有没有匹配

EXISTS 是 IN 更低调的“表亲”。它不关心值，只检查子查询是否返回了至少一行。里面通常写 SELECT 1，因为选哪一列都无所谓。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

这条查询找出至少有一笔订单金额超过 100 的客户。内层查询引用了外层查询的 c.id，正是这一点让它成为相关子查询。SQLite 一找到匹配就停止扫描内层表，所以在“这一行有没有关联行？”这类问题上，EXISTS 往往比 IN 更快。

它的否定形式 NOT EXISTS，是询问“没有关联行”时不受 NULL 影响的安全写法：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

FROM 中的子查询：派生表

凡是能放表的地方都能放子查询，FROM 子句也不例外。内层查询会变成一个临时的、有名字的“派生表”，你可以对它做连接、过滤或聚合。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

内层查询算出每个客户的总金额，外层查询再按国家求这些总额的平均值。像这样的两阶段聚合，正是派生表的用武之地：一个 GROUP BY 做不完所有事情的时候。

AS per_customer 这个别名是必需的：每个派生表都要有名字。

相关子查询：外层每行执行一次

当子查询引用了外层查询的列时，它就是相关子查询。SQLite 必须为外层的每一行重新执行内层查询，这很灵活，但代价可能不小。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

为每个客户找出最大的一笔订单。内层查询依赖 customers.id，所以每个客户执行一次。没有订单的客户得到 NULL，这正是你想要的。

相关子查询天然适合“对 A 中的每一行，从 B 中算出点什么”这类需求。表很小或者查找有索引时，用它没问题。在没有合适索引的大表上，上线前先做性能分析，JOIN 加 GROUP BY 往往更快。

子查询还是 JOIN？

下面两条查询回答的是同一个问题：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Subquery
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

两者返回相同的行。SQLite 的优化器经常在内部把一种写法改写成另一种。所以按可读性来选：

• 只需要过滤，又不想让内层表的列混进结果时，用子查询。
• 结果需要两张表的列时，用 JOIN。
• 问的是“是否至少存在一行关联数据？”时，用 EXISTS：它更清楚，也能避开 IN/NOT IN 的 NULL 陷阱。

拿不准的时候，就写那个大声读出来也能自己解释清楚的版本。

常见陷阱：子查询返回多行

和 = 一起使用的子查询最多只能返回一行。如果返回了多行，SQLite 会挑其中一行（实际上相当于随机），你会悄无声息地得到错误结果，没有任何报错。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Risky: what if more than one customer is from the UK?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

内层查询可能返回多行时，用 IN：

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

如果你预期正好只有一行并且想确保这一点，加上 LIMIT 1 和 ORDER BY，至少让选择是确定的。更好的做法是：让数据本身保证只有一行（按唯一列过滤）。

下一步：公用表表达式

FROM 中的子查询很快就会变得难以驾驭，尤其是同一个派生表要用两次，或者嵌套到三层深的时候。公用表表达式（WITH ... AS (...)）让你预先给子查询起个名字，然后在语句的其余部分按名字引用它。这就是下一页的内容。