PHP小码农

Appearance

集合操作 INTERSECT（交集） ​

本节讲解 INTERSECT 的语义、它与「内连接 + DISTINCT」的等价关系与性能差异、INTERSECT ALL 的多重集语义、与 IN / EXISTS 的取舍，以及用交集做数据一致性校验的实战套路。学完本节，你将能判断什么时候该用交集，什么时候该改写为半连接。

前置知识

阅读本节前，建议先了解：集合操作 UNION（并集）

基础概念 ​

交集（INTERSECT） 返回「同时出现在左右两侧」的行。它天然带去重语义：即使某行在两侧各出现 3 次，结果中也只出现 1 次。

概念术语说明
交集INTERSECT同时存在于两侧的行，去重
交集保留重复INTERSECT ALL按两侧较小出现次数保留
多重集Multiset允许重复元素，... ALL 系列的基础
半连接Semi Join只判断存在性的连接
去重Deduplication消除完全相同的行
双侧排序Dual Sort交集运算的排序归并实现
哈希去重Hash Aggregate交集运算的哈希实现

去重与保留重复的语义差异（设左侧 L、右侧 R 都含重复行）：

写法结果语义示例 L=(1,1,1) R=(1,1)
INTERSECT每个不同的行出现一次(1)
INTERSECT ALL出现次数取两侧最小值(1, 1)

INTERSECT 与「连接」的对照：

维度INTERSECTINNER JOIN
输出列两侧对应的列两侧所有列
重复行自动去重保留笛卡尔配对结果
列数要求必须相同无要求
NULL 处理靠集合语义，NULL 视为相等= 比较返回未知，需 IS NOT DISTINCT FROM
典型用途两结果集的公共行关联取数据

语法与用法 ​

text
查询1
INTERSECT [ALL]
查询2
[ORDER BY 列名 [ASC|DESC]]
[LIMIT n];

-- 可与 UNION / EXCEPT 混合，优先级为 INTERSECT 高于 UNION 与 EXCEPT
查询1
UNION
查询2
INTERSECT
查询3;
-- 等价于 查询1 UNION (查询2 INTERSECT 查询3)

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

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
);

-- 订单表
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 user_tags (
  user_id INTEGER NOT NULL,
  tag     TEXT    NOT NULL,
  PRIMARY KEY (user_id, tag)
);

-- 活动参与表
CREATE TABLE campaign_users (
  user_id     INTEGER NOT NULL,
  campaign    TEXT    NOT NULL,
  PRIMARY KEY (user_id, campaign)
);

造数：

sql
INSERT INTO users (name, city, vip)
SELECT '用户' || g, (ARRAY['北京','上海','广州','深圳'])[1 + g % 4], g % 5 = 0
FROM generate_series(1, 20000) AS g;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO-' || lpad(g::text, 6, '0'),
  1 + g % 20000,
  (random() * 1000)::numeric(10, 2),
  (ARRAY['paid','shipped','done','closed'])[1 + g % 4],
  DATE '2025-01-01' + (g % 365)
FROM generate_series(1, 80000) AS g;

-- 标签：每人 0~5 个
INSERT INTO user_tags (user_id, tag)
SELECT u.id, t.tag
FROM users u
CROSS JOIN LATERAL (
  SELECT unnest(ARRAY['数码','图书','运动','美食','旅行']) AS tag
  OFFSET 0 LIMIT (u.id % 6)
) AS t
ON CONFLICT DO NOTHING;

-- 活动参与：约一半用户
INSERT INTO campaign_users (user_id, campaign)
SELECT id, (ARRAY['618','双11'])[1 + id % 2]
FROM users
WHERE id % 2 = 0;

ANALYZE users, orders, user_tags, campaign_users;

详细说明 ​

INTERSECT 的执行方式 ​

