如何正确编写SQL查询获取无有效关联账户的catastros数据

• 现有SQL的WHERE条件逻辑存在漏洞：仅判断单条关联记录的账户状态，未排除「同时绑定了已删除账户和正常有效账户」的catastro数据。比如某catastro同时关联了1个已删除账户和1个有效账户时，现有WHERE条件会命中已删除账户的关联记录，错误地将该catastro返回，不符合「关联已删除账户的同时没有绑定其他有效账户」的要求。
• 多关联场景下直接用JOIN后的结果过滤，会因为单条关联记录满足条件，导致整个catastro被误返回，没有做全局的有效账户存在性校验。

你的两个查询要求本质可以合并为：返回所有没有关联任何有效（deleted_at为空）账户的catastros，可以用NOT EXISTS做存在性校验实现，SQL如下：

SELECT DISTINCT c.*
FROM cat_catastros c
WHERE NOT EXISTS (
    SELECT 1
    FROM inm_cuenta_catastro cc
    INNER JOIN inm_cuentas cu 
        ON cc.cuenta_id = cu.id 
        AND cu.deleted_at IS NULL
    WHERE cc.catastro_id = c.id
)

方案说明 #

• 子查询校验当前catastro是否存在关联的有效账户，只要不存在就符合返回要求，自然覆盖两种场景：1、从未关联过任何账户；2、关联的所有账户都是已删除状态。
• 加DISTINCT是为了避免单个catastro关联多个已删除账户时出现重复返回的问题，如果你需要返回关联的账户信息也可以保留原JOIN逻辑，只要在WHERE层加上上述的NOT EXISTS校验即可。

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