Oracle中Left Join与Union联用产生重复记录的原因及优化方案咨询

重复原因分析 #

你当前查询返回3条重复101的核心问题在于关联逻辑的错误匹配：

• 原查询通过子查询UNION获取匹配的ordnum，再与payment_info进行关联。当payment表中某行的支付字段（如第三行的phonepe）无法匹配payment_info时，子查询返回NULL，此时NULL = pinfo.ordnum的比较结果为NULL。SQL Server的LEFT JOIN在这种场景下，错误地将左表行与右表中存在的ordnum=101行进行了匹配，导致重复结果。
• 另外，原查询的子查询逻辑冗余，多次扫描payment_info表，也可能引发意外的匹配问题。

高性能解决方案 #

以下方案均针对SQL Server优化，避免OR条件的性能瓶颈，同时准确返回期望结果（101、102、NULL）：

方案1：UNPIVOT转换后关联 #

先将payment表的多列支付方式转为行结构，再通过等值关联payment_info，逻辑清晰且性能优异：

SELECT 
    pi.ordnum
FROM (
    SELECT 
        invoice,
        CASE 
            WHEN cardpay IS NOT NULL THEN 'C'
            WHEN gpay IS NOT NULL THEN 'G'
            WHEN phonepe IS NOT NULL THEN 'P'
        END AS payment_method,
        COALESCE(cardpay, gpay, phonepe) AS payment_value
    FROM payment
    WHERE invoice = '4567'
) p_unpivot
LEFT JOIN payment_info pi 
    ON p_unpivot.payment_method = pi.payment_method
    AND p_unpivot.payment_value = pi.payment_value

该方案利用列转行消除了多条件匹配的复杂性，可充分利用payment_info表上(payment_method, payment_value)的复合索引。

方案2：使用OUTER APPLY运算符 #

SQL Server的OUTER APPLY专为行级关联优化，针对左表每一行执行子查询，避免OR条件的性能损耗：

SELECT 
    pi.ordnum
FROM payment p
OUTER APPLY (
    SELECT pi_inner.ordnum
    FROM payment_info pi_inner
    WHERE 
        (pi_inner.payment_method = 'C' AND pi_inner.payment_value = p.cardpay)
        OR (pi_inner.payment_method = 'G' AND pi_inner.payment_value = p.gpay)
        OR (pi_inner.payment_method = 'P' AND pi_inner.payment_value = p.phonepe)
) pi
WHERE p.invoice = '4567'

OUTER APPLY会保留左表所有行，子查询无匹配时返回NULL，SQL Server对APPLY的执行计划优化通常优于普通OR条件JOIN。

方案3：CASE表达式匹配 #

若payment表每行仅存在一个有效支付字段（如示例数据），可直接用CASE表达式匹配，逻辑简单性能高：

SELECT 
    CASE
        WHEN p.cardpay IS NOT NULL THEN (SELECT ordnum FROM payment_info WHERE payment_method='C' AND payment_value=p.cardpay)
        WHEN p.gpay IS NOT NULL THEN (SELECT ordnum FROM payment_info WHERE payment_method='G' AND payment_value=p.gpay)
        WHEN p.phonepe IS NOT NULL THEN (SELECT ordnum FROM payment_info WHERE payment_method='P' AND payment_value=p.phonepe)
    END AS ordnum
FROM payment p
WHERE p.invoice = '4567'

每个标量子查询仅执行一次，且可利用payment_info的复合索引快速定位匹配值。

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