多表关联SQL查询：筛选Active字段全为0的用户地域记录

针对你的需求，这里提供几种高效的实现方案，适配数十万级数据量的性能要求：

方案1：NOT EXISTS 子查询（推荐，逻辑直观且性能优异） #

通过子查询检查当前user_id+territory_id组合是否存在Active=1的记录，若不存在则保留该组合：

SELECT 
    u.user_id,
    u.name,
    t.post_code,
    u.territory_id
FROM Users u
JOIN Territories t ON u.territory_id = t.territory_id
WHERE NOT EXISTS (
    SELECT 1
    FROM Active_user_territory aut
    WHERE aut.user_id = u.user_id
      AND aut.territory_id = u.territory_id
      AND aut.Active = 1
);

逻辑说明：NOT EXISTS会快速终止子查询的匹配（一旦找到Active=1的记录就停止），配合合适的索引能大幅提升查询效率。

方案2：GROUP BY + HAVING 聚合筛选 #

先对Active_user_territory按组合分组，筛选出所有记录Active都为0的组合，再关联其他表取字段：

WITH valid_combinations AS (
    SELECT user_id, territory_id
    FROM Active_user_territory
    GROUP BY user_id, territory_id
    HAVING MAX(Active) = 0
)
SELECT 
    u.user_id,
    u.name,
    t.post_code,
    u.territory_id
FROM Users u
JOIN Territories t ON u.territory_id = t.territory_id
JOIN valid_combinations vc ON u.user_id = vc.user_id AND u.territory_id = vc.territory_id;

逻辑说明：MAX(Active)=0意味着该组合下所有Active值都是0（只要有一个1，MAX就会是1），适合需要先明确有效组合再关联的场景。

方案3：LEFT JOIN + IS NULL 排除法 #

将Users与存在Active=1的组合做左连接，筛选没有匹配到的记录：

SELECT 
    u.user_id,
    u.name,
    t.post_code,
    u.territory_id
FROM Users u
JOIN Territories t ON u.territory_id = t.territory_id
LEFT JOIN Active_user_territory aut 
    ON u.user_id = aut.user_id 
    AND u.territory_id = aut.territory_id
    AND aut.Active = 1
WHERE aut.user_id IS NULL;

逻辑说明：左连接后，aut.user_id IS NULL的记录就是不存在Active=1的组合，和NOT EXISTS逻辑类似，性能表现相近。

性能优化建议 #

针对数十万级数据量，务必在Active_user_territory表上创建复合索引：

CREATE INDEX idx_aut_user_territory_active ON Active_user_territory(user_id, territory_id, Active);

这个索引能让三种方案的查询都快速定位到目标数据，避免全表扫描。

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