避免使用含OR的JOIN：多ID关联的SQL查询优化方案咨询

数据表 #

table_1

table_2

原SQL代码 #

select
t1.id_1,
sum(t1.num_purchases) as sum_purchases,
array_agg(t2.order) as orders

from table_1 t1

left join (select id, order from table_2) t2
on t2.id = t1.id_1

group by t1.id_1

需求 #

原代码仅匹配table_2.id = table_1.id_1，但table_2.id可能对应table_1.id_1或table_1.id_2，需要调整代码实现正确关联，且不能在JOIN条件中使用OR。

预期结果 #

（注：原预期结果中id=5的sum_purchases应为24，推测是笔误）

解决方案 #

方案1：拆分关联键后关联

通过UNION ALL将table_1的每条记录拆分为两行，分别以id_1和id_2作为关联字段，再与table_2关联，最后聚合结果：

select
    base.id_1,
    sum(base.num_purchases) as sum_purchases,
    array_agg(t2.order) as orders
from (
    -- 拆分出id_2作为关联键的行
    select id_1, id_2, num_purchases from table_1
    union all
    -- 拆分出id_1作为关联键的行
    select id_1, id_1 as id_2, num_purchases from table_1
) base
left join table_2 t2 on t2.id = base.id_2
group by base.id_1

方案2：两次独立LEFT JOIN后合并结果

分别用id_1和id_2与table_2做LEFT JOIN，再合并两次关联得到的订单数组：

select
    t1.id_1,
    sum(t1.num_purchases) as sum_purchases,
    array_cat(
        -- 合并id_1关联得到的订单数组（过滤空值）
        coalesce(array_agg(t2a.order) filter (where t2a.order is not null), '{}'),
        -- 合并id_2关联得到的订单数组（过滤空值）
        coalesce(array_agg(t2b.order) filter (where t2b.order is not null), '{}')
    ) as orders
from table_1 t1
left join table_2 t2a on t2a.id = t1.id_1
left join table_2 t2b on t2b.id = t1.id_2
group by t1.id_1

两种方案均未在JOIN条件中使用OR，且能正确匹配table_2.id到table_1的id_1或id_2。

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