如何查找未匹配另一表值的行？SQL多表域名匹配查询需求

表结构与测试数据 #

-- sales表结构与数据
CREATE TABLE sales (
    sale_id VARCHAR(18),
    list_of_email_addresses VARCHAR(8000)
);

INSERT INTO sales (sale_id, list_of_email_addresses) 
VALUES 
    ('12345', 'darba@snug.net, minty@snugs-canada.net, gravy@snugs.uk'),
    ('12346', 'tinto@muse.com, muscles@muse.net, garbo@muse.net'),
    ('12347', 'slick@bang.org, rick@bang.org, racer@bang.org');

-- accounts表结构与数据
CREATE TABLE accounts (
    account_id VARCHAR(18),
    domain_name VARCHAR(255)
);

INSERT INTO accounts (account_id, domain_name) 
VALUES 
    ('A-000001', 'canada.ca'),
    ('A-000002', 'thisfuntime.com'),
    ('A-000003', 'bang.org'),
    ('A-000004', 'snug.net');

需求1：找出无匹配账户的销售记录 #

需返回sales表中所有邮箱域名都未在accounts表domain_name中出现的记录：

• sale_id 12345不返回：其中snug.net匹配accounts表的A-000004
• sale_id 12346返回：所有邮箱的域名（muse.com、muse.net）均无匹配账户
• sale_id 12347不返回：其中bang.org匹配accounts表的A-000003

解决方案 #

SELECT s.sale_id, s.list_of_email_addresses
FROM sales s
WHERE NOT EXISTS (
    SELECT 1
    FROM accounts a
    WHERE s.list_of_email_addresses LIKE CONCAT('%@', a.domain_name)
       OR s.list_of_email_addresses LIKE CONCAT('%@', a.domain_name, ',%')
);

需求2：找出无关联销售记录的账户 #

需返回accounts表中domain_name从未出现在任何sales记录的邮箱域名中的账户，预期结果：

'A-000001', 'canada.ca'
'A-000002', 'thisfuntime.com'

解决方案 #

SELECT a.account_id, a.domain_name
FROM accounts a
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.list_of_email_addresses LIKE CONCAT('%@', a.domain_name)
       OR s.list_of_email_addresses LIKE CONCAT('%@', a.domain_name, ',%')
);

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