如何用SQL从多关联表的多列中分别获取唯一值（而非唯一行值）

首先，你遇到的核心问题是多表JOIN产生了笛卡尔积，导致结果是所有角色ID的排列组合，而不是每个角色ID的独立唯一值。下面分两种形式给出高效的解决方案：

形式一：各角色ID独立成行（推荐，高效无冗余） #

这种形式是最直接且高效的，通过UNION ALL分别查询每个关联表的唯一ID，其他列填充NULL，完全避免笛卡尔积：

SELECT ac.admin_id, NULL AS student_id, NULL AS teacher_id
FROM admin_contacts ac
WHERE ac.contact_id = 1
UNION ALL
SELECT NULL AS admin_id, sc.student_id, NULL AS teacher_id
FROM student_contacts sc
WHERE sc.contact_id = 1
UNION ALL
SELECT NULL AS admin_id, NULL AS student_id, tc.teacher_id
FROM teacher_contacts tc
WHERE tc.contact_id = 1;

为什么这个方案更好？ #

• 每个子查询只扫描对应的关联表，没有多表JOIN的开销，执行速度极快
• 直接返回每个角色ID的唯一值，没有冗余数据，不需要后续遍历去重
• 如果你的关联表在contact_id列上有索引，查询效率会进一步提升（建议给这三张关联表的contact_id加索引）

形式二：角色ID按行对齐（仅当各角色数量一致时推荐） #

你提到的形式二需要把三个角色的ID按行对齐，这在各角色数量不同时无法完美实现（会出现NULL），但可以通过窗口函数+全连接来模拟：

WITH admin_ids AS (
    -- 给admin的ID按顺序编号
    SELECT admin_id, ROW_NUMBER() OVER (ORDER BY admin_id) AS rn
    FROM admin_contacts
    WHERE contact_id = 1
),
student_ids AS (
    -- 给student的ID按顺序编号
    SELECT student_id, ROW_NUMBER() OVER (ORDER BY student_id) AS rn
    FROM student_contacts
    WHERE contact_id = 1
),
teacher_ids AS (
    -- 给teacher的ID按顺序编号
    SELECT teacher_id, ROW_NUMBER() OVER (ORDER BY teacher_id) AS rn
    FROM teacher_contacts
    WHERE contact_id = 1
)
-- 按编号全连接，实现对齐
SELECT a.admin_id, s.student_id, t.teacher_id
FROM admin_ids a
FULL OUTER JOIN student_ids s ON a.rn = s.rn
FULL OUTER JOIN teacher_ids t ON COALESCE(a.rn, s.rn) = t.rn;

注意事项： #

• 这里的对齐是基于ID排序后的编号，没有实际业务逻辑关联，只是形式上的对齐
• 如果某类角色的数量多于其他类，多出的行会在其他列显示NULL
• 这种方案的性能略低于形式一，因为需要额外的窗口函数计算和连接操作

为什么原来的查询会产生笛卡尔积？ #

当你同时JOIN三张关联表时，数据库会先把admin_contacts和student_contacts的所有组合生成，再和teacher_contacts组合，最终得到所有可能的排列组合。DISTINCT只能去重完全相同的行，但无法消除不同角色ID的组合，所以会出现3^3=27行的结果。

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