如何获取所有关联邮箱均标记为无效的联系人？

需求 #

筛选出所有关联的邮箱中，invalid_email字段值全部为true的联系人。

表结构 #

CREATE TABLE contacts (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE email_addresses (
    id INT NOT NULL AUTO_INCREMENT,
    email_address VARCHAR(255) NOT NULL,
    invalid_email tinyint(1) NOT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE email_addr_bean_rel (
    id INT NOT NULL AUTO_INCREMENT,
    email_address_id VARCHAR(255) NOT NULL,
    contact_id VARCHAR(255) NOT NULL,
    bean_module VARCHAR(255) NOT NULL,
    PRIMARY KEY(Id)
);

测试数据 #

INSERT INTO contacts
    (id, first_name, last_name)
VALUES
    (1, 'Jill', 'Valentine'),
    (2, 'Ada', 'Wong'),
    (3, 'Leon', 'Kennedy')
;

INSERT INTO email_addresses
    (id, email_address, invalid_email)
VALUES
    (1, 'jill.valentine@racoon-city.pod.gob', true),
    (2, 'ada.wong@umbrella.com', true),
    (3, 'leon.kennedy@racoon-city.pod.gob', false),
    (4, 'rebeca.chambers@racoon-city.pod.gob', false)
;

INSERT INTO email_addr_bean_rel
    (id, email_address_id, contact_id, bean_module)
VALUES
    (1, '1', 1, 'contacts'),
    (2, '2', 2, 'contacts'),
    (3, '3', 2, 'contacts'),
    (4, '4', 3, 'contacts')
;

当前尝试的查询 #

SELECT eabr.contact_id
    FROM email_addr_bean_rel eabr
    JOIN email_addresses ea ON eabr.email_address_id = ea.id
    WHERE ea.invalid_email = false

该查询仅能找出关联了有效邮箱的联系人ID，无法直接得到目标结果。

正确的查询思路与实现 #

思路核心 #

要找出所有关联邮箱全无效的联系人，本质是排除那些存在至少一个有效邮箱（invalid_email=false）的联系人，或者验证联系人的所有关联邮箱中没有有效邮箱。

方法一：使用NOT EXISTS排除法 #

SELECT c.id, c.first_name, c.last_name
FROM contacts c
WHERE NOT EXISTS (
    SELECT 1
    FROM email_addr_bean_rel eabr
    JOIN email_addresses ea ON eabr.email_address_id = ea.id
    WHERE eabr.contact_id = c.id
      AND ea.invalid_email = false
)

逻辑说明：子查询会找到所有关联了至少一个有效邮箱的联系人，外层用NOT EXISTS就得到没有任何有效邮箱的联系人，即所有邮箱均无效的目标对象。

方法二：分组统计验证法 #

SELECT c.id, c.first_name, c.last_name
FROM contacts c
JOIN email_addr_bean_rel eabr ON c.id = eabr.contact_id
JOIN email_addresses ea ON eabr.email_address_id = ea.id
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(CASE WHEN ea.invalid_email = false THEN 1 ELSE 0 END) = 0

逻辑说明：按联系人分组后，统计每个联系人的有效邮箱数量，当数量为0时，说明该联系人的所有关联邮箱都是无效的，符合需求。

预期结果 #

执行上述任一查询，都会得到结果：

(1, 'Jill', 'Valentine')

原因：Ada关联了一个有效邮箱和一个无效邮箱，Leon的关联邮箱为有效，均不符合条件；只有Jill的所有关联邮箱均为无效，符合筛选要求。

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