Is there a way to make multiple UNION's more efficient when they all do the same joins?

Question

I have 3 tables and I'd like to make a view that aggregates data vertically (using UNIONS) from the 3 tables. I already have a query that does what I want, but it uses a lot of repeated joins for each query, and I'm wondering if there is a way to only join up all these tables once and put the unions on top of that.

I want to join 3 tables that I will call desiredTable1, desiredTable2, and desiredTable3.

They all make use of joins on joinTable, joinTable2 and joinTable3 like so:

(select desiredTable1.id, desiredTable1.created_at, joinTable1.firstname, joinTable1.lastname, 'DESCRIPTOR_1' as "descriptor",  desiredTable1.amount from desiredTable1
join joinTable3 on joinTable3.id = desiredTable1.joinTable3_id
join joinTable2 on joinTable2.id = joinTable3.joinTable2_id
join joinTable1 on joinTable1.id = joinTable2.joinTable1_id)


UNION


(select desiredTable2.id, desiredTable2.created_at, joinTable1.firstname, joinTable1.lastname, 'DESCRIPTOR_2' as "descriptor",  desiredTable2.amount from desiredTable2
join joinTable3 on joinTable3.id = desiredTable2.joinTable3_id
join joinTable2 on joinTable2.id = joinTable3.joinTable2_id
join joinTable1 on joinTable1.id = joinTable2.joinTable1_id)


UNION


(select desiredTable3.id, desiredTable3.created_at, joinTable1.firstname, joinTable1.lastname, 'DESCRIPTOR_3' as "descriptor", desiredTable3.amount from desiredTable3
join joinTable3 on joinTable3.id = desiredTable3.joinTable3_id
join joinTable2 on joinTable2.id = joinTable3.joinTable2_id
join joinTable1 on joinTable1.id = joinTable2.joinTable1_id)

As you can see, I need the linked information from joinTable1 in each query, but if I can help it, I would prefer not to repeat all these joins. Is there a way I could "define" a subquery that does all those joins, then do all the unions on top of that?

score 2 · Accepted Answer · answered Sep 06 '19 at 18:30

You can use union all before joining:

select dt.id, dt.created_at, jt1.firstname, jt1.lastname, dt.descriptor,  dt.amount
from ((select dt1.*, 'DESCRIPTOR_1' as descriptor
       from desiredTable1 dt1
      ) union all
      (select dt2.*, 'DESCRIPTOR_2' as descriptor
       from desiredTable2 dt2
      ) union all
      (select dt3.*, 'DESCRIPTOR_3' as descriptor
       from desiredTable3 dt3
      )
     ) dt join
     joinTable3 jt3
     on jt3.id = dt.joinTable3_id join
     joinTable2 jt2
     on jt2.id = jt3.joinTable2_id join
     joinTable1 jt1
     on jt1.id = jt2.joinTable1_id;

Note: This uses dt.* for the subqueries as a convenience. If the tables don't have the same columns, list only the ones needed for the outer query.

Is there a way to make multiple UNION's more efficient when they all do the same joins?

1 Answers1