如何编写SQL查询获取未被指定用户读取的M2M关联通知记录

原查询的问题 #

你之前的查询逻辑存在漏洞：当一条通知被多个用户读过（包括当前用户）时，LEFT JOIN会生成多条关联记录，其中一条是当前用户的ID，其余是其他用户的。WHERE条件里的snr.account_id IS NULL OR snr.account_id <> 'some-account-id'会保留非当前用户的关联记录，导致这条本应被排除的通知依然被返回，不符合“排除当前用户已读记录”的需求。

正确的查询写法 #

这里提供两种可靠的实现方式：

写法一：用NOT EXISTS子查询（逻辑清晰，推荐）

SELECT sn.id, sn.created_at, sn.updated_at, sn.message
FROM server_notifications sn
WHERE sn.deleted_at IS NULL
  AND NOT EXISTS (
    SELECT 1
    FROM server_notif_read_by_accounts snr
    WHERE snr.server_notification_id = sn.id
      AND snr.account_id = '当前登录用户ID'
  );

这个逻辑很直接：筛选所有未被软删除的通知，并且不存在当前用户的已读记录的条目。不管这条通知有没有被其他人读过，只要当前用户没读，就会被返回。

写法二：用LEFT JOIN限定关联条件

SELECT sn.id, sn.created_at, sn.updated_at, sn.message
FROM server_notifications sn
LEFT JOIN server_notif_read_by_accounts snr
  ON sn.id = snr.server_notification_id
  AND snr.account_id = '当前登录用户ID'
WHERE sn.deleted_at IS NULL
  AND snr.account_id IS NULL;

这种写法在LEFT JOIN阶段就只关联当前用户的已读记录，如果当前用户没读这条通知，snr的所有字段都会是NULL，最后通过snr.account_id IS NULL过滤出符合要求的通知。

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