EXISTS 子查询

本节讲解 EXISTS 子查询的存在语义、与 JOIN 的本质区别（半连接）、NOT EXISTS 作为反连接的优势、相关性与优化器改写，以及它在去重、存在性判断、条件更新中的实用场景。学完本节，你将能用 EXISTS 写出不产生行膨胀的查询。

前置知识

阅读本节前，建议先了解：表子查询（派生表）

基础概念

EXISTS 子查询 是一个返回布尔值的谓词：只要子查询能返回至少一行，结果就是 true，否则是 false。它不关心子查询返回什么值，因此习惯上写 SELECT 1 或 SELECT *。

概念	术语	说明
存在谓词	EXISTS Predicate	判断子查询是否有结果
半连接	Semi Join	找到匹配即停止，不复制右表列
反连接	Anti Join	找不匹配的行，用于 NOT EXISTS
短路求值	Short-Circuit Evaluation	找到第一行立即返回，不扫描剩余部分
相关性	Correlation	子查询引用外层列，每行执行一次
行复制	Row Duplication	JOIN 会复制行，EXISTS 不会
提升	Subquery Pull-up	优化器把 EXISTS 转为 Semi Join

EXISTS 与 INNER JOIN 的根本差异在于是否复制行：

对比项	EXISTS	INNER JOIN
结果行数	与左表一致（最多）	可能膨胀（一对多）
右表列	不可用	可用
需要用 DISTINCT	否	常常需要
计划节点	Semi Join	Hash Join / Nested Loop
短路能力	✅ 找到即停	❌ 需处理所有匹配
适合场景	只判断"有没有"	需要右表数据

语法与用法

EXISTS 的标准语法：

-- 存在性判断
SELECT 列列表 FROM 表名 AS 别名
WHERE EXISTS (SELECT 1 FROM 关联表 WHERE 关联表.外键 = 别名.主键);

-- 否定形式
SELECT 列列表 FROM 表名 AS 别名
WHERE NOT EXISTS (SELECT 1 FROM 关联表 WHERE 关联表.外键 = 别名.主键);

-- EXISTS 可与其他条件组合
WHERE EXISTS (...) AND EXISTS (...) OR NOT EXISTS (...)

准备本节贯穿使用的示例表：

-- 用户表
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 订单明细
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL
);

-- 商品表
CREATE TABLE products (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      TEXT    NOT NULL UNIQUE,
  name     TEXT    NOT NULL,
  category TEXT    NOT NULL
);

INSERT INTO users (name, city, vip) VALUES
  ('alice', 'beijing',  true),
  ('bob',   'shanghai', false),
  ('carol', 'shenzhen', true),
  ('dave',  'chengdu',  false),      -- 无订单
  ('erin',  'beijing',  false);      -- 无订单

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),  -- 游客订单
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

INSERT INTO products (id, sku, name, category) VALUES
  (1, 'SKU-1001', '机械键盘',     'peripherals'),
  (2, 'SKU-1002', '无线鼠标',     'peripherals'),
  (3, 'SKU-1003', 'USB-C 扩展坞', 'accessories');

INSERT INTO order_items (order_id, product_id, qty, price) VALUES
  (1, 1, 1, 349.00),
  (1, 2, 2, 129.00),
  (2, 3, 1, 199.00),
  (3, 1, 1, 349.00),
  (3, 3, 1, 199.00),
  (5, 1, 1, 349.00),
  (5, 3, 1, 199.00);

CREATE INDEX idx_orders_user_id       ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

ANALYZE users, orders, order_items, products;

详细说明

EXISTS 与 INNER JOIN 的结果差异

最直观的对照是"找出有订单的用户"：

-- 写法 A：EXISTS —— 每行只出现一次
SELECT u.name, u.city
FROM users AS u
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.name;

 name  |   city
-------+----------
 alice | beijing
 bob   | shanghai
 carol | shenzhen
(3 rows)

