多自引用ID字段场景下，如何用单SQL查询获取关联人员姓名？

原始数据表 #

| id | name  | mother | father | brothers  |
|----|-------|--------|--------|-----------|
| 1  | danny | 3      | 2      | {4, 6, 7} |
| 2  | bob   | 20     | 30     | {}        |
| 3  | marge | 10     | 50     | {}        |
| 4  | rose  | 3      | 2      | {1, 6, 7} |

当前实现方式 #

要获取ID为1的Danny的母亲、父亲及兄弟姓名，现在是用两次查询加代码处理：
第一次查亲属ID：

SELECT mother, father, brothers
FROM people
WHERE id = 1

拿到结果后，再执行第二次查询拿姓名：

SELECT name
FROM people
WHERE id IN (3, 2, 4, 6, 7)

需求与问题 #

想改成单条SQL搞定。另外说明下：示例里只列了3个自引用字段，实际场景里这类字段有15个左右。

你已经尝试了下面的语句，但不确定是不是最优：

SELECT "name"
FROM   people
WHERE  id IN
       (
              SELECT mother AS sibling
              FROM   people
              WHERE  id = 1
              UNION
              SELECT Unnest(brothers) AS sibling
              FROM   people
              WHERE  id = 1)

疑问点：

• 性能上：每次UNION都重复查FROM people WHERE id=1，不知道PostgreSQL底层会不会优化
• 语句太冗余：一堆重复的FROM和WHERE子句

优化方案 #

下面两种方案都能解决你的问题，而且避免了重复查询，更高效：

方案1：用CTE复用目标用户数据 #

CTE只会查一次Danny的记录，把所有亲属ID收集好，后续直接用，不会重复扫表：

WITH danny_relatives AS (
    SELECT 
        mother,
        father,
        unnest(brothers) AS sibling_id
    FROM people
    WHERE id = 1
)
SELECT p.name
FROM people p
JOIN (
    SELECT mother AS relative_id FROM danny_relatives
    UNION ALL
    SELECT father AS relative_id FROM danny_relatives
    UNION ALL
    SELECT sibling_id AS relative_id FROM danny_relatives
) r ON p.id = r.relative_id;

这里用UNION ALL代替UNION，因为亲属ID不会重复，不需要去重，性能比UNION更好。

方案2：横向展开所有自引用字段（更适合多字段场景） #

如果有15个自引用字段，这个写法更简洁，只需要在VALUES里依次加字段就行：

SELECT p.name
FROM people d
CROSS JOIN LATERAL (
    VALUES
        (d.mother),
        (d.father),
        UNNEST(d.brothers)
) AS relatives(rel_id)
JOIN people p ON p.id = relatives.rel_id
WHERE d.id = 1;

这个写法只会查一次id=1的记录，然后把所有亲属ID转成一行行的数据，再关联查姓名，性能最优，代码也更干净。

性能提示 #

• 上面两个方案都只会扫描一次people表中id=1的记录，比你之前的尝试高效
• 确保people.id是主键（或者有索引），关联查询的时候速度会非常快

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