如何通过单次关联实现多字段与同表记录匹配？

现有数据表 #

tableA #

id,nameId,locationId,addressId
100,bob,ny,abc
200,bean,uk,zx

tableB #

Ids
bob
ny
abc
200,
bean

期望输出 #

id,nameId,locationId,addressId,nameIdMatch,locationIdMatch,addressIdMatch
100,bob,ny,abc,bob,ny,abc
200,bean,uk,zx,bean,uk,

问题 #

目前通过将nameId、locationId、addressId逐个与tableB关联的方式能得到目标结果，有没有更优的方法，仅通过单次关联实现需求？

解决方案 #

可以通过一次LEFT JOIN配合条件判断与聚合函数实现，以下是适配多数关系型数据库的通用SQL示例：

SELECT 
    a.id,
    a.nameId,
    a.locationId,
    a.addressId,
    MAX(CASE WHEN b.Ids = a.nameId THEN a.nameId ELSE '' END) AS nameIdMatch,
    MAX(CASE WHEN b.Ids = a.locationId THEN a.locationId ELSE '' END) AS locationIdMatch,
    MAX(CASE WHEN b.Ids = a.addressId THEN a.addressId ELSE '' END) AS addressIdMatch
FROM tableA a
LEFT JOIN tableB b ON b.Ids IN (a.nameId, a.locationId, a.addressId)
GROUP BY a.id, a.nameId, a.locationId, a.addressId;

逻辑说明 #

1. 单次关联：通过LEFT JOIN将tableA与tableB关联，关联条件直接判断tableB的Ids是否等于tableA的任意一个目标字段，仅执行一次关联操作。
2. 条件匹配：用CASE语句分别检查每个字段是否在tableB中存在，存在则返回字段值，否则返回空字符串。
3. 聚合去重：关联后可能出现多条匹配记录，用MAX（或MIN）聚合函数提取每个字段的有效匹配结果，确保每条tableA记录只输出一行。

如果是支持数组或集合操作的数据库（比如PostgreSQL），还可以先将tableB的Ids转为数组，直接判断字段是否在数组中，全程无需JOIN，效率更高：

WITH b_ids AS (
    SELECT ARRAY_AGG(Ids) AS id_list FROM tableB
)
SELECT 
    a.id,
    a.nameId,
    a.locationId,
    a.addressId,
    CASE WHEN a.nameId = ANY((SELECT id_list FROM b_ids)) THEN a.nameId ELSE '' END AS nameIdMatch,
    CASE WHEN a.locationId = ANY((SELECT id_list FROM b_ids)) THEN a.locationId ELSE '' END AS locationIdMatch,
    CASE WHEN a.addressId = ANY((SELECT id_list FROM b_ids)) THEN a.addressId ELSE '' END AS addressIdMatch
FROM tableA a;

这个方式仅查询一次tableB生成id数组，之后直接在tableA的查询中做数组包含判断，属于高效的单次查询实现方式。

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