-- 写法 B：INNER JOIN —— alice 出现 2 次（她有 2 笔订单）
SELECT u.name, u.city
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name;

 name  |   city
-------+----------
 alice | beijing
 alice | beijing          ← 行被复制
 bob   | shanghai
 bob   | shanghai         ← 行被复制
 carol | shenzhen
 carol | shenzhen         ← 行被复制
(6 rows)

要消除重复必须加 DISTINCT，而 DISTINCT 需要额外的排序或哈希去重：

-- 写法 C：JOIN + DISTINCT —— 结果正确但有去重开销
SELECT DISTINCT u.name, u.city
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name;

                              QUERY PLAN
-----------------------------------------------------------------------
 Unique  (cost=... ) (actual time=... rows=3 loops=1)
   ->  Sort  (cost=... ) (actual time=... rows=6 loops=1)      -- 需要排序去重
         Sort Key: u.name, u.city
         ->  Hash Join  (cost=... ) (actual time=... rows=6 loops=1)

而 EXISTS 被优化器转为半连接（Semi Join），匹配到第一行就停止：

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, u.city
FROM users AS u
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Semi Join  (cost=... rows=3 width=36) (actual time=0.052..0.062 rows=3 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=36) (actual time=0.010..0.014 rows=5 loops=1)
   ->  Hash  (cost=... ) (actual time=... rows=5 loops=1)
         ->  HashAggregate  (cost=... ) (actual time=... rows=5 loops=1)
               Group Key: o.user_id                  -- 右表先按连接键去重
               ->  Seq Scan on orders o  (cost=... ) (actual time=0.010..0.014 rows=7 loops=1)

注意 HashAggregate 先把 orders.user_id 去重，再做半连接 —— 这说明优化器理解 EXISTS 只需要"存在性"。

NOT EXISTS 与反连接

NOT EXISTS 会被转为反连接（Anti Join），这是它与 NOT IN 最重要的区别：

-- NOT EXISTS：找出没有订单的用户
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name
FROM users AS u
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.name;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost=... ) (actual time=0.086..0.088 rows=2 loops=1)
   Sort Key: u.name
   ->  Hash Anti Join  (cost=... rows=2 width=32) (actual time=0.049..0.062 rows=2 loops=1)
         Hash Cond: (u.id = o.user_id)
         ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=32) (actual time=0.010..0.014 rows=5 loops=1)
         ->  Hash  (cost=... ) (actual time=... rows=5 loops=1)
               ->  HashAggregate  (cost=... ) (actual time=... rows=5 loops=1)
                     Group Key: o.user_id
                     ->  Seq Scan on orders o  (cost=... ) (actual time=0.012..0.014 rows=7 loops=1)

 name  |   city
-------+--------
 dave  | chengdu
 erin  | beijing
(2 rows)

对比 NOT IN 的行为差异：

-- NOT IN：orders.user_id 含 NULL，导致整个结果为空
SELECT u.name
FROM users AS u
WHERE u.id NOT IN (SELECT user_id FROM orders)
ORDER BY u.name;

 name
------
(0 rows)

原因见下一节的详细分析。用表格总结三者的差异：

对比项	NOT EXISTS	NOT IN	LEFT JOIN ... IS NULL
NULL 安全	✅ 安全	❌ 遇 NULL 结果为空	✅ 安全
计划节点	Hash Anti Join	Hash Anti Join（无 NULL 时）	Hash Left Join + Filter
需要 DISTINCT	否	否	否（但右表需唯一）
可读性	高	中	低
推荐度	✅ 首选	⚠️ 确认无 NULL 时可用	⚠️ 备选

短路求值与相关性

EXISTS 的优势之一是短路：只要找到一行就返回，不必扫描完整个子查询。这在 LIMIT 1 结合索引时最明显：

