Select a.field1, a.field2, a.field3
From (Select a.field1, b.field2, c.field3
From table1 a
Join table2 b
on b.something = a.something
Join table3 c
On c.something = a.something
Where a.field7 = 'value'
UNION ALL
Select a.field4, a.field5, a.field6
From table11 a
Join table21 b
On b.something = a.something
Where a.field8 = 'something' ) a
Join table10 b
On b.field11 = (Select c.field11
From table10 c
Where c.field10 = a.field1 ) <- instead of a.field1, if I hardcode value (eg. '100') query runs faster
Join table21 c
On c.something = a.something
...