如何通过纯关联查询排除仅满足单一条件的Issue记录？

需求说明 #

需要排除仅满足pd.val = 1且整个Issue（i表记录）无任何pn.val = 1匹配记录的条目（即测试数据中的Issue 2），要求仅通过关联查询实现，禁止使用UNION或子查询。

数据库表结构与测试数据 #

-- 问题主表
create table i (
  id integer not null primary key
);

-- 问题参数表
create table p (
  id integer not null primary key,
  i_id integer not null references i (id)
);

-- n类型参数表
create table pn (
  id   integer not null primary key,
  p_id integer not null references p (id),
  val  integer
);

-- d类型参数表
create table pd (
  id   integer not null primary key,
  p_id integer not null references p (id),
  val  integer
);

-- 插入测试数据
insert into i values (1), (2);
insert into p values (1, 1), (2, 1), (3, 2);
insert into pn values (1, 1, 1);
insert into pd values (1, 2, 1), (2, 3, 1);

原查询及结果 #

原查询会返回包含Issue 2的记录：

select
  i.id i_id,
  p.id p_id,
  pn.id pn_id, pn.val pn_val,
  pd.id pd_id, pd.val pd_val
from i
  join p on i.id = p.i_id
  left join pn on p.id = pn.p_id
  left join pd on p.id = pd.p_id
where
  pn.val = 1
  or
  pd.val = 1;

查询结果：

+----+----+-----+------+-----+------+
|i_id|p_id|pn_id|pn_val|pd_id|pd_val|
+----+----+-----+------+-----+------+
|1   |2   |null |null  |1    |1     |
|2   |3   |null |null  |2    |1     |
|1   |1   |1    |1     |null |null  |
+----+----+-----+------+-----+------+

解决方案查询 #

通过新增两层内关联（p_filter和pn_filter）过滤出存在至少一条pn.val = 1记录的Issue，再关联该Issue的所有参数记录并保留符合条件的条目：

select
  i.id i_id,
  p.id p_id,
  pn.id pn_id, pn.val pn_val,
  pd.id pd_id, pd.val pd_val
from i
  -- 关联过滤：确保当前Issue存在至少一条pn.val=1的参数记录
  inner join p p_filter on i.id = p_filter.i_id
  inner join pn pn_filter on p_filter.id = pn_filter.p_id and pn_filter.val = 1
  -- 关联当前Issue的所有参数记录
  join p on i.id = p.i_id
  left join pn on p.id = pn.p_id
  left join pd on p.id = pd.p_id
where
  pn.val = 1 or pd.val = 1;

查询结果 #

+----+----+-----+------+-----+------+
|i_id|p_id|pn_id|pn_val|pd_id|pd_val|
+----+----+-----+------+-----+------+
|1   |1   |1    |1     |null |null  |
|1   |2   |null |null  |1    |1     |
+----+----+-----+------+-----+------+

逻辑说明 #

• inner join p p_filter + inner join pn pn_filter：仅保留存在至少一条pn.val=1参数记录的Issue（即Issue 1），直接排除无匹配的Issue 2。
• 后续的join p、left join pn、left join pd：保留该Issue下所有符合pn.val=1或pd.val=1的参数记录。

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