PHP小码农

Appearance

IN 与 NOT IN 子查询 ​

本节讲解 IN 与 NOT IN 子查询的集合成员语义、NOT IN 遇 NULL 全集失效的根本原因、ANY / ALL 与 IN 的等价关系，以及如何安全地把 NOT IN 改写成 NOT EXISTS。学完本节，你将彻底避开 SQL 中最经典的 NULL 陷阱。

前置知识

阅读本节前，建议先了解：EXISTS 子查询

基础概念 ​

IN 子查询 判断某个值是否属于子查询返回的集合，等价于对该集合内所有值做 OR 连接。NOT IN 是其否定形式。二者的 NULL 行为差异极大，是本节的核心。

概念术语说明
成员谓词Membership PredicateIN 判断值是否属于集合
三值逻辑Three-Valued Logictrue / false / NULL 三种结果
空值传播NULL PropagationNULL 参与比较结果仍为 NULL
全集失效Empty Result TrapNOT IN 遇 NULL 结果恒为空
集合成员Set MembershipIN 的语义本质
量词比较Quantified ComparisonANY / ALL 的正式名称
集合去重Set Deduplication优化器对 IN 右侧的隐式去重
半连接Semi JoinIN 被优化后的计划形态

IN 的语义可以展开为等价的 OR 表达式，这解释了它的全部行为：

text
x IN (a, b, c)      ≡  x = a OR x = b OR x = c
x NOT IN (a, b, c)  ≡  x <> a AND x <> b AND x <> c

用表格理解 NULL 的三值逻辑传播：

表达式展开含 NULL 时的结果说明
1 IN (1, 2)1=1 OR 1=2true有匹配即真
3 IN (1, 2)3=1 OR 3=2false全不匹配为假
3 IN (1, NULL)3=1 OR 3=NULLNULL含 NULL 无法判定
3 NOT IN (1, 2)3<>1 AND 3<>2true全不等为真
3 NOT IN (1, NULL)3<>1 AND 3<>NULLNULL无法判定
1 NOT IN (1, NULL)1<>1 AND 1<>NULLfalse有相等为假

关键结论：NOT IN 的子查询只要包含任意一个 NULL，结果就永远不会是 true。

语法与用法 ​

IN 子查询的四种形态：

text
-- 形态 1：值 IN 子查询
SELECT ... FROM 表名 WHERE 列 IN (SELECT 列 FROM 表名 WHERE 条件);

-- 形态 2：值 NOT IN 子查询
SELECT ... FROM 表名 WHERE 列 NOT IN (SELECT 列 FROM 表名 WHERE 条件);

-- 形态 3：IN 常量列表（等价于多个 OR）
SELECT ... FROM 表名 WHERE 列 IN (值1, 值2, 值3);

-- 形态 4：ANY / ALL 量词（IN 的正式形式）
SELECT ... FROM 表名 WHERE 列 = ANY (SELECT 列 FROM 表名);    -- 等价于 IN
SELECT ... FROM 表名 WHERE 列 <> ALL (SELECT 列 FROM 表名);   -- 等价于 NOT IN

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

sql
-- 用户表
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
);

-- 订单表：刻意保留 user_id 为 NULL 的游客订单
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 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'),  -- 游客订单，user_id 为 NULL
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

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

CREATE INDEX idx_orders_user_id ON orders (user_id);

ANALYZE users, orders, products;

详细说明 ​

NOT IN 的 NULL 陷阱 ​

先用最小例子复现问题：

sql
-- 子查询不含 NULL：NOT IN 正常工作
SELECT 3 NOT IN (1, 2) AS r1;
text
 r1
----
 t
sql
-- 子查询含 NULL：NOT IN 结果为 NULL
SELECT 3 NOT IN (1, NULL) AS r2;
text
 r2
----
        ← NULL，不是 false

在一行的比较中，NULL 会被 WHERE 当作"不满足"；在子查询中，这个效应会传导到全部行：

sql
-- 查询 1：id NOT IN (含 NULL 的子查询) —— 结果为空
SELECT u.name
FROM users AS u
WHERE u.id NOT IN (SELECT user_id FROM orders)
ORDER BY u.name;
text
 name
------
(0 rows)
sql
-- 查询 2：加上 IS NOT NULL 过滤后 —— 恢复正常
SELECT u.name
FROM users AS u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)
ORDER BY u.name;
text
 name  |   city
-------+----------
 dave  | chengdu
 erin  | beijing
(2 rows)
sql
-- 查询 3：NOT EXISTS —— 结果与查询 2 一致，且无需额外过滤
SELECT 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.name;
text
 name  |   city
-------+----------
 dave  | chengdu
 erin  | beijing
(2 rows)

三种写法的对比：

写法是否受 NULL 影响结果正确性推荐度
NOT IN (SELECT user_id FROM orders)✅ 受影响，恒空❌ 错误❌
NOT IN (SELECT ... WHERE user_id IS NOT NULL)否✅ 正确⚠️ 需额外过滤
NOT EXISTS (...)否✅ 正确✅ 首选
LEFT JOIN ... IS NULL否✅ 正确⚠️ 备选

