编写SQL查询筛选跨区域部分匹配（非完全匹配）的客户

需求描述 #

编写SQL查询以识别不同区域间部分匹配的客户：

• 返回所有欧洲客户中，与亚洲客户的email或phone_number匹配，但不同时两者都匹配的记录
• email或phone_number列可能为NULL
• 补充规则：
  1. 仅对比指定区域（欧洲 vs 亚洲）
  2. 可扩展对比更多属性（如zip_code），规则为任意属性匹配但非全部匹配则纳入结果

示例数据 #

CREATE TABLE IF NOT EXISTS customers
(
    id integer,
    email text,
    phone_number text,
    region text
);

TRUNCATE TABLE customers;

INSERT INTO customers (
    id, 
    email, 
    phone_number, 
    region
) VALUES 
    /* 纳入Bob：email匹配但phone_number不匹配 */
    (
        1,
        'bob@example.com',
        '+111111111',
        'europe'
    ),
    (
        2,
        'bob@example.com',
        NULL,
        'asia'
    ),
    /* 纳入Jane：email匹配但phone_number不匹配（反向） */
    (
        3,
        'jane@example.com',
        NULL,
        'europe'
    ),
    (
        4,
        'jane@example.com',
        '+222222222',
        'asia'
    ),
    /* 纳入John：email匹配但phone_number不匹配 */
    (
        5,
        'john@example.com',
        '+444444444',
        'europe'
    ),
    (
        6,
        'john@example.com',
        '+555555555',
        'asia'
    ),
    /* 纳入Sarah：phone_number匹配但email不匹配 */
    (
        7,
        'sarah@example.com',
        '+666666666',
        'europe'
    ),
    (
        8,
        'sarah.smith@example.com',
        '+666666666',
        'asia'
    ),
    /* 排除Paul：email和phone_number均不匹配 */
    (
        9,
        'paul@example.com',
        '+777777777',
        'europe'
    ),
    /* 排除Jenny：email和phone_number均不匹配（反向） */
    (
        10,
        'jenny@example.com',
        '+888888888',
        'asia'
    ),
    /* 排除Thomas：email和phone_number均匹配 */
    (
        11,
        'thomas@example.com',
        '+999999999',
        'europe'
    ),
    (
        12,
        'thomas@example.com',
        '+999999999',
        'asia'
    ),
    /* 排除Mathew：email和phone_number均匹配（phone为NULL） */
    (
        13,
        'mathew@example.com',
        NULL,
        'europe'
    ),
    (
        14,
        'mathew@example.com',
        NULL,
        'asia'
    ),
    /* 排除该用户：email和phone_number均匹配（email为NULL） */
    (
        15,
        NULL,
        '+000000000',
        'europe'
    ),
    (
        16,
        NULL,
        '+000000000',
        'asia'
    );

预期结果 #

当前尝试的问题 #

1. 原型查询能匹配email或phone_number，但无法排除两者均匹配的记录
2. 添加排除完全匹配的条件后，错误排除了部分匹配记录（因为JOIN后存在多条匹配，只要有一条完全匹配就会被排除）

解决方案 #

正确SQL实现 #

SELECT DISTINCT c.*
FROM customers c
WHERE c.region = 'europe'
AND EXISTS (
    SELECT 1
    FROM customers ac
    WHERE ac.region = 'asia'
    -- 至少一个属性匹配（含NULL相等的情况）
    AND (
        (c.email = ac.email OR (c.email IS NULL AND ac.email IS NULL))
        OR (c.phone_number = ac.phone_number OR (c.phone_number IS NULL AND ac.phone_number IS NULL))
    )
    -- 不是所有属性都匹配（含NULL相等的情况）
    AND NOT (
        (c.email = ac.email OR (c.email IS NULL AND ac.email IS NULL))
        AND (c.phone_number = ac.phone_number OR (c.phone_number IS NULL AND ac.phone_number IS NULL))
    )
)
ORDER BY c.id;

关键优化点 #

1. 使用EXISTS替代JOIN：避免JOIN带来的多匹配冲突，只需判断是否存在符合条件的亚洲客户即可
2. NULL值正确处理：SQL中NULL = NULL返回UNKNOWN，必须显式判断(c.email IS NULL AND ac.email IS NULL)来识别NULL相等的场景
3. 逻辑分层明确：
   • 第一层：验证存在亚洲客户与当前欧洲客户至少一个属性匹配
   • 第二层：验证不存在亚洲客户与当前欧洲客户所有属性都匹配

扩展到更多属性的通用写法 #

如果需要新增zip_code等属性，只需同步修改匹配条件和全匹配排除条件：

SELECT DISTINCT c.*
FROM customers c
WHERE c.region = 'europe'
AND EXISTS (
    SELECT 1
    FROM customers ac
    WHERE ac.region = 'asia'
    -- 至少一个属性匹配（新增属性添加对应判断）
    AND (
        (c.email = ac.email OR (c.email IS NULL AND ac.email IS NULL))
        OR (c.phone_number = ac.phone_number OR (c.phone_number IS NULL AND ac.phone_number IS NULL))
        OR (c.zip_code = ac.zip_code OR (c.zip_code IS NULL AND ac.zip_code IS NULL))
    )
    -- 不是所有属性都匹配（新增属性添加对应判断）
    AND NOT (
        (c.email = ac.email OR (c.email IS NULL AND ac.email IS NULL))
        AND (c.phone_number = ac.phone_number OR (c.phone_number IS NULL AND ac.phone_number IS NULL))
        AND (c.zip_code = ac.zip_code OR (c.zip_code IS NULL AND ac.zip_code IS NULL))
    )
)
ORDER BY c.id;

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