Geek Answers Handbook

Multiple full outer join

I want to use the result FULL OUTER JOINas a table for FULL OUTER JOINin another table. What syntax should I use?

For example, T1, T2, T3 - my tables with column identifiers, 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 in the last ON clause, I want T3.id to match either T1.idor T2.id. Any alternative way to do this is fine too.

+4
source share
3 answers
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
+2
source

You can do this as 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 UNION (depending 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)
+1

Often, chains of complete external associations do not behave as expected. In one replacement is used left join. This works best when the table has all the required identifiers. But you can also build this:

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 appropriate rows in the sentence where:

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
0
source

More articles:


All Articles