PostgreSQL多列NOT IN查询优化：避免重复执行子查询

首先明确原查询的两个核心问题：

1. 子查询返回a.item_id, b.item_id两列，但NOT IN仅支持单列匹配，这会直接导致语法错误——由于table_a和table_b通过item_id关联，子查询实际只需返回两者共有的item_id集合即可。
2. 重复执行完全相同的关联子查询，既冗余又可能影响性能（尽管部分数据库会自动缓存，但显式优化更可控、可读性更强）。

优化方案：用CTE（公共表表达式）复用子查询结果 #

CTE可以让你预先计算一次关联结果，之后在主查询中多次引用，确保子查询仅执行一次。

场景1：匹配原查询逻辑（item_a_id和item_b_id都不在关联结果中）

WITH valid_item_ids AS (
    -- 仅执行一次的关联查询，得到table_a和table_b共有的item_id集合
    SELECT a.item_id 
    FROM table_a a 
    JOIN table_b b ON a.item_id = b.item_id
)
SELECT c.id  -- 按需求筛选记录ID，而非所有字段
FROM table_c c
WHERE c.item_a_id NOT IN (SELECT item_id FROM valid_item_ids)
  AND c.item_b_id NOT IN (SELECT item_id FROM valid_item_ids);

场景2：匹配“不同时存在”的需求（排除item_a_id和item_b_id都在关联结果中的记录）

如果你的真实需求是只要item_a_id或item_b_id有一个不在关联结果中，就保留该记录（即“不同时存在”的准确逻辑），可以调整WHERE条件：

WITH valid_item_ids AS (
    SELECT a.item_id 
    FROM table_a a 
    JOIN table_b b ON a.item_id = b.item_id
)
SELECT c.id
FROM table_c c
WHERE NOT (
    c.item_a_id IN (SELECT item_id FROM valid_item_ids)
    AND c.item_b_id IN (SELECT item_id FROM valid_item_ids)
);

更安全的替代方案：使用EXISTS #

如果valid_item_ids中存在NULL值，NOT IN会导致整个查询返回空结果（因为NULL的比较逻辑特殊），此时用EXISTS更安全，性能也更稳定：

WITH valid_item_ids AS (
    SELECT a.item_id 
    FROM table_a a 
    JOIN table_b b ON a.item_id = b.item_id
)
SELECT c.id
FROM table_c c
WHERE NOT EXISTS (SELECT 1 FROM valid_item_ids vi WHERE vi.item_id = c.item_a_id)
   OR NOT EXISTS (SELECT 1 FROM valid_item_ids vi WHERE vi.item_id = c.item_b_id);

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