-- 对每个用户判断"是否有大额订单"，找到一笔即停
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name
FROM users AS u
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.user_id = u.id AND o.amount > 500
);

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Semi Join  (cost=... ) (actual time=0.058..0.070 rows=2 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (cost=0.00..12.70 rows=370 width=32) (actual time=0.009..0.013 rows=5 loops=1)
   ->  Hash  (cost=... ) (actual time=... rows=2 loops=1)
         ->  HashAggregate  (cost=... ) (actual time=... rows=2 loops=1)
               Group Key: o.user_id
               ->  Seq Scan on orders o  (cost=... ) (actual time=0.010..0.016 rows=2 loops=1)
                     Filter: (amount > 500::numeric)

 name
-------
 alice
(1 row)

当左侧结果集很小、索引可用时，优化器会改用 Nested Loop Semi Join，此时短路最明显：

-- 单行驱动的半连接：loops=1 且内层找到即停
EXPLAIN (ANALYZE, BUFFERS)
SELECT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = 1);

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Result  (cost=0.29..0.30 rows=1 width=1) (actual time=0.020..0.021 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Only Scan using idx_orders_user_id on orders o  (cost=0.28..8.30 rows=2 width=0)
                                                                 (actual time=0.015..0.015 rows=1 loops=1)
           Index Cond: (user_id = 1)
           Heap Fetches: 0

actual rows=1 而 rows=2 是估算值，说明索引扫描在找到第一行后就停止了。

EXISTS 用于条件更新与删除

EXISTS 在 UPDATE / DELETE 中用于表达"仅当关联数据存在时"：

-- 只把"有已支付订单"的用户的 vip 升级
UPDATE users AS u
SET vip = true
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.user_id = u.id AND o.status = 'paid' AND o.amount >= 500
);

UPDATE 1

-- 删除"没有任何明细的订单"
DELETE FROM orders AS o
WHERE NOT EXISTS (
  SELECT 1 FROM order_items AS i WHERE i.order_id = o.id
);

DELETE 2

-- EXISTS 也支持在子查询中做多重判断
SELECT u.name
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.id
  WHERE o.user_id = u.id AND i.qty >= 2
);

 name
------
 alice
(1 row)

实战示例

下面用完整流程做一份"用户活跃度筛查"报表。

-- 步骤 1：有订单的用户（半连接）
SELECT u.id, u.name, u.city
FROM users AS u
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.id;

 id | name  |   city
----+-------+----------
  1 | alice | beijing
  2 | bob   | shanghai
  3 | carol | shenzhen
(3 rows)

-- 步骤 2：无订单的用户（反连接）
SELECT u.id, u.name, u.city
FROM users AS u
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
ORDER BY u.id;

 id | name |   city
----+------+----------
  4 | dave | chengdu
  5 | erin | beijing
(2 rows)

-- 步骤 3：既要有已支付订单，又不能有待支付订单
SELECT u.name
FROM users AS u
WHERE     EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id AND o.status = 'paid')
  AND NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id AND o.status = 'pending')
ORDER BY u.name;

 name
------
 alice
 bob
(2 rows)

-- 步骤 4：用 EXISTS 找"有订单但从未买过某分类商品"的用户
SELECT u.name
FROM users AS u
WHERE     EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id)
  AND NOT EXISTS (
    SELECT 1
    FROM orders      AS o
    JOIN order_items AS i ON i.order_id = o.id
    JOIN products    AS p ON p.id = i.product_id
    WHERE o.user_id = u.id AND p.category = 'peripherals'
  )
ORDER BY u.name;

 name
------
 carol
(1 row)

-- 步骤 5：验证 EXISTS 与 JOIN+DISTINCT 的结果一致性
WITH exists_ver AS (
  SELECT u.id, u.name FROM users u
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
),
join_ver AS (
  SELECT DISTINCT u.id, u.name FROM users u JOIN orders o ON o.user_id = u.id
)
SELECT
  (SELECT count(*) FROM exists_ver) AS exists_cnt,
  (SELECT count(*) FROM join_ver)   AS join_cnt,
  EXISTS (SELECT 1 FROM exists_ver EXCEPT SELECT * FROM join_ver) AS has_diff;

 exists_cnt | join_cnt | has_diff
