37 EXISTS 与派生表

EXISTS (subquery) 在子查询返回至少一行时为真。它是回答“是否有任何相关行满足 X？”这类问题的主力工具——通常写成引用外层行的关联子查询。派生表是在 FROM 子句中使用的子查询，给它指定别名后，就可以像表一样使用。两者结合起来，涵盖了在接触 CTE 和窗口函数之前你会编写的大多数 SQL。

本章要点

• EXISTS 与 NOT EXISTS：测试“是否存在任何行”/“不存在任何行”
• 为什么对于可能含空值的子查询，NOT EXISTS 胜过 NOT IN
• 派生表：在 FROM 中使用并带有别名的子查询
• 什么时候派生表比 CTE 更清晰
• IN 与连接的关系

示例

-- EXISTS: customers who have at least one order
SELECT c.id, c.name
FROM customers_example c
WHERE EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- NOT EXISTS: customers with no orders (safe even with NULL customer_id values)
SELECT c.id, c.name
FROM customers_example c
WHERE NOT EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- Derived table: a subquery used in FROM, given an alias
SELECT sub.customer_id, sub.total_spent
FROM (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
) AS sub
WHERE sub.total_spent > 30
ORDER BY sub.customer_id;

-- Derived table joined back to a base table
SELECT c.name, latest.total
FROM customers_example c
JOIN (
  SELECT customer_id, max(total) AS total
  FROM orders_example
  GROUP BY customer_id
) AS latest ON latest.customer_id = c.id
ORDER BY c.id;

EXISTS 内部的约定写法 SELECT 1 并不是什么魔法——PostgreSQL 不关心子查询返回哪些列；它只检查是否会产生任何行。SELECT 1、SELECT * 和 SELECT NULL 的效果都一样。SELECT 1 表示“我不关心这些列”，因此成为约定俗成的写法。

派生表也称内联视图，其实就是 FROM 中的子查询。明确的别名（写在右括号后面）为结果指定一个可供引用的名称。派生表很容易组合：可以连接、过滤、分组，甚至嵌套它们。主要缺点是，复杂的派生表会把逻辑埋在查询中间。CTE（下一课）通常是为中间结果命名的更好方式。

运行示例

运行方式（需要 customers_example 和 orders_example）：

$ psql -f source/exists-and-derived-tables.sql postgres
 id | name
----+------
  1 | Alice
(1 row)

 id | name
----+------
  2 | Bob
  3 | Carol
(2 rows)
 ...

常见陷阱

• IN (SELECT col FROM t) 和 EXISTS (SELECT 1 FROM t WHERE t.col = outer.col) 通常等价——但当 NULL 可能出现时，NOT IN 与 NOT EXISTS 并不等价。只要子查询可能包含 NULL，就优先使用 NOT EXISTS。
• 像 AS x 这样的别名有助于提高清晰度和可移植性。PostgreSQL 16 及更高版本允许 FROM 中的子查询省略别名；较早的 PostgreSQL 版本则要求提供别名。
• 派生表内部可以使用 ORDER BY，但外层查询使用它时不会保留该顺序——应在外层排序。
• 一些旧指南说 EXISTS“总是比 IN 慢”。在现代 PostgreSQL 中，规划器对两者处理得都很好；应优先根据正确性和可读性选择。

下载

SQL 文件：exists-and-derived-tables.sql · 全部示例文件 · 准备工作