嵌套WHERE子句查询聊天ID时返回行数超出预期

你的查询语句存在两个核心问题：

1. JOIN条件缺失聊天关联
   你在left join bv.junction_user_chat juc1时仅指定了juc1.user_id = 1，没有关联juc1.chat_id = c.id，这会导致juc1表中所有用户1的记录和每一条聊天记录做笛卡尔积匹配，juc2的情况同理。这种情况下，不管聊天是否同时包含用户1和3，都会产生匹配结果，根本无法筛选出目标聊天ID。

2. 逻辑判断无效
   WHERE条件里的(juc1.user_id = 1 and juc2.user_id = 3) or (juc1.user_id = 3 and juc2.user_id = 1)看起来是在匹配双向用户，但由于JOIN时没关联聊天ID，juc1和juc2的记录是独立匹配所有聊天的，这个条件会满足所有非管理员聊天的情况，自然返回了全部结果。

修正后的查询语句（你已给出的正确版本） #

select distinct on (c.id)
    c.id
from bv.chat c
left join bv.junction_user_chat juc1
    on juc1.chat_id = c.id
left join bv.junction_user_chat juc2
    on juc2.chat_id = c.id
where c.is_admin_chat = false
and (
        (juc1.user_id = 1 and juc2.user_id = 3)
    or
        (juc1.user_id = 3 and juc2.user_id = 1)
);

修正逻辑说明 #

• 两个JOIN都添加了chat_id = c.id的关联，确保juc1和juc2都是当前聊天下的用户关联记录。
• 通过条件判断同一个聊天下同时存在用户1和3的双向情况，精准筛选出两人共同的聊天ID。

更简洁的替代写法 #

也可以用分组统计的方式实现，逻辑更清晰：

select c.id
from bv.chat c
join bv.junction_user_chat juc on juc.chat_id = c.id
where c.is_admin_chat = false
and juc.user_id in (1,3)
group by c.id
having count(distinct juc.user_id) = 2;

这种写法先筛选出用户1或3的聊天关联记录，分组后统计该聊天下的不同用户数为2，确保同时包含两个目标用户。

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