Search code examples
sqlimpala

Multiple Full Outer Joins

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.

Solution 
  • 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