IN 与 ANY / ALL 的等价关系 ​

IN 是 = ANY 的语法糖，而 ANY / ALL 提供了更丰富的形式：

写法等价形式语义
x IN (子查询)x = ANY (子查询)等于任意一个
x NOT IN (子查询)x <> ALL (子查询)不等于所有
—x > ANY (子查询)大于最小值
—x >= ALL (子查询)大于等于最大值
—x < ANY (子查询)小于最大值
—x <= ALL (子查询)小于等于最小值
sql
-- IN 与 = ANY 完全等价
SELECT count(*) AS in_cnt FROM users WHERE id IN (SELECT user_id FROM orders);
SELECT count(*) AS any_cnt FROM users WHERE id = ANY (SELECT user_id FROM orders);
text
 in_cnt | any_cnt
--------+---------
      3 |       3
sql
-- 用 ANY / ALL 表达"比较"语义（IN 无法表达）
-- 找出金额超过任意一笔已支付订单的用户订单
SELECT order_no, amount
FROM orders
WHERE status = 'pending'
  AND amount > ANY (SELECT amount FROM orders WHERE status = 'paid')
ORDER BY amount DESC;
text
 order_no | amount
----------+--------
 SO-1007  | 350.00
sql
-- ALL 语义：金额大于所有已支付订单
SELECT order_no, amount
FROM orders
WHERE status = 'pending'
  AND amount >= ALL (SELECT amount FROM orders WHERE status = 'paid')
ORDER BY amount DESC;
text
 order_no | amount
----------+--------
(0 rows)

优化器对 IN 的处理 ​

PostgreSQL 会把 IN 转为半连接，并对右侧做隐式去重：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name
FROM users AS u
WHERE u.id IN (SELECT user_id FROM orders);
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Semi Join  (cost=... rows=3 width=32) (actual time=0.055..0.066 rows=3 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              -- 隐式去重，只保留非 NULL
               ->  Seq Scan on orders o  (cost=... ) (actual time=0.010..0.015 rows=7 loops=1)

NOT IN 会转为反连接，但只在右侧无 NULL 时才安全：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name
FROM users AS u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Anti Join  (cost=... rows=2 width=32) (actual time=0.048..0.061 rows=2 loops=1)
   Hash Cond: (u.id = o.user_id)
   Hash Filter: (NOT (hashed SubPlan 1))          -- 计划中显式的 NULL 检测
   ->  Seq Scan on users u  (cost=... )
   SubPlan 1
     ->  Seq Scan on orders  (cost=... )
           Filter: (user_id IS NOT NULL)

注意 Hash Filter: (NOT (hashed SubPlan 1)) —— 当子查询无法被安全提升时，NOT IN 会退化为"先物化子查询再逐行判断"，性能显著低于反连接。

sql
-- 对比：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);
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Anti Join  (cost=... rows=2 width=32) (actual time=0.046..0.058 rows=2 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (cost=... )
   ->  Hash  (cost=... )
         ->  HashAggregate  (cost=... )
               Group Key: o.user_id                  -- 去重后的哈希表
               ->  Seq Scan on orders o  (cost=... )

IN 用于多值过滤与批量操作 ​

IN 的常量列表形式在实际业务中极常用：

sql
-- 常量列表：优化器会解析为数组或 OR 展开
SELECT order_no, status
FROM orders
WHERE status IN ('paid', 'refunded')
ORDER BY order_no;
text
 order_no | status
----------+--------
 SO-1001  | paid
 SO-1002  | paid
 SO-1003  | paid
 SO-1005  | paid
 SO-1006  | paid
(5 rows)
sql
-- 大常量列表（超过约 100 个）会被转为数组比较，效率更高
SELECT count(*) FROM orders WHERE order_no IN (
  'SO-1001','SO-1002','SO-1003','SO-1004','SO-1005','SO-1006','SO-1007'
);
sql
-- 用 = ANY(数组) 显式表达，配合 GIN 索引可加速
SELECT count(*) FROM orders WHERE order_no = ANY (ARRAY['SO-1001','SO-1002','SO-1003']);
text
 count
-------
     3
sql
-- 批量更新：只更新指定集合
UPDATE orders
SET status = 'refunded'
WHERE order_no IN ('SO-1004', 'SO-1007');
text
UPDATE 2

实战示例 ​

下面用完整流程做一份"沉睡用户召回"报表。

sql
-- 步骤 1：先确认数据中确实存在 NULL（这是陷阱的前提）
SELECT count(*) AS total, count(user_id) AS non_null, count(*) - count(user_id) AS null_cnt
FROM orders;
text
 total | non_null | null_cnt
-------+----------+----------
     7 |        6 |        1
sql
-- 步骤 2：错误示范 —— NOT IN 返回空集
SELECT u.id, u.name
FROM users AS u
WHERE u.id NOT IN (SELECT user_id FROM orders)
ORDER BY u.id;
text
 id | name
----+------
(0 rows)
sql
-- 步骤 3：定位问题 —— 单独查看子查询结果
SELECT user_id FROM orders ORDER BY user_id NULLS LAST;
text
 user_id
---------
       1
       1
       2
       2
       3
       3
         ← NULL
(7 rows)
sql
-- 步骤 4：正确改写为 NOT EXISTS
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;
text
 id | name |   city
----+------+----------
  4 | dave | chengdu
  5 | erin | beijing
(2 rows)
sql
-- 步骤 5：验证两种写法在过滤 NULL 后的一致性
SELECT
  (SELECT count(*) FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)) AS not_in_ok,
  (SELECT count(*) FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id))     AS not_exists_cnt;
