两临时表全外连接：前5列匹配后两列非空不匹配时保留B表记录

要对两个临时表做全外连接，核心需求就是：当A表和B表的前5列（RowID/B_RowID、fr_billto/B_fr_billto、ord_revtype2/B_ord_revtype2、stp_mileagetype/B_stp_mileagetype、stp_type1/B_stp_type1）匹配时，哪怕后两列（A.BasisUnit与B.B_TARBasisUnit、A.RateUnit与B.B_RateUnit）非空且不匹配，也得保留B表中对应的行。之前的连接条件因为把后两列的严格匹配也加进去了，导致这类B表记录直接被过滤掉了。

解决方案一：调整连接键+WHERE筛选 #

把连接条件只限定在前5列，再通过WHERE子句筛选出所有需要保留的场景：

SELECT 
    A.RowID,
    A.fr_billto,
    A.ord_revtype2,
    A.stp_mileagetype,
    A.stp_type1,
    A.BasisUnit,
    A.RateUnit,
    B.B_RowID,
    B.B_fr_billto,
    B.B_ord_revtype2,
    B.B_stp_mileagetype,
    B.B_stp_type1,
    B.B_TARBasisUnit,
    B.B_RateUnit,
    B.B_TARCode
FROM 
    #TempA A
FULL OUTER JOIN 
    #TempB B ON 
        A.RowID = B.B_RowID 
        AND A.fr_billto = B.B_fr_billto 
        AND A.ord_revtype2 = B.B_ord_revtype2 
        AND A.stp_mileagetype = B.B_stp_mileagetype 
        AND A.stp_type1 = B.B_stp_type1
WHERE 
    -- 保留前后列都匹配的原有效行
    (A.BasisUnit = B.B_TARBasisUnit AND A.RateUnit = B.B_RateUnit)
    -- 保留前5列匹配、后两列非空但不匹配的B表行
    OR (A.RowID IS NOT NULL 
        AND B.B_TARBasisUnit IS NOT NULL 
        AND B.B_RateUnit IS NOT NULL 
        AND NOT (A.BasisUnit = B.B_TARBasisUnit AND A.RateUnit = B.B_RateUnit))
    -- 保留A表独有的行
    OR B.B_RowID IS NULL
    -- 保留B表完全无A表匹配的行
    OR A.RowID IS NULL;

解决方案二：UNION ALL拆分场景 #

把需求拆成三个独立的查询，用UNION ALL合并结果，逻辑更直观：

-- 1. A表所有行 + 后两列匹配的B表对应行
SELECT 
    A.*, B.*
FROM #TempA A
LEFT JOIN #TempB B ON 
    A.RowID = B.B_RowID 
    AND A.fr_billto = B.B_fr_billto 
    AND A.ord_revtype2 = B.B_ord_revtype2 
    AND A.stp_mileagetype = B.B_stp_mileagetype 
    AND A.stp_type1 = B.B_stp_type1
    AND A.BasisUnit = B.B_TARBasisUnit 
    AND A.RateUnit = B.B_RateUnit

UNION ALL

-- 2. 前5列匹配、后两列非空但不匹配的B表行
SELECT 
    A.*, B.*
FROM #TempA A
JOIN #TempB B ON 
    A.RowID = B.B_RowID 
    AND A.fr_billto = B.B_fr_billto 
    AND A.ord_revtype2 = B.B_ord_revtype2 
    AND A.stp_mileagetype = B.B_stp_mileagetype 
    AND A.stp_type1 = B.B_stp_type1
WHERE 
    B.B_TARBasisUnit IS NOT NULL 
    AND B.B_RateUnit IS NOT NULL 
    AND NOT (A.BasisUnit = B.B_TARBasisUnit AND A.RateUnit = B.B_RateUnit)

UNION ALL

-- 3. B表中没有A表前5列匹配的行
SELECT 
    A.*, B.*
FROM #TempA A
RIGHT JOIN #TempB B ON 
    A.RowID = B.B_RowID 
    AND A.fr_billto = B.B_fr_billto 
    AND A.ord_revtype2 = B.B_ord_revtype2 
    AND A.stp_mileagetype = B.B_stp_mileagetype 
    AND A.stp_type1 = B.B_stp_type1
WHERE A.RowID IS NULL;

两种方案都能满足需求，第一种适合追求简洁的场景，第二种拆分后逻辑更清晰，便于后续维护。

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