EXISTS 与 IN：存在性检查

问题：查询有订单的用户

有三种写法：

方式 1：使用 IN

SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders);

方式 2：使用 EXISTS

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

方式 3：使用 JOIN

SELECT DISTINCT u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

三种结果相同，但性能和语义有差异。

EXISTS 基础

EXISTS 检查子查询是否返回至少一行。

语法：

SELECT columns
FROM table
WHERE EXISTS (subquery);

• 返回 TRUE：子查询有结果
• 返回 FALSE：子查询无结果

关键特性：

• EXISTS 只关心是否有行，不关心返回什么
• 通常使用 SELECT 1 或 SELECT *（性能相同）
• 短路求值：找到第一行就停止

示例：查询有订单的用户

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

结果：

  name
---------
 Alice
 Bob
 Charlie

工作原理：

1. 对 users 的每一行
2. 执行子查询，检查是否存在 user_id = u.id 的订单
3. 存在则返回该用户

NOT EXISTS：查询不存在的记录

NOT EXISTS 检查子查询是否不返回任何行。

-- 查询没有订单的用户
SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

结果：

  name
--------
 Diana

这是查找"孤儿记录"的推荐方式。

EXISTS vs IN

语义差异

IN：检查值是否在集合中

WHERE id IN (1, 2, 3)
WHERE id IN (SELECT user_id FROM orders)

EXISTS：检查是否存在满足条件的行

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

性能差异

IN：

• 子查询先执行，生成完整结果集
• 主查询对每行检查值是否在集合中
• 适用于子查询结果集较小的情况

EXISTS：

• 相关子查询，对主查询每行执行
• 找到第一个匹配就停止（短路求值）
• 适用于子查询结果集较大的情况

现代数据库优化器通常会自动选择最优执行计划，两者性能差异不大。但 EXISTS 在以下情况更优：

• 子查询返回大量行
• 只需检查存在性（不需要实际值）

示例对比

-- IN：生成所有 user_id
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

-- EXISTS：对每个用户检查，找到匹配就停止
SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NULL 的陷阱

NOT IN 与 NULL

重要：如果 IN 子查询包含 NULL，NOT IN 可能返回意外结果。

-- 假设 orders 表有一行 user_id 为 NULL
INSERT INTO orders (user_id, product, amount, order_date) VALUES
    (NULL, 'Gift', 100.00, '2026-01-20');

-- 查询没有订单的用户
SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);

结果：空集（0 行）！

为什么？

NOT IN (1, 2, 3, NULL) 等价于：

id != 1 AND id != 2 AND id != 3 AND id != NULL

id != NULL 的结果是 NULL（不是 TRUE），整个表达式变成 NULL，WHERE 过滤掉 NULL。

正确写法 1：排除 NULL

SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

正确写法 2：使用 NOT EXISTS（推荐）

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

NOT EXISTS 不受 NULL 影响。

IN 与 NULL

IN 对 NULL 的处理相对安全：

-- 如果 user_id 是 NULL，不会匹配任何 id
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

但 NULL IN (...) 结果是 NULL：

SELECT NULL IN (1, 2, 3);  -- 结果：NULL（不是 FALSE）

实际应用场景

场景 1：数据完整性检查

-- 查询有订单但用户不存在的记录（孤儿订单）
SELECT o.id, o.product
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

场景 2：复杂存在性条件

-- 查询至少有一个高价值订单（> 500）的用户
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id AND o.amount > 500
);

场景 3：排除已处理记录

-- 查询还没有评价的订单
SELECT o.product
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.order_id = o.id
);

场景 4：双向存在性

-- 查询既买过 Laptop 又买过 Monitor 的用户
SELECT u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.product = 'Laptop')
  AND EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.product = 'Monitor');

EXISTS vs JOIN vs IN 选择指南

场景	推荐方法	原因
只检查存在性	EXISTS	短路求值，语义清晰
需要返回关联表的列	JOIN	必须用 JOIN
子查询结果小（<1000行）	IN	简洁，性能可接受
子查询结果大	EXISTS	避免大集合比较
需要 NOT 操作	NOT EXISTS	避免 NULL 陷阱
需要 DISTINCT	EXISTS 或 JOIN + DISTINCT	IN 可能有重复

高级技巧

使用 EXISTS 代替 COUNT

-- 低效：COUNT 需要扫描所有行
SELECT CASE WHEN COUNT(*) > 0 THEN 'Yes' ELSE 'No' END
FROM orders
WHERE user_id = 1;

-- 高效：EXISTS 找到第一行就停止
SELECT CASE WHEN EXISTS (SELECT 1 FROM orders WHERE user_id = 1) THEN 'Yes' ELSE 'No' END;

EXISTS 与聚合结合

-- 查询有订单且平均订单金额 > 100 的用户
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
    GROUP BY o.user_id
    HAVING AVG(o.amount) > 100
);

常见错误

错误 1：NOT IN 忽略 NULL

-- 危险：如果子查询有 NULL，返回空集
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

错误 2：EXISTS 中使用 SELECT *

-- 低效：虽然能工作，但不必要
WHERE EXISTS (SELECT * FROM orders WHERE user_id = users.id)

-- 推荐：只检查存在性
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id)

实际上，现代优化器会忽略 SELECT 后的内容，两者性能相同。但 SELECT 1 更明确表达意图。

错误 3：混淆 IN 和 EXISTS 的场景

-- 不必要的 EXISTS（IN 更简洁）
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id IN (1, 2, 3))

-- 应该用 IN
WHERE user_id IN (1, 2, 3)

练习

1. 基本 EXISTS：查询有订单的用户（使用 EXISTS）。

2. NOT EXISTS：查询没有订单的用户（使用 NOT EXISTS）。

3. 复杂条件：查询有至少 2 个订单的用户（使用 EXISTS + 聚合）。

4. IN vs EXISTS：分别用 IN 和 EXISTS 查询购买过 'Laptop' 的用户，对比性能。

5. NULL 陷阱：创建一个 user_id 为 NULL 的订单，然后测试 NOT IN 和 NOT EXISTS 的差异。

6. 双向存在性：查询既有 Electronics 订单又有 Furniture 订单的用户。

7. 数据完整性：查询 orders 表中 user_id 在 users 表中不存在的记录。

8. 高级应用：查询每个类别中至少被 2 个不同用户购买过的产品。

完成练习后，打开本章答案页核对。

下一步

学会了 EXISTS 和 IN 的使用。下一节学习 UNION 和集合操作：如何合并多个查询结果，以及 INTERSECT、EXCEPT 的使用。

回到第三章概览 · 返回SQL 学习路线