SQL三表关联查询：筛选活跃会员并排除有效禁止联系人员

可直接运行的通用SQL语句 #

SELECT 
    m.ID,
    m.cancelled_on,
    mc.activity,
    mc.activity_date,
    mcs.mailing_suppression,
    mcs.valid_to
FROM MEMBERS m
INNER JOIN MEMBER_CATEGORY mc 
    ON m.ID = mc.ID
    AND mc.activity = 'rowing'
    AND mc.activity_date BETWEEN '2020-12-01' AND '2021-10-01'
LEFT JOIN MEMBER_CONTACT_SUPPRESSIONS mcs 
    ON m.ID = mcs.ID
WHERE
    m.cancelled_on IS NULL
    AND NOT (
        mcs.mailing_suppression IN ('DO NOT CONTACT','DO NOT TELEPHONE','DO NOT EMAIL')
        AND mcs.valid_to >= '2021-11-02'
    )

逻辑说明 #

• 关联MEMBER_CONTACT_SUPPRESSIONS使用LEFT JOIN而非INNER JOIN，避免直接筛掉没有禁止联系记录的正常用户，这类用户本身符合筛选规则，不应被排除。
• MEMBER_CATEGORY的筛选条件写在INNER JOIN的ON子句中可以提前过滤无效数据，提升查询效率，写在WHERE子句中最终效果一致。
• 排除规则用NOT()完整包裹，避免逻辑判断写反导致筛选结果错误。

如果你的数据库支持CTE语法，也可以使用分层写法，可读性更高：

WITH active_members AS (
    SELECT ID, cancelled_on FROM MEMBERS WHERE cancelled_on IS NULL
),
target_rowing_members AS (
    SELECT 
        am.ID,
        am.cancelled_on,
        mc.activity,
        mc.activity_date
    FROM active_members am
    INNER JOIN MEMBER_CATEGORY mc 
        ON am.ID = mc.ID
        AND mc.activity = 'rowing'
        AND mc.activity_date BETWEEN '2020-12-01' AND '2021-10-01'
)
SELECT 
    trm.*,
    mcs.mailing_suppression,
    mcs.valid_to
FROM target_rowing_members trm
LEFT JOIN MEMBER_CONTACT_SUPPRESSIONS mcs 
    ON trm.ID = mcs.ID
WHERE NOT (
    mcs.mailing_suppression IN ('DO NOT CONTACT','DO NOT TELEPHONE','DO NOT EMAIL')
    AND mcs.valid_to >= '2021-11-02'
)

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