PostgreSQL 对 INTERSECT 有两种实现：结果集较小时用哈希去重，较大或需要有序时用双侧排序归并。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id FROM user_tags WHERE tag = '数码'
INTERSECT
SELECT user_id FROM campaign_users WHERE campaign = '618';
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 HashSetOp Intersect  (cost=0.00..1182.40 rows=2861 width=4) (actual time=6.21..7.94 rows=2913 loops=1)
   ->  Append  (actual time=0.02..6.12 rows=9854 loops=1)
         ->  Seq Scan on user_tags  (actual time=0.01..2.44 rows=4000 loops=1)
               Filter: (tag = '数码'::text)
               Rows Removed by Filter: 33852
         ->  Seq Scan on campaign_users  (actual time=0.01..2.71 rows=10000 loops=1)
               Filter: (campaign = '618'::text)
               Rows Removed by Filter: 0
 Execution Time: 8.402 ms

HashSetOp Intersect 是交集专用节点：它为每一侧维护一个哈希表，最后取双方都有的键。当数据量大到哈希表放不下时，会退化为：

text
   ->  Sort ... 
   ->  Sort ...
   SetOp Intersect  (cost=...)

即先对两侧分别排序，再归并比较。

HashSetOp Intersect 的代价特征：内存占用与去重后的行数成正比，而不是原始行数。所以输入重复越多，交集运算越省内存。

INTERSECT 与 INNER JOIN + DISTINCT 的等价改写 ​

很多场景下 INTERSECT 与「内连接后 DISTINCT」语义一致，但计划形态不同。

sql
-- 写法 A：INTERSECT
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id FROM user_tags WHERE tag = '数码'
INTERSECT
SELECT user_id FROM campaign_users WHERE campaign = '618';
sql
-- 写法 B：INNER JOIN + DISTINCT
EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT t.user_id
FROM user_tags t
JOIN campaign_users c ON c.user_id = t.user_id
WHERE t.tag = '数码' AND c.campaign = '618';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=392.10..392.35 rows=2861 width=4) (actual time=5.88..6.02 rows=2913 loops=1)
   Group Key: t.user_id
   ->  Hash Join  (cost=172.00..378.40 rows=11444 width=4) (actual time=3.10..5.21 rows=2913 loops=1)
         Hash Cond: (c.user_id = t.user_id)
         ->  Seq Scan on campaign_users c  (actual time=0.01..1.42 rows=10000 loops=1)
               Filter: (campaign = '618'::text)
         ->  Hash  (actual time=0.01..1.21 rows=4000 loops=1)
               ->  Seq Scan on user_tags t  (actual time=0.01..0.94 rows=4000 loops=1)
                     Filter: (tag = '数码'::text)
 Execution Time: 6.902 ms

两者耗时相近（8.4ms vs 6.9ms），但改写 B 有一个重要优势：它可以返回额外的列。因为 INTERSECT 要求两侧列完全一致，一旦你想带上 users.name，就必须放弃交集改用连接。

sql
-- INTERSECT 做不到：两侧列数不同会报错
SELECT t.user_id, u.name FROM user_tags t JOIN users u ON u.id = t.user_id WHERE t.tag = '数码'
INTERSECT
SELECT user_id FROM campaign_users WHERE campaign = '618';
text
ERROR:  each INTERSECT query must have the same number of columns
LINE 1: SELECT t.user_id, u.name FROM user_tags t JOIN users u ON u.id...

结论：需要附加列、或需要进一步的排序分页时，改写为连接；纯粹比较两个集合的公共元素时，INTERSECT 更直观。

何时用 EXISTS 替代 INTERSECT ​

如果只是「判断存在性」或「筛选左表的行」，半连接通常比交集更快——因为它不需要对两侧都做去重。

sql
-- INTERSECT：两侧各自去重
EXPLAIN (ANALYZE)
SELECT user_id FROM user_tags WHERE tag = '数码'
INTERSECT
SELECT user_id FROM campaign_users WHERE campaign = '618';
sql
-- 半连接：只对右表建哈希，左侧边扫边输出，天然去重（若左表 user_id 唯一）
EXPLAIN (ANALYZE)
SELECT DISTINCT t.user_id
FROM user_tags t
WHERE t.tag = '数码'
  AND EXISTS (SELECT 1 FROM campaign_users c WHERE c.user_id = t.user_id AND c.campaign = '618');
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Unique  (cost=0.71..384.10 rows=2861 width=4) (actual time=5.12..5.30 rows=2913 loops=1)
   ->  Nested Loop Semi Join  (cost=0.71..384.00 rows=2861 width=4) (actual time=0.03..5.02 rows=2913 loops=1)
         ->  Seq Scan on user_tags t  (actual time=0.01..0.94 rows=4000 loops=1)
               Filter: (tag = '数码'::text)
         ->  Index Only Scan using campaign_users_pkey on campaign_users c
               (actual time=0.001..0.001 rows=1 loops=4000)
               Index Cond: ((user_id = t.user_id) AND (campaign = '618'::text))
               Heap Fetches: 0
 Execution Time: 5.608 ms

