LEFT JOIN匹配失败时，如何提取表A首词关联表B数据？

问题场景 #

使用LEFT JOIN关联表A与表B，通过Names字段精确匹配时，部分记录因格式差异（如后缀不同）匹配失败返回Null。需要实现：当精确匹配失败时，提取表A Names字段的首词，在表B Names中搜索并返回对应数据。

表结构示例 #

表A

表B

当前LEFT JOIN输出结果 #

期望输出结果 #

解决方案 #

核心思路：先执行精确匹配，当匹配结果为Null时，提取表A Names的首词，在表B中匹配包含该首词的记录，用COALESCE函数优先返回精确匹配的结果，无结果时返回二次匹配的结果。

MySQL/MariaDB 实现 #

SELECT
    a.Names AS `A.Names`,
    COALESCE(b1.Names, (
        SELECT Names 
        FROM 表B 
        WHERE Names LIKE CONCAT(SUBSTRING_INDEX(a.Names, ' ', 1), '%') 
        LIMIT 1
    )) AS `B.Names`,
    COALESCE(b1.Type, (
        SELECT Type 
        FROM 表B 
        WHERE Names LIKE CONCAT(SUBSTRING_INDEX(a.Names, ' ', 1), '%') 
        LIMIT 1
    )) AS Type
FROM 表A a
LEFT JOIN 表B b1 ON a.Names = b1.Names;

SQL Server 实现 #

SELECT
    a.Names AS [A.Names],
    COALESCE(b1.Names, b2.Names) AS [B.Names],
    COALESCE(b1.Type, b2.Type) AS Type
FROM 表A a
LEFT JOIN 表B b1 ON a.Names = b1.Names
OUTER APPLY (
    SELECT TOP 1 Names, Type
    FROM 表B
    WHERE Names LIKE CONCAT(LEFT(a.Names, CHARINDEX(' ', a.Names) - 1), '%')
) b2;

PostgreSQL 实现 #

SELECT
    a.Names AS "A.Names",
    COALESCE(b1.Names, b2.Names) AS "B.Names",
    COALESCE(b1.Type, b2.Type) AS Type
FROM 表A a
LEFT JOIN 表B b1 ON a.Names = b1.Names
LEFT JOIN LATERAL (
    SELECT Names, Type
    FROM 表B
    WHERE Names LIKE CONCAT(SPLIT_PART(a.Names, ' ', 1), '%')
    LIMIT 1
) b2 ON TRUE;

注意事项 #

• 若表B中存在多条包含同一首词的记录，上述SQL会返回数据库默认排序的第一条记录。如需优先匹配更接近表A Names的记录，可在子查询中添加ORDER BY，比如ORDER BY LENGTH(Names)优先返回名称最短的记录。
• 当表A的Names字段为单个单词（无空格）时，提取首词的函数会返回整个字段，不影响匹配逻辑。

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