如何通过互斥条件实现两表Left Join且仅匹配一次？

嘿，这个需求很常见，你当前的写法确实能实现“要么用col1匹配，要么用col2匹配（col1不匹配时才触发）”的逻辑，但如果遇到一个TableA行同时满足两种匹配条件的情况，还是会返回重复行，而且条件表达式可以更直观。下面给你两种更简洁且能避免重复的实现方式：

方案1：用LATERAL JOIN（支持的数据库：PostgreSQL、SQL Server、MySQL 8.0+等） #

这种方式最直观，明确指定匹配优先级，且确保每个TableA行只匹配一个TableB行：

SELECT a.*, b.*
FROM TableA a
LEFT JOIN LATERAL (
    -- 先找col1匹配的行，找不到再找col2匹配的
    SELECT *
    FROM TableB b
    WHERE a.col1 = b.col1 OR a.col2 = b.col2
    ORDER BY CASE WHEN a.col1 = b.col1 THEN 1 ELSE 2 END
    LIMIT 1  -- 只取优先级最高的一个匹配项
) b ON true;

为什么更优： #

• 逻辑清晰：通过ORDER BY明确了“col1匹配优先”的规则
• 自动去重：LIMIT 1直接确保每个TableA行最多返回一个匹配结果
• 可读性强：子查询里的逻辑一眼就能看懂，比嵌套的OR条件好维护

方案2：用窗口函数（通用所有支持窗口函数的数据库） #

如果你的数据库不支持LATERAL JOIN，窗口函数是通用解决方案：

WITH ranked_matches AS (
    SELECT 
        a.*, 
        b.*,
        -- 给每个TableA行的匹配项排名，col1匹配的排第1
        ROW_NUMBER() OVER (
            PARTITION BY a.id  -- 这里替换成TableA的主键字段
            ORDER BY CASE WHEN a.col1 = b.col1 THEN 1 ELSE 2 END
        ) AS match_rank
    FROM TableA a
    LEFT JOIN TableB b ON a.col1 = b.col1 OR a.col2 = b.col2
)
-- 只保留每个TableA行的第一个匹配项
SELECT *
FROM ranked_matches
WHERE match_rank = 1;

为什么更优： #

• 兼容性广：几乎所有现代关系型数据库都支持窗口函数
• 灵活扩展：如果以后要加更多匹配优先级，只要修改ORDER BY里的CASE逻辑就行

关于你原写法的说明 #

你原来的条件a.col1 = b.col1 OR (a.col1 != b.col1 AND a.col2 = b.col2)其实等价于a.col1 = b.col1 OR a.col2 = b.col2——因为当a.col1 = b.col1时，后面的括号条件自动不成立，两种写法逻辑完全一致。但原写法的问题是无法避免重复行：如果一个TableA行同时有col1匹配和col2匹配的TableB行，会返回两行结果，而上面的两种方案都解决了这个问题。

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