SQL优化：查询关联指定父ID列表但未关联指定单个父ID的子ID

优化方案 #

有比原MINUS写法更简洁、执行效率更高的实现，推荐以下两种：

方案1：关联+条件聚合（性能最优）

仅需一次表关联即可完成计算，避免多次子查询和表扫描，适合大数据量场景：

SELECT c.id AS child_id
FROM child_table c
INNER JOIN association_table a ON c.node_id = a.child_node
INNER JOIN parent_table p ON a.parent_node = p.node_id
WHERE p.id IN (1, 2, 4) -- 仅筛选需要判断的父ID，减少扫描数据量
GROUP BY c.id
HAVING MAX(CASE WHEN p.id IN (1, 2) THEN 1 ELSE 0 END) = 1 -- 至少关联了一个目标父ID
   AND MAX(CASE WHEN p.id = 4 THEN 1 ELSE 0 END) = 0; -- 未关联指定排除的父ID

方案2：EXISTS + NOT EXISTS（可读性最优）

逻辑直观易懂，执行效率也优于MINUS写法，可利用关联字段的索引提前终止匹配：

SELECT c.id AS child_id
FROM child_table c
-- 条件1：和父ID列表中至少一个ID关联
WHERE EXISTS (
    SELECT 1
    FROM association_table a
    INNER JOIN parent_table p ON a.parent_node = p.node_id
    WHERE a.child_node = c.node_id AND p.id IN (1, 2)
)
-- 条件2：未和指定父ID关联
AND NOT EXISTS (
    SELECT 1
    FROM association_table a
    INNER JOIN parent_table p ON a.parent_node = p.node_id
    WHERE a.child_node = c.node_id AND p.id = 4
);

方案优势说明 #

原MINUS写法需要执行两次独立的子查询链路，再对结果集做去重取差，会重复扫描子表、关联表、父表两次。上述两种方案仅需最多一次全量扫描，且可以通过给parent_table.id、association_table.parent_node、association_table.child_node加索引进一步提升查询效率。

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