同表Inner Join查询优化：避免重复使用IN子句的方法

首先是数据表结构与测试数据：

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY,
  key BOOLEAN NOT NULL
);

INSERT INTO my_table VALUES (10, true);
INSERT INTO my_table VALUES (11, true);
INSERT INTO my_table VALUES (12, true);
INSERT INTO my_table VALUES (20, false);
INSERT INTO my_table VALUES (21, false);

需求是筛选出包含id为11或12的关联记录（关联规则为：同key值且my_table_1.id < my_table_2.id）。最初的解决方案通过在JOIN条件中重复使用IN子句实现了正确结果，但当业务中需要匹配的id数量达到上万条时，重复的长IN列表会引发性能问题。

优化方案1：使用CTE（公共表表达式）存储目标id集合 #

通过CTE一次性定义需要匹配的id列表，后续只需引用该集合即可，避免重复编写IN子句：

WITH target_ids AS (
    -- 可直接替换为业务场景下的id查询语句，比如 SELECT id FROM business_table WHERE ...
    SELECT unnest(ARRAY[11, 12]) AS id
)
SELECT t1.id, t1.key, t2.id, t2.key
FROM my_table t1
INNER JOIN my_table t2 
    ON t1.id < t2.id
    AND t1.key = t2.key
WHERE EXISTS (
    SELECT 1 FROM target_ids ti WHERE ti.id IN (t1.id, t2.id)
);

优化方案2：使用临时表存储目标id集合（适合大数量id场景） #

当目标id数量极大时，临时表配合主键索引能显著提升匹配效率：

-- 创建临时表并插入目标id
CREATE TEMPORARY TABLE target_ids (id INT PRIMARY KEY);
INSERT INTO target_ids VALUES (11), (12);
-- 批量导入时推荐用 COPY 命令提升效率

-- 执行查询
SELECT t1.id, t1.key, t2.id, t2.key
FROM my_table t1
INNER JOIN my_table t2 
    ON t1.id < t2.id
    AND t1.key = t2.key
WHERE EXISTS (
    SELECT 1 FROM target_ids ti WHERE ti.id = t1.id OR ti.id = t2.id
);

优化说明 #

两种方案都只需要定义一次目标id集合，避免了重复IN子句带来的冗余和性能损耗：

• CTE写法简洁，适合中小规模的id列表；
• 临时表配合索引能快速完成大规模id的匹配，执行效率更高。

另外，原查询中放在JOIN条件里的筛选逻辑，移至WHERE子句后效果完全一致，可读性更强。

内容的提问来源于stack exchange，提问作者Michael Linker