------------+----------+----------
          3 |        3 | f

-- 步骤 6：性能对比 —— EXISTS 与 IN 的计划差异
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name FROM users u WHERE u.id IN (SELECT user_id FROM orders);
-- 无 NULL 的列会被优化为 Semi Join，与 EXISTS 等价

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Semi Join  (cost=... ) (actual time=... rows=3 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (cost=... )
   ->  Hash  (cost=... )
         ->  HashAggregate  (cost=... )
               Group Key: o.user_id

-- 步骤 7：检查 NULL 导致 NOT IN 失效的具体位置
SELECT
  (SELECT count(*) FROM users WHERE id NOT IN (SELECT user_id FROM orders))     AS not_in_cnt,
  (SELECT count(*) FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id)) AS not_exists_cnt;

 not_in_cnt | not_exists_cnt
------------+----------------
          0 |              2

not_in_cnt = 0 而 not_exists_cnt = 2 —— 这正是 SO-1006 的 user_id IS NULL 造成的。

注意事项

• 不要用 NOT IN 处理可能含 NULL 的列。 这是 SQL 中最经典的陷阱。

-- 错误！子查询含 NULL 时结果恒为空
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 正确：NOT EXISTS 不受 NULL 影响
SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);

• 不要在 EXISTS 子查询里写 SELECT * 并在外层引用。 EXISTS 的输出列对外层不可见。

-- 错误！列 o.amount 不存在于外层
SELECT u.name, o.amount FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 正确：需要右表列时用 JOIN
SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id = u.id;

• 不要用 EXISTS (... ) = true 这类冗余写法。 EXISTS 本身已经是布尔谓词。

-- 不推荐
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) = true;

-- 推荐
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

• 不要忽略相关子查询的索引需求。 o.user_id = u.id 中的 user_id 必须有索引，否则每行都全表扫描。

CREATE INDEX idx_orders_user_id ON orders (user_id);

• 不要用 EXISTS 替代需要右表数据的连接。 若最终需要 o.amount，必须用 JOIN。
• 不要假设 EXISTS 一定比 JOIN 快。 数据量大且两侧都需全扫时，两者计划可能相同。
• 不要忘记 EXISTS 与 NOT EXISTS 的组合顺序会影响可读性。 多个反连接叠加时建议用 CTE 分步。
• 不要在 EXISTS 子查询中加 ORDER BY。 存在性判断与顺序无关，排序是纯浪费。

-- 错误！无效排序
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id ORDER BY o.amount DESC);

-- 正确
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

最佳实践

1. "判断有没有"一律用 EXISTS，不要用 JOIN + DISTINCT。

SELECT u.name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

2. "判断没有"一律用 NOT EXISTS，不要用 NOT IN。

SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

3. 子查询的 SELECT 列表统一写 SELECT 1。 语义清晰且避免无谓的列解析。

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

4. 为相关列建索引。

CREATE INDEX idx_orders_user_id       ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

5. 在 UPDATE / DELETE 中用 EXISTS 表达关联条件，避免子查询返回多行错误。

UPDATE users AS u
SET vip = true
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id AND o.amount >= 500);

6. 用 EXPLAIN 确认出现了 Semi Join / Anti Join 节点。 只有这两个节点才说明优化器理解并利用了短路语义。

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

预期看到 Hash Anti Join。

7. 多个 EXISTS 组合时注意 AND / OR 的优先级与括号。

-- 用括号明确语义
WHERE (EXISTS (...) OR EXISTS (...))
  AND NOT EXISTS (...)

下一节

继续学习：IN 与 NOT IN 子查询

参考链接

• Subquery Expressions
• Table Expressions
• Using EXPLAIN
• Row and Array Comparisons