Nested Loop Semi Join + Unique：右表通过主键索引精确探测，不再需要为两侧同时维护哈希表。

写法计划节点去重次数适用场景
INTERSECTHashSetOp Intersect两侧各一次纯粹集合比较
JOIN + DISTINCTHashAggregate + Hash Join一次需要附加列
EXISTS + DISTINCTUnique + Semi Join一次有索引可探测
INHash Semi Join一次（右侧去重）单列，右侧较小

INTERSECT ALL 的多重集语义 ​

INTERSECT ALL 保留重复，出现次数取两侧的较小值。用一个最小例子说明：

sql
-- 左侧 (1,1,1)，右侧 (1,1)：结果 (1,1)
SELECT v FROM (VALUES (1),(1),(1)) AS a(v)
INTERSECT ALL
SELECT v FROM (VALUES (1),(1)) AS b(v);
text
 v
---
 1
 1
(2 rows)
sql
-- INTERSECT：只保留一次
SELECT v FROM (VALUES (1),(1),(1)) AS a(v)
INTERSECT
SELECT v FROM (VALUES (1),(1)) AS b(v);
text
 v
---
 1
(1 row)

INTERSECT ALL 的实现不会做去重，因此计划中不会出现 HashSetOp：

sql
EXPLAIN (ANALYZE)
SELECT order_no FROM orders WHERE status = 'closed'
INTERSECT ALL
SELECT order_no FROM orders WHERE status = 'closed';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 HashSetOp Intersect All  (cost=0.00..4812.00 rows=19995 width=13) (actual time=21.4..24.1 rows=19995 loops=1)
   ->  Append  (actual time=0.02..18.1 rows=39990 loops=1)
         ->  Seq Scan on orders  (actual time=0.01..9.31 rows=19995 loops=1)
               Filter: (status = 'closed'::text)
         ->  Seq Scan on orders  (actual time=0.01..9.31 rows=19995 loops=1)
               Filter: (status = 'closed'::text)
 Execution Time: 24.702 ms

节点名为 HashSetOp Intersect All：它给每行记一个计数器，两侧匹配时减 1，减到 0 就不再输出。

实际业务中 INTERSECT ALL 用得很少——多数场景要么需要去重（用 INTERSECT），要么根本不需要求交集（用 JOIN）。

NULL 在交集中的处理 ​

交集用集合语义比较，NULL 被视为彼此相等，这与 = 运算符的行为不同。

sql
-- 两个 NULL 在集合操作中相等，结果输出一行 NULL
SELECT NULL::integer AS v
INTERSECT
SELECT NULL::integer;
text
 v
---
  
(1 row)
sql
-- 但 = 运算符对 NULL 返回未知，取不到任何行
SELECT NULL::integer AS v WHERE NULL::integer = NULL::integer;
text
 v
---
(0 rows)

后果：如果某列含 NULL，INTERSECT 会把 NULL 当作一个有效的公共值参与匹配。若业务上希望忽略 NULL，必须显式过滤：

sql
SELECT user_id FROM orders WHERE created_at >= '2025-07-01' AND user_id IS NOT NULL
INTERSECT
SELECT user_id FROM campaign_users WHERE campaign = '618';

实战示例 ​

目标：校验两套独立计算路径得出的「高价值用户」名单是否完全一致。这是数据迁移、报表对账中的典型需求。

步骤 1：构造两条独立路径

sql
-- 路径一：按订单金额阈值
CREATE TABLE vip_path_a AS
SELECT user_id
FROM orders
WHERE user_id IS NOT NULL
GROUP BY user_id
HAVING sum(amount) > 3000;

