Multiple Full Outer Joins

Question

I want to use the result of a FULL OUTER JOIN as a table to FULL OUTER JOIN on another table. What is the syntax that I should be using?

For eg: T1, T2, T3 are my tables with columns id, name. I need something like:

T1 FULL OUTER JOIN T2 on T1.id = T2.id   ==> Let this be named X

X FULL OUTER JOIN T3 on X.id = t3.id

I want this to be achieved so that in the final ON clause, I want the T3.id to match either T1.id or T2.id. Any alternative way to do this is also OK.

TT. · Accepted Answer · 2016-03-08T12:00:44.567

3

SELECT COALESCE(X.id,t3.id) AS id, *-- specific columns here instead of the *
FROM 
    (
       SELECT COALESCE(t1.id,t2.id) AS id, * -- specific columns here instead of the *
       FROM T1 FULL OUTER JOIN T2 on T1.id = T2.id
    ) AS X
    FULL OUTER JOIN T3 on X.id = t3.id

edited Mar 08 '16 at 12:00

answered Mar 08 '16 at 11:42

TT.

15,774
6
47
88

@sagi Oooowkay, hand and feet it is. – TT. Mar 08 '16 at 12:01
:PP , didn't mean to upset – sagi Mar 08 '16 at 12:03
@sagi No worries ;-), I understand. – TT. Mar 08 '16 at 12:07

score 3 · Answer 2 · answered Mar 08 '16 at 11:48

Often, chains of full outer joins don't behave quite as expected. One replacements uses left join. This works best when a table has all the ids you need. But you can also construct that:

from (select id from t1 union
      select id from t2 union
      select id from t3
     ) ids left join
     t1
     on ids.id = t1.id left join
     t2
     on ids.id = t2.id left join
     t3
     on ids.id = t3.id

Note that the first subquery can often be replaced by a table. If you have such a table, you can select the matching rows in the where clause:

from ids left join
     t1
     on ids.id = t1.id left join
     t2
     on ids.id = t2.id left join
     t3
     on ids.id = t3.id
where t1.id is not null or t2.id is not null or t3.id is not null

sagi · Answer 3 · 2016-03-08T12:45:37.013

1

You can do it like you suggested, using IN()

FROM T1
FULL OUTER JOIN T2
 ON(T1.id = T2.id)
FULL OUTER JOIN T3
 ON(T3.ID IN(T2.id,T1.id))

or I think you can do it with a UNION (depends on what you need) :

SELECT * FROM 
    (SELECT name,id from T1
    UNION
    SELECT name,id from T2) x
FULL OUTER JOIN T3
   ON(t3.id = x.id)

edited Mar 08 '16 at 12:45

answered Mar 08 '16 at 11:41

sagi

40,026
6
59
84

I get this error when I use IN(): NotImplementedException: Error generating a valid execution plan for this query. A FULL OUTER JOIN type with no equi-join predicates can only be executed with a single node plan. – Putt Mar 08 '16 at 12:40
Did you try to run it or run execution plan on it? try to run it first @Abhishek – sagi Mar 08 '16 at 12:41
I ran the query in Impala which resulted in this error. – Putt Mar 08 '16 at 12:43

Multiple Full Outer Joins

3 Answers3