text
 not_in_ok | not_exists_cnt
-----------+----------------
         2 |              2
sql
-- 步骤 6：进阶 —— 沉睡用户定义：无已支付订单，但有过其他订单
SELECT u.id, u.name
FROM users AS u
WHERE     NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid')
  AND     EXISTS     (SELECT 1 FROM orders o WHERE o.user_id = u.id)
ORDER BY u.id;
text
 id | name
----+-------
  3 | carol
(1 row)
sql
-- 步骤 7：用 IN 做"购买过指定分类"的筛选
SELECT DISTINCT u.id, u.name
FROM users  AS u
JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.id IN (
    SELECT i.order_id
    FROM order_items AS i
    JOIN products    AS p ON p.id = i.product_id
    WHERE p.category = 'peripherals'
  )
ORDER BY u.id;
text
 id | name
----+-------
  1 | alice
  2 | bob
(2 rows)
sql
-- 步骤 8：用 EXISTS 改写步骤 7，避免 DISTINCT
SELECT u.id, 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
  JOIN products    AS p ON p.id = i.product_id
  WHERE o.user_id = u.id AND o.status = 'paid' AND p.category = 'peripherals'
)
ORDER BY u.id;
text
 id | name
----+-------
  1 | alice
  2 | bob
(2 rows)

注意事项 ​

  • NOT IN 的子查询列必须保证非空，否则结果恒为空。 这是本节最重要的一条。
sql
-- 错误！子查询含 NULL 时结果永远为空
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 方案一：过滤 NULL（可行但脆弱，依赖后续维护者记得）
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

-- 方案二：改用 NOT EXISTS（推荐）
SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);
  • 不要忽略外层值本身为 NULL 的情况。 NULL IN (1, 2) 结果也是 NULL。
sql
SELECT NULL IN (1, 2) AS r;    -- 结果是 NULL，不是 false
  • 不要在 NOT IN 的常量列表中漏写类型转换。 类型不匹配会触发隐式转换，可能改变语义。
sql
-- 不推荐：user_id 是 integer，常量是 text，触发隐式转换
SELECT * FROM users WHERE id NOT IN ('1', '2');
  • 不要用 NOT IN 处理可能返回空集合的子查询。 空集合时 NOT IN 等价于 NOT IN ()，行为是"全部为真"，容易与预期不符。
sql
-- 子查询为空时，以下查询会返回所有 users
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE 1 = 0);
  • 不要滥用 IN 的庞大常量列表。 上万个常量会显著增加解析时间，应改用临时表或 VALUES 连接。
  • 不要忘记 IN 的右侧子查询只应返回一列。 多列需改用行 IN。
sql
-- 错误！subquery has too many columns
SELECT * FROM users WHERE id IN (SELECT user_id, amount FROM orders);

-- 正确：单列
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 或使用行 IN
SELECT * FROM users WHERE (id, city) IN (SELECT user_id, city FROM orders);
  • 不要在 ANY / ALL 与 IN 之间随意互换。 <> ALL 与 NOT IN 虽然等价，但 > ALL 等语义在 IN 中没有对应形式。

最佳实践 ​

  1. "不在集合中"一律用 NOT EXISTS，不用 NOT IN。
sql
SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  1. 必须用 NOT IN 时，在子查询内显式过滤 NULL。
sql
SELECT u.name FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
  1. 在 NOT NULL 列上使用 NOT IN 是安全的。 让列约束成为你的守护者。
sql
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;    -- 从源头消除风险
  1. IN 常量列表控制在合理规模（几百以内）。 大量值时改用 VALUES 连接或临时表。
sql
-- 上千个值改用 VALUES 连接
WITH wanted(order_no) AS (
  VALUES ('SO-1001'), ('SO-1002'), ('SO-1003')
)
SELECT o.* FROM orders o JOIN wanted w ON w.order_no = o.order_no;
  1. 用 EXPLAIN 确认 NOT IN 是否被转为 Hash Anti Join。 出现 hashed SubPlan 说明未优化，应改写为 NOT EXISTS。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  1. 类型匹配。 IN 两侧类型一致才能走索引。
sql
-- 正确：整数对整数
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
  1. 业务上"不存在"的语义优先用 EXISTS 家族表达。 NOT IN 更适合表达"值不在枚举列表中"这类纯常量场景。

下一节

继续学习：相关子查询

参考链接 ​