Outer JOIN 2 Tables using a third table to filter

Question

I am creating a view with two very large data sets numbering the millions of rows. I have joining the two tables together using a full outer join and group by some of the columns.

I have a field id column common to both tables that I need users to be able to filter the view on. Originally, I was using TableA.F_ID = TableB.F_ID in the outer join syntax and that performed very well. However, if there were F_ID rows in TableB that were not in TableA they would not be displayed unless I used

where TableA.F_ID = <VALUE> or TableB.ID = <VALUE>

To get around having to use the or clause I used the fable that F_ID comes from in the query via an inner join to filter the data. This method does work but it has to execute the select on TABLEA and TABLEB before filtering out the rows and the query is taking 10+ minutes to return.

Based on the query below is there a way to optimize this to get a quicker end result?

select fm.F_ID
       ,fad.A
       ,fad.B
       ,fad.C
       ,fac.A
       ,fac.B
       ,fac.C
       ,fab.SUM as FAB_SUM
       ,fac.SUM as FAC_SUM
from V_FAB_COMBINED_GROUPS fad
FULL OUTER JOIN V_LTB_ALL_GROUPED fac on 
    fab.F_ID = fac.F_ID
    and fac.A = fab.A
    and fac.B = fab.B
    and fac.C = fab.C
inner join fm on (fm.F_ID = fab.F_ID or fm.F_ID = fac.F_ID)
where fm.F_ID = 1

What's the issue with using the additional where clause? You could do do coalesce(fab.f_id, fac.f_id) f_id as a column in the view? That way, users could just do and f_id = ... when they query against the view? — Boneist
@Boneist no issue with where clause per say, I just have very stringent requirements for how the view must function. I will research the coalesce option you mentioned — Josh

kanagaraj kanagaraj · Accepted Answer · 2018-11-02T15:54:07

Try this and see if it improves performance:

select cte.*
from (select fm.F_ID fm_id
       ,fac.F_ID fac_id   
       ,fad.A
       ,fad.B
       ,fad.C
       ,fac.A
       ,fac.B
       ,fac.C
       ,fab.SUM as FAB_SUM
       ,fac.SUM as FAC_SUM
from V_FAB_COMBINED_GROUPS fab
FULL OUTER JOIN V_LTB_ALL_GROUPED fac  
    on fab.F_ID = fac.F_ID
    and fac.A = fab.A
    and fac.B = fab.B
    and fac.C = fab.C) cte
 join fm on (fm.F_ID = cte.fm_id or fm.F_ID = cte.fac_id)
where fm.F_ID = 1

Outer JOIN 2 Tables using a third table to filter

1 Answers