-- 路径二：按订单笔数阈值
CREATE TABLE vip_path_b AS
SELECT user_id
FROM orders
WHERE user_id IS NOT NULL
GROUP BY user_id
HAVING count(*) >= 3;

ANALYZE vip_path_a, vip_path_b;

步骤 2：求交集 —— 两条路径都认可的用户

sql
SELECT count(*) AS intersect_cnt FROM (
  SELECT user_id FROM vip_path_a
  INTERSECT
  SELECT user_id FROM vip_path_b
) AS t;
text
 intersect_cnt
---------------
          6120
(1 row)

步骤 3：分别求单侧独有的用户

sql
-- 只在路径一：金额高但笔数少
SELECT count(*) AS only_a FROM (
  SELECT user_id FROM vip_path_a
  EXCEPT
  SELECT user_id FROM vip_path_b
) AS t;

-- 只在路径二：笔数多但金额低
SELECT count(*) AS only_b FROM (
  SELECT user_id FROM vip_path_b
  EXCEPT
  SELECT user_id FROM vip_path_a
) AS t;
text
 only_a
--------
   4387
(1 row)

 only_b
--------
   3502
(1 row)

步骤 4：用集合恒等式做一致性自检

理论上应满足：|A ∩ B| + |A − B| = |A|，|A ∩ B| + |B − A| = |B|。用一条 SQL 同时验证：

sql
WITH a AS (SELECT user_id FROM vip_path_a),
     b AS (SELECT user_id FROM vip_path_b),
     i AS (SELECT user_id FROM a INTERSECT SELECT user_id FROM b),
     oa AS (SELECT user_id FROM a EXCEPT SELECT user_id FROM b),
     ob AS (SELECT user_id FROM b EXCEPT SELECT user_id FROM a)
SELECT
  (SELECT count(*) FROM a)                       AS a_cnt,
  (SELECT count(*) FROM b)                       AS b_cnt,
  (SELECT count(*) FROM i)                       AS i_cnt,
  (SELECT count(*) FROM i) + (SELECT count(*) FROM oa) AS check_a,
  (SELECT count(*) FROM i) + (SELECT count(*) FROM ob) AS check_b;
text
 a_cnt | b_cnt | i_cnt | check_a | check_b
-------+-------+-------+---------+---------
 10507 |  9622 |  6120 |   10507 |    9622
(1 row)

check_a = a_cnt 且 check_b = b_cnt，恒等式成立，说明三条集合操作的结果自洽。

步骤 5：找出单侧独有用户的具体名单

sql
-- 金额高但笔数少的用户
SELECT u.id, u.name, u.city
FROM users u
JOIN (
  SELECT user_id FROM vip_path_a
  EXCEPT
  SELECT user_id FROM vip_path_b
) AS d ON d.user_id = u.id
ORDER BY u.id
LIMIT 10;
text
  id  |   name    |  city
------+-----------+--------
  137 | 用户137   | 广州
  401 | 用户401   | 上海
  688 | 用户688   | 北京
  923 | 用户923   | 深圳
 1140 | 用户1140  | 广州
 1587 | 用户1587  | 上海
 1902 | 用户1902  | 北京
 2318 | 用户2318  | 深圳
 2665 | 用户2665  | 广州
 3011 | 用户3011  | 上海
(10 rows)

步骤 6：把交集改写为半连接并对比耗时

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM (
  SELECT user_id FROM vip_path_a
  INTERSECT
  SELECT user_id FROM vip_path_b
) AS t;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=1421.00..1421.01 rows=1 width=8) (actual time=14.21..14.21 rows=1 loops=1)
   ->  HashSetOp Intersect  (actual time=12.10..13.84 rows=6120 loops=1)
         ->  Append  (actual time=0.02..9.42 rows=20129 loops=1)
               ->  Seq Scan on vip_path_a  (actual time=0.01..3.21 rows=10507 loops=1)
               ->  Seq Scan on vip_path_b  (actual time=0.01..3.10 rows=9622 loops=1)
 Execution Time: 15.104 ms
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vip_path_a a
WHERE EXISTS (SELECT 1 FROM vip_path_b b WHERE b.user_id = a.user_id);
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=0.29..612.10 rows=1 width=8) (actual time=3.42..3.42 rows=1 loops=1)
   ->  Nested Loop Semi Join  (actual time=0.02..2.88 rows=6120 loops=1)
         ->  Seq Scan on vip_path_a a  (actual time=0.01..0.94 rows=10507 loops=1)
         ->  Index Only Scan using vip_path_b_user_id_idx on vip_path_b b
               (actual time=0.001..0.001 rows=1 loops=10507)
               Index Cond: (user_id = a.user_id)
               Heap Fetches: 0
 Execution Time: 4.108 ms

前提是给右表建了索引。加上索引后，半连接比交集快 3~4 倍。

sql
CREATE INDEX IF NOT EXISTS vip_path_b_user_id_idx ON vip_path_b (user_id);

步骤 7：耗时汇总

写法计划核心节点执行耗时
INTERSECTHashSetOp Intersect15.1 ms
EXISTS（右表有索引）Nested Loop Semi Join4.1 ms
JOIN + DISTINCTHashAggregate + Hash Join6.9 ms

注意事项 ​

  1. INTERSECT 要求两侧列数与类型完全匹配，无法直接附加额外列：
sql
-- 错误！两侧列数不同
SELECT user_id, tag FROM user_tags
INTERSECT
SELECT user_id FROM campaign_users;
text
ERROR:  each INTERSECT query must have the same number of columns
LINE 2: INTERSECT

  1. INTERSECT 与 EXISTS 不等价：前者比较的是「整行所有列的组合」，后者只比较连接列。列数相同时通常一致，但语义意图不同。

  2. 优先使用 EXISTS 而非 INTERSECT 来做「筛选」，因为前者可以被优化器提升为半连接并利用索引。

  3. INTERSECT 的 ORDER BY 只能出现在最末尾，且只能引用第一段的列名：

sql
-- 错误！ORDER BY 在中间
SELECT user_id FROM user_tags ORDER BY user_id
INTERSECT
SELECT user_id FROM campaign_users;
text
ERROR:  syntax error at or near "INTERSECT"
LINE 2: INTERSECT
  1. INTERSECT 优先级高于 UNION 和 EXCEPT，混合使用时建议加括号明确意图：
sql
-- 保证可读性：显式括号
(SELECT user_id FROM vip_path_a
 UNION
 SELECT user_id FROM vip_path_b)
INTERSECT
SELECT user_id FROM campaign_users;
  1. 不要用 INTERSECT 代替 INNER JOIN 做表关联，它会静默丢掉所有非公共列，且丢失重复行信息。

最佳实践 ​

  1. 纯集合比较用 INTERSECT，取数据用 JOIN。 判断标准是「输出是否需要非公共列」。

  2. 筛选场景优先用 EXISTS + 索引，避免为两侧都建哈希表：

sql
SELECT a.user_id FROM vip_path_a a
WHERE EXISTS (SELECT 1 FROM vip_path_b b WHERE b.user_id = a.user_id);
  1. 对账场景用「交集 + 两侧差集」三件套，能精确定位差异所在：
sql
-- 交集、只在 A、只在 B
SELECT user_id FROM a INTERSECT SELECT user_id FROM b;
SELECT user_id FROM a EXCEPT    SELECT user_id FROM b;
SELECT user_id FROM b EXCEPT    SELECT user_id FROM a;

  1. 用集合恒等式做自检：|A∩B| + |A−B| = |A|，能在一条 SQL 中验证结果自洽。

  2. 含 NULL 的列在交集前显式过滤，避免 NULL 被当成公共值：

sql
SELECT user_id FROM orders WHERE user_id IS NOT NULL
INTERSECT
SELECT user_id FROM campaign_users;

  1. 需要保留重复次数时用 INTERSECT ALL，但先确认业务真的需要多重集语义。

  2. 混合使用集合操作时加括号，不要依赖 INTERSECT 高于 UNION 的优先级规则。

下一节

继续学习：集合操作 EXCEPT（差集）

参考链接 ​