如何筛选两个结果集中productID匹配的共同记录？

修改后的查询方案 #

下面两种写法都能满足需求——只保留businessContacts和nonBusinessContacts两个表中共同存在的productID对应的记录，且productID > 10：

方法一：先提取共有的productID再筛选

WITH SharedProductIDs AS (
    -- 找出两个表中都存在且productID大于10的ID集合
    SELECT productID 
    FROM businessContacts
    WHERE productID > 10
    INTERSECT
    SELECT productID 
    FROM nonBusinessContacts
    WHERE productID > 10
),
CTE_Products AS (
    -- 从业务联系人表中筛选属于共有ID的记录
    SELECT productID, productName, companyName 
    FROM businessContacts
    WHERE productID IN (SELECT productID FROM SharedProductIDs)
    UNION ALL
    -- 从非业务联系人表中筛选属于共有ID的记录
    SELECT productID, productName, companyName 
    FROM nonBusinessContacts
    WHERE productID IN (SELECT productID FROM SharedProductIDs)
)
SELECT * FROM CTE_Products

方法二：用EXISTS直接判断存在性

WITH CTE_Products AS (
    -- 业务联系人表中，只保留在非业务表也有对应ID且productID>10的记录
    SELECT productID, productName, companyName 
    FROM businessContacts bc
    WHERE productID > 10
      AND EXISTS (SELECT 1 FROM nonBusinessContacts nbc WHERE nbc.productID = bc.productID)
    UNION ALL
    -- 非业务联系人表中，只保留在业务表也有对应ID且productID>10的记录
    SELECT productID, productName, companyName 
    FROM nonBusinessContacts nbc
    WHERE productID > 10
      AND EXISTS (SELECT 1 FROM businessContacts bc WHERE bc.productID = nbc.productID)
)
SELECT * FROM CTE_Products

逻辑说明 #

两种方法核心都是先锁定同时出现在两个表中的productID，再基于这些ID提取两个表的记录，确保不会保留某表独有的productID对应的条目。UNION ALL维持了原有的合并逻辑，不会自动去重（如果两个表同ID的记录内容不同，都